FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Hi Charles,

see the following, which is talking about prepared statements in
Sybase's ct-library API

http://sybooks.sybase.com/onlinebooks/group-cn/cng1250e/guide

it says:

Adaptive Server restrictions and database requirements
Adaptive Server Enterprise implements dynamic SQL using 
temporary stored procedures. A temporary stored procedure 
is created when a SQL statement is prepared, and destroyed 
when that prepared statement is deallocated. 
A prepared statement can be deallocated either explicitly 
with a ct_dynamic(CS_DEALLOC) call or implicitly when a 
connection is closed.

As a consequence of this implementation, an application 
accessing Adaptive Server and using dynamic SQL is subject 
to the restrictions of Adaptive Server stored procedures. 
Some of the implications of this are: 

Temporary tables are destroyed when the prepared statement is
deallocated.


...


HTH,

Bill


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Charles Hung
Sent: 16 November 2005 17:35
To: freetds AT lists.ibiblio.org
Subject: [freetds] FreeTDS ODBC and Sybase temporary table



Hello,

I am setting up a Sybase ODBC application on HP-UX 11i (11.11) PA-RISC
using FreeTDS 0.63 and unixODBC 2.2.11, both built from source. I was
able to connect to a remote Sybase ASE 12.5.3 database and ran few
simple queries using both tsql (FreeTDS) and
isql (unixODBC). TDS version 5.0 is used.

Then I tried the following SQL statements:

create table #chungtest(t1 char(10))
go
select count(*) from #chungtest
go

tsql works properly by returning 0. However, isql complained the table
in tempdb cannot be found when the SELECT statement is run. I did
TDSDUMP on both isql and tsql, and noticed in isql dump, these two
statements are prepared as stored procedures (see
the log at the end).

Sybase documentation says when a temporary table is created inside a
stored procedure, that temporary table only persists inside the stored
procedure. See
http://sybooks.sybase.com/onlinebooks/group-as/asg1251e/sqlug/@Generic__
BookTextView/20907;pt=20907#X

This explains why the SELECT statement cannot find the temporary table
when these two statements are created as two different stored
procedures.

My questions are:
1. Is it true that the conversion to stored procedure occurs in the
FreeTDS ODBC driver? I believe it is since I did not find the converted
statements in the unixODBC trace log.

2. If the conversion occurs in FreeTDS ODBC driver, is there a way to
change the driver behavior so the conversion does not happen to
temporary tables, or not at all?

Any comment and help would be appreciated.

Charles Hung

TDSDUMP from isql (unixODBC)
=============================
20:00:59 Creating prepared statement
20:00:59 tds_free_all_results()
20:00:59 Sending packet
0000 0f 01 00 5a 00 00 00 00-e7 00 4f 01 00 0a 6f 31 |...Z.... ..O...o1|
0010 36 37 6c 6f 36 30 30 30-00 40 63 72 65 61 74 65 |67lo6000 .@create|
0020 20 70 72 6f 63 20 6f 31-36 37 6c 6f 36 30 30 30 | proc o1 67lo6000|
0030 20 61 73 20 63 72 65 61-74 65 20 74 61 62 6c 65 | as crea te table|
0040 20 23 63 68 75 6e 67 74-65 73 74 20 28 74 31 20 | #chungt est (t1 |
0050 63 68 61 72 28 31 30 29-29 20                   |char(10) ) |

20:00:59 Received header
0000 04 01 00 21 00 00 00 00-                        |...!....|

20:00:59 Received packet
0000 e7 00 0d 20 00 0a 6f 31-36 37 6c 6f 36 30 30 30 |... ..o1 67lo6000|
0010 fd 00 00 00 02 00 00 00-00                      |........ .|

20:00:59 processing result tokens.  marker is  e7(TDS5_DYNAMIC)
20:00:59 tds_get_string: reading 10 from wire to give 10 to client.
20:00:59 processing result tokens.  marker is  fd(DONE)
20:00:59 tds_process_end: more_results = 0
            was_cancelled = 0
            error = 0
            done_count_valid = 0
20:00:59 tds_process_end() state set to TDS_IDLE
20:00:59 tds_process_result_tokens() state is COMPLETED
20:00:59 End prepare, execute
20:00:59 tds_submit_execute()
20:00:59 tds_free_all_results()
20:00:59 Sending packet
0000 0f 01 00 1a 00 00 00 00-e7 00 0f 02 00 0a 6f 31 |........ ......o1|
0010 36 37 6c 6f 36 30 30 30-00 00                   |67lo6000 ..|

20:00:59 Received header
0000 04 01 00 2a 00 00 00 00-                        |...*....|

20:00:59 Received packet
0000 e7 00 0d 20 00 0a 6f 31-36 37 6c 6f 36 30 30 30 |... ..o1 67lo6000|
0010 ff 00 41 00 02 00 00 00-00 fe 00 08 00 02 00 00 |..A..... ........|
0020 00 00                  -                        |..|

