FreeTDS/odbc_exec using select aliases not working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
CouchCaptain
Forum Newbie
Posts: 1
Joined: Sat Mar 26, 2011 10:12 am
Location: Dallas/Ft. Worth Texas, USA

FreeTDS/odbc_exec using select aliases not working

Post by CouchCaptain »

I have program which extracts reporting data from several MS SQL databases and writes the consolidated output into MySQL tables. It's a basic ETL app.

I had it running on an Windows machine and it worked fine. I've now moved to Ubuntu 10.10 64bit on AMD processor. I got the FreeTDS (64bit) and unixODBC working using protocol TDS_Version 8.0 in the connection. All the data comes through, but in a query I have a select with several "as" aliases. When I look at the TDS log it shows the aliased column names. When I dump the PHP object, it has the unaliased column names. I've search high and low and can't find the same discussion anywhere.

Example code that reproduces the problem:

Code: Select all

        $con = odbc_connect($dsn,$user,$pass); // this works fine...
        $list = odbc_exec($con,"select top 5 sto_pk as store from items");
        $stores = odbc_fetch_object($list);
        var_dump($stores);
Here's the var_dump which shows the unaliased fields:
object(stdClass)#1 (1) {
["Sto_Pk"]=>
string(1) "2"
}
Here's the FreeTDS log showing the field name:
net.c:671:Received packet
0000 81 02 00 00 00 08 00 34-05 73 00 74 00 6f 00 72 |.......4 .s.t.o.r|
0010 00 65 00 00 00 00 00 38-07 52 00 4f 00 57 00 53 |.e.....8 .R.O.W.S|
0020 00 54 00 41 00 54 00 a4-0d 00 01 05 00 69 00 74 |.T.A.T.. .....i.t|
0030 00 65 00 6d 00 73 00 a5-13 00 01 01 20 06 53 00 |.e.m.s.. .... .S.|
0040 74 00 6f 00 5f 00 50 00-6b 00 02 00 14 ff 11 00 |t.o._.P. k.......|
0050 c1 00 00 00 00 00 79 00-00 00 00 ac 0d 00 00 01 |......y. ........|
0060 00 00 00 00 26 04 04 f3-de bc 0a ac 0d 00 00 01 |....&... ........|
0070 00 00 00 00 26 04 04 08-00 00 00 ac 0d 00 00 01 |....&... ........|
0080 00 00 00 00 26 04 04 01-00 00 00 ac 0d 00 00 01 |....&... ........|
0090 00 00 00 00 26 04 04 05-00 00 00 fe 00 00 e0 00 |....&... ........|
00a0 00 00 00 00 - |....|

token.c:510:processing result tokens. marker is 81(TDS7_RESULT)
token.c:1520:processing TDS7 result metadata.
mem.c:563:tds_free_all_results()
token.c:1541:set current_results to cursor->res_info
token.c:1555:setting up column 0
token.c:1490:tds7_get_data_info:
colname = store (5 bytes)
type = 52 (smallint)
server's type = 52 (smallint)
column_varint_size = 0
column_size = 2 (2 on server)
token.c:1555:setting up column 1
token.c:1490:tds7_get_data_info:
colname = ROWSTAT (7 bytes)
type = 56 (int)
server's type = 56 (int)
column_varint_size = 0
column_size = 4 (4 on server)
token.c:510:processing result tokens. marker is ff(DONEINPROC)
token.c:2201:tds_process_end: more_results = 1
was_cancelled = 0
error = 0
done_count_valid = 1
token.c:2232: rows_affected = 0
token.c:510:processing result tokens. marker is 79(RETURNSTATUS)
token.c:510:processing result tokens. marker is ac(PARAM)
token.c:567:processing parameters for sp 2
token.c:569:calling tds_process_param_result
token.c:1198:tds_process_param_result(0x2b02ad0, 0x7fffc2f727a8)
token.c:1596:processing result. type = 38(integer-null), varint_size 1
If I tried to reference the field "store" from the result of odbc_fetch_object, I will get the error:
PHP Notice: Undefined property: stdClass::$store
Anyone seen this and have a solution? Hoping it's something simple.
Thanks.
Post Reply