Skip to Content.
Sympa Menu

freetds - [freetds] FreeTDS ODBC and Sybase temporary table

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Charles Hung <chung AT caci.com>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] FreeTDS ODBC and Sybase temporary table
  • Date: Wed, 16 Nov 2005 12:35:15 -0500


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()





Archive powered by MHonArc 2.6.24.

Top of Page