20:00:59 processing result tokens.  marker is  e7(TDS5_DYNAMIC)
20:00:59 tds_get_string: reading 10 from wire to give 10 to client.
20:00:59 processing result tokens.  marker is  ff(DONEINPROC)
20:00:59 tds_process_end: more_results = 1
            was_cancelled = 0
            error = 0
            done_count_valid = 0
20:00:59 processing result tokens.  marker is  fe(DONEPROC)
20:00:59 tds_process_end: more_results = 0
            was_cancelled = 0
            error = 0
            done_count_valid = 0
20:00:59 tds_process_end() state set to TDS_IDLE
20:00:59 tds_process_result_tokens() state is COMPLETED
20:00:59 SQLFreeHandle(3, 0x400113f8)
20:00:59 tds_submit_unprepare() o167lo6000
20:00:59 tds_free_all_results()
20:00:59 Sending packet
0000 0f 01 00 1a 00 00 00 00-e7 00 0f 04 00 0a 6f 31 |........ ......o1|
0010 36 37 6c 6f 36 30 30 30-00 00                   |67lo6000 ..|

20:00:59 Received header
0000 04 01 00 21 00 00 00 00-                        |...!....|

20:00:59 Received packet
0000 e7 00 0d 20 00 0a 6f 31-36 37 6c 6f 36 30 30 30 |... ..o1 67lo6000|
0010 fd 00 00 00 02 00 00 00-00                      |........ .|

20:00:59 processing result tokens.  marker is  e7(TDS5_DYNAMIC)
20:00:59 tds_get_string: reading 10 from wire to give 10 to client.
20:00:59 processing result tokens.  marker is  fd(DONE)
20:00:59 tds_process_end: more_results = 0
            was_cancelled = 0
            error = 0
            done_count_valid = 0
20:00:59 tds_process_end() state set to TDS_IDLE
20:00:59 tds_process_result_tokens() state is COMPLETED
20:01:05 Creating prepared statement
20:01:05 tds_free_all_results()
20:01:05 Sending packet
0000 0f 01 00 54 00 00 00 00-e7 00 49 01 00 0a 6f 31 |...T.... ..I...o1|
0010 36 37 6c 6f 39 30 30 30-00 3a 63 72 65 61 74 65 |67lo9000 .:create|
0020 20 70 72 6f 63 20 6f 31-36 37 6c 6f 39 30 30 30 | proc o1 67lo9000|
0030 20 61 73 20 73 65 6c 65-63 74 20 63 6f 75 6e 74 | as sele ct count|
0040 28 2a 29 20 66 72 6f 6d-20 23 63 68 75 6e 67 74 |(*) from  #chungt|
0050 65 73 74 20            -                        |est |

20:01:05 Received header
0000 04 01 00 d0 00 00 00 00-                        |........|

20:01:05 Received packet
0000 e7 00 0d 20 00 0a 6f 31-36 37 6c 6f 39 30 30 30 |... ..o1 67lo9000|
0010 e5 00 ac 00 00 00 d0 01-10 05 34 32 30 30 30 00 |........ ..42000.|
0020 00 01 00 87 23 63 68 75-6e 67 74 65 73 74 20 6e |....#chu ngtest n|
0030 6f 74 20 66 6f 75 6e 64-2e 20 53 70 65 63 69 66 |ot found . Specif|
0040 79 20 6f 77 6e 65 72 2e-6f 62 6a 65 63 74 6e 61 |y owner. objectna|
0050 6d 65 20 6f 72 20 75 73-65 20 73 70 5f 68 65 6c |me or us e sp_hel|
0060 70 20 74 6f 20 63 68 65-63 6b 20 77 68 65 74 68 |p to che ck wheth|
0070 65 72 20 74 68 65 20 6f-62 6a 65 63 74 20 65 78 |er the o bject ex|
0080 69 73 74 73 20 28 73 70-5f 68 65 6c 70 20 6d 61 |ists (sp _help ma|
0090 79 20 70 72 6f 64 75 63-65 20 6c 6f 74 73 20 6f |y produc e lots o|
00a0 66 20 6f 75 74 70 75 74-29 2e 0a 06 49 33 5f 53 |f output )...I3_S|
00b0 31 35 0a 6f 31 36 37 6c-6f 39 30 30 30 00 01 fd |15.o167l o9000...|
00c0 00 02 00 02 00 00 00 00-                        |........|

20:01:05 processing result tokens.  marker is  e7(TDS5_DYNAMIC)
20:01:05 tds_get_string: reading 10 from wire to give 10 to client.
20:01:05 tds_get_string: reading 135 from wire to give 135 to client.
20:01:05 tds_get_string: reading 6 from wire to give 6 to client.
20:01:05 tds_get_string: reading 10 from wire to give 10 to client.
20:01:05 processing result tokens.  marker is  fd(DONE)
20:01:05 tds_process_end: more_results = 0
            was_cancelled = 0
            error = 1
            done_count_valid = 0
