FreeTDS/odbc_exec using select aliases not working
Posted: Sat Mar 26, 2011 10:50 am
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:
Here's the var_dump which shows the unaliased fields:
Thanks.
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 FreeTDS log showing the field name:object(stdClass)#1 (1) {
["Sto_Pk"]=>
string(1) "2"
}
If I tried to reference the field "store" from the result of odbc_fetch_object, I will get the error: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.cprocessing TDS7 result metadata.
mem.c:563:tds_free_all_results()
token.cset current_results to cursor->res_info
token.csetting up column 0
token.ctds7_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.csetting up column 1
token.ctds7_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.ctds_process_end: more_results = 1
was_cancelled = 0
error = 0
done_count_valid = 1
token.crows_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.ctds_process_param_result(0x2b02ad0, 0x7fffc2f727a8)
token.cprocessing result. type = 38(integer-null), varint_size 1
Anyone seen this and have a solution? Hoping it's something simple.PHP Notice: Undefined property: stdClass::$store
Thanks.