20:01:05 tds_process_end() state set to TDS_IDLE
20:01:05 SQLFreeHandle(3, 0x400113f8)
20:01:05 tds_submit_unprepare() o167lo9000
20:01:05 tds_free_all_results()
20:01:05 Sending packet
0000 0f 01 00 1a 00 00 00 00-e7 00 0f 04 00 0a 6f 31 |........ ......o1|
0010 36 37 6c 6f 39 30 30 30-00 00                   |67lo9000 ..|

20:01:05 Received header
0000 04 01 00 83 00 00 00 00-                        |........|

20:01:05 Received packet
0000 e7 00 0d 20 00 0a 6f 31-36 37 6c 6f 39 30 30 30 |... ..o1 67lo9000|
0010 e5 00 5f 00 00 02 4e 02-10 05 5a 5a 5a 5a 5a 00 |.._...N. ..ZZZZZ.|
0020 00 01 00 44 44 79 6e 61-6d 69 63 20 53 51 4c 20 |...DDyna mic SQL |
0030 65 72 72 6f 72 20 2d 20-50 72 65 70 61 72 65 64 |error -  Prepared|
0040 20 73 74 61 74 65 6d 65-6e 74 20 27 6f 31 36 37 | stateme nt 'o167|
0050 6c 6f 39 30 30 30 27 20-64 6f 65 73 20 6e 6f 74 |lo9000'  does not|
0060 20 65 78 69 73 74 2e 0a-06 49 33 5f 53 31 35 00 | exist.. .I3_S15.|
0070 00 01 fd 00 02 00 02 00-00 00 00                |........ ...|

20:01:05 processing result tokens.  marker is  e7(TDS5_DYNAMIC)
20:01:05 tds_get_string: reading 10 from wire to give 10 to client.
20:01:05 tds_get_string: reading 68 from wire to give 68 to client.
20:01:05 tds_get_string: reading 6 from wire to give 6 to client.
20:01:05 processing result tokens.  marker is  fd(DONE)
20:01:05 tds_process_end: more_results = 0
            was_cancelled = 0
            error = 1
            done_count_valid = 0
20:01:05 tds_process_end() state set to TDS_IDLE
20:01:06 tds_submit_unprepare() o167lo9000
20:01:06 tds_free_all_results()
20:01:06 Sending packet
0000 0f 01 00 1a 00 00 00 00-e7 00 0f 04 00 0a 6f 31 |........ ......o1|
0010 36 37 6c 6f 39 30 30 30-00 00                   |67lo9000 ..|

20:01:06 Received header
0000 04 01 00 83 00 00 00 00-                        |........|

20:01:06 Received packet
0000 e7 00 0d 20 00 0a 6f 31-36 37 6c 6f 39 30 30 30 |... ..o1 67lo9000|
0010 e5 00 5f 00 00 02 4e 02-10 05 5a 5a 5a 5a 5a 00 |.._...N. ..ZZZZZ.|
0020 00 01 00 44 44 79 6e 61-6d 69 63 20 53 51 4c 20 |...DDyna mic SQL |
0030 65 72 72 6f 72 20 2d 20-50 72 65 70 61 72 65 64 |error -  Prepared|
0040 20 73 74 61 74 65 6d 65-6e 74 20 27 6f 31 36 37 | stateme nt 'o167|
0050 6c 6f 39 30 30 30 27 20-64 6f 65 73 20 6e 6f 74 |lo9000'  does not|
0060 20 65 78 69 73 74 2e 0a-06 49 33 5f 53 31 35 00 | exist.. .I3_S15.|
0070 00 01 fd 00 02 00 02 00-00 00 00                |........ ...|

20:01:06 processing result tokens.  marker is  e7(TDS5_DYNAMIC)
20:01:06 tds_get_string: reading 10 from wire to give 10 to client.
20:01:06 tds_get_string: reading 68 from wire to give 68 to client.
20:01:06 tds_get_string: reading 6 from wire to give 6 to client.
20:01:06 processing result tokens.  marker is  fd(DONE)
20:01:06 tds_process_end: more_results = 0
            was_cancelled = 0
            error = 1
            done_count_valid = 0
20:01:06 tds_process_end() state set to TDS_IDLE
20:01:06 tds_free_all_results()

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--------------------------------------------------------

If you are not an intended recipient of this e-mail, please notify the 
sender, delete it and do not read, act upon, print, disclose, copy, retain or 
redistribute it. Click here for important additional terms relating to this 
e-mail.     http://www.ml.com/email_terms/
--------------------------------------------------------