problems using freetds with php & unixodbc
Axel Westhagen <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <[email protected]> |
> On Wed, 09 Oct 2013 10:56:09 +0200 > Axel Westhagen <[email protected]> wrote: > >> I add two trace files with the same statement execute from php and >> from isql. They are different. iodbc use sqlPrepare. > I suspect a NULL-handling error either in PHP or in your application. > SQL Server returns a row for SUM even if there are no rows, see below. > >> It seems there is a Problem at "Null value is eliminated by an >> aggregate or other SET operation" ? > That is not an error; it's a message from the server warning you that > the SUM function ignored NULLs in at least one row included in the > aggregation. The message is dictated by the ANSI standard. It can be > suppressed using SET, but the effect is the same whether or not the > message is produced. > > Here's a simple example: > > $ printf 'create table nt(t int null)\ngo\n insert nt values (NULL) > select sum(t) from nt\ngo\n drop table nt\ngo\n' | bsqlodbc .... > ---------- > NULL > bsqlodbc: error 8153: 01003: [FreeTDS][SQL Server]Warning: Null value > is eliminated by an aggregate or other SET operation. > > If you use TDSDUMP I'm sure you'll see the row sent from the server and > produced by the ODBC function that PHP calls. The problem you're > experiencing appears to be further up the stack. > > --jkl ok, I undestand this is my code in php: #$db = dbq_0($DBA['DBHOST'],$DBA['DBNAME'],$DBA['DBUSER'],$DBA['DBPASS']); #$sql = "select sum(vat_eur) from gi_costs_union"; #$rs = odbc_exec($db,$sql) or die(odbc_errormsg($db)); #echo odbc_result_all($rs); When I set the parameter SQL_CUR_USE_ODBC in the connection string it works! BUT then my Application has many other problems with updates or inserts, so I can not use it. maybe php misunderstand this warning or get multiple resultsets. so I tried using : #while (odbc_next_result($db)) $n = $n + 1; but no success Axel TSQLDUMP: 12:24:39.664588 16827 (odbc_export.h:225):SQLExecDirect(0xa230b10, select sum(vat_eur) from gi_costs_union, -3) 12:24:39.664610 16827 (prepare_query.c:201):parsing 0 parameters 12:24:39.664628 16827 (odbc.c:3208):_SQLExecute(0xa230b10) 12:24:39.664638 16827 (odbc.c:3225):_SQLExecute() starting with state 0 12:24:39.664649 16827 (query.c:2069):tds_cursor_declare() cursor id = 0 12:24:39.664660 16827 (query.c:2116):tds_cursor_open() cursor id = 0 12:24:39.664670 16827 (mem.c:598):tds_free_all_results() 12:24:39.664680 16827 (util.c:159):Changed query state from IDLE to QUERYING 12:24:39.664695 16827 (query.c:2246):tds_cursor_open (): RPC call set up 12:24:39.664705 16827 (query.c:2250):tds_cursor_open (): cursor open completed 12:24:39.664715 16827 (packet.c:133):Sending packet 0000 03 01 00 8c 00 00 01 00-ff ff 02 00 00 00 00 01 |........ ........| 0010 26 04 00 00 00 63 4e 00-00 00 09 04 f0 00 36 4e |&....cN. ......6N| 0020 00 00 00 73 00 65 00 6c-00 65 00 63 00 74 00 20 |...s.e.l .e.c.t. | 0030 00 73 00 75 00 6d 00 28-00 76 00 61 00 74 00 5f |.s.u.m.( .v.a.t._| 0040 00 65 00 75 00 72 00 29-00 20 00 66 00 72 00 6f |.e.u.r.) . .f.r.o| 0050 00 6d 00 20 00 67 00 69-00 5f 00 63 00 6f 00 73 |.m. .g.i ._.c.o.s| 0060 00 74 00 73 00 5f 00 75-00 6e 00 69 00 6f 00 6e |.t.s._.u .n.i.o.n| 0070 00 00 01 26 04 04 08 00-00 00 00 01 26 04 04 01 |...&.... ....&...| 0080 20 00 00 00 01 26 04 04-00 00 00 00 | ....&.. ....| 12:24:39.664829 16827 (util.c:159):Changed query state from QUERYING to PENDING 12:24:39.664845 16827 (token.c:525):tds_process_tokens(0xa272a50, 0xbfed7b74, 0xbfed7b70, 0x16900) 12:24:39.664857 16827 (util.c:159):Changed query state from PENDING to READING 12:24:42.342978 16827 (packet.c:108):Received packet 0000 04 01 01 43 00 33 01 00-81 02 00 00 00 01 00 6d |...C.3.. .......m| 0010 08 00 00 00 00 00 38 07-52 00 4f 00 57 00 53 00 |......8. R.O.W.S.| 0020 54 00 41 00 54 00 a5 06-00 01 00 04 02 00 14 ab |T.A.T... ........| 0030 ba 00 d9 1f 00 00 01 00-49 00 57 00 61 00 72 00 |........ I.W.a.r.| 0040 6e 00 69 00 6e 00 67 00-3a 00 20 00 4e 00 75 00 |n.i.n.g. :. .N.u.| 0050 6c 00 6c 00 20 00 76 00-61 00 6c 00 75 00 65 00 |l.l. .v. a.l.u.e.| 0060 20 00 69 00 73 00 20 00-65 00 6c 00 69 00 6d 00 | .i.s. . e.l.i.m.| 0070 69 00 6e 00 61 00 74 00-65 00 64 00 20 00 62 00 |i.n.a.t. e.d. .b.| 0080 79 00 20 00 61 00 6e 00-20 00 61 00 67 00 67 00 |y. .a.n. .a.g.g.| 0090 72 00 65 00 67 00 61 00-74 00 65 00 20 00 6f 00 |r.e.g.a. t.e. .o.| 00a0 72 00 20 00 6f 00 74 00-68 00 65 00 72 00 20 00 |r. .o.t. h.e.r. .| 00b0 53 00 45 00 54 00 20 00-6f 00 70 00 65 00 72 00 |S.E.T. . o.p.e.r.| 00c0 61 00 74 00 69 00 6f 00-6e 00 2e 00 0e 47 00 45 |a.t.i.o. n....G.E| 00d0 00 4f 00 2d 00 4c 00 4d-00 42 00 2d 00 57 00 38 |.O.-.L.M .B.-.W.8| 00e0 00 4b 00 53 00 51 00 4c-00 00 01 00 ff 11 00 c1 |.K.S.Q.L ........| 00f0 00 00 00 00 00 79 00 00-00 00 ac 0d 00 00 01 00 |.....y.. ........| 0100 00 00 00 26 04 04 f3 de-bc 0a ac 0d 00 00 01 00 |...&.... ........| 0110 00 00 00 26 04 04 08 00-00 00 ac 0d 00 00 01 00 |...&.... ........| 0120 00 00 00 26 04 04 01 00-00 00 ac 0d 00 00 01 00 |...&.... ........| 0130 00 00 00 26 04 04 01 00-00 00 fe 00 00 e0 00 00 |...&.... ........| 0140 00 00 00 - |...| 12:24:42.343136 16827 (token.c:540):processing result tokens. marker is 81(TDS7_RESULT) 12:24:42.343145 16827 (token.c:1475):processing TDS7 result metadata. 12:24:42.343152 16827 (mem.c:598):tds_free_all_results() 12:24:42.343162 16827 (token.c:1497):set current_results to cursor->res_info 12:24:42.343168 16827 (token.c:1507):setting up 2 columns 12:24:42.343176 16827 (token.c:1445):tds7_get_data_info: colname = (0 bytes) type = 109 (float-null) server's type = 109 (float-null) column_varint_size = 1 column_size = 8 (8 on server) 12:24:42.343187 16827 (token.c:1445):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) 12:24:42.343195 16827 (token.c:1516): name size/wsize type/wtype utype 12:24:42.343202 16827 (token.c:1517): -------------------- --------------- --------------- ------- 12:24:42.343209 16827 (token.c:1527): 8/8 109/109 0 12:24:42.343216 16827 (token.c:1527): ROWSTAT 4/4 56/56 0 12:24:42.343224 16827 (token.c:540):processing result tokens. marker is a5(COLINFO) 12:24:42.343230 16827 (token.c:107):tds_process_default_tokens() marker is a5(COLINFO) 12:24:42.343237 16827 (token.c:540):processing result tokens. marker is ab(INFO) 12:24:42.343244 16827 (token.c:107):tds_process_default_tokens() marker is ab(INFO) 12:24:42.343250 16827 (token.c:2154):tds_process_msg() reading message 8153 from server 12:24:42.343260 16827 (token.c:2225):tds_process_msg() calling client msg handler 12:24:42.343267 16827 (odbc.c:2283):msgno 8153 20003 12:24:42.343276 16827 (token.c:2238):tds_process_msg() returning TDS_SUCCESS 12:24:42.343283 16827 (util.c:159):Changed query state from READING to PENDING 12:24:42.343290 16827 (odbc.c:3554):odbc_process_tokens: tds_process_tokens returned 0 12:24:42.343296 16827 (odbc.c:3555): result_type=4044, TDS_DONE_COUNT=10, TDS_DONE_ERROR=0 12:24:42.343303 16827 (odbc.c:3627):odbc_process_tokens: returning result_type 4044 12:24:42.343309 16827 (mem.c:909):tds_cursor_deallocated() : freeing cursor_id 0 12:24:42.343316 16827 (mem.c:934):tds_cursor_deallocated() : cursor_id 0 found 12:24:42.343322 16827 (mem.c:938):tds_cursor_deallocated() : relinking list 12:24:42.343328 16827 (mem.c:945):tds_cursor_deallocated() : relinked list 12:24:42.343334 16827 (mem.c:961):tds_release_cursor() : freeing cursor_id 0 12:24:42.343340 16827 (mem.c:963):tds_release_cursor() : freeing cursor results 12:24:42.343346 16827 (mem.c:969):tds_release_cursor() : freeing cursor name 12:24:42.343353 16827 (mem.c:974):tds_release_cursor() : freeing cursor query 12:24:42.343358 16827 (mem.c:978):tds_release_cursor() : cursor_id 0 freed 12:24:42.343365 16827 (token.c:525):tds_process_tokens(0xa272a50, 0xbfed7b74, 0xbfed7b70, 0x6914) 12:24:42.343378 16827 (util.c:159):Changed query state from PENDING to READING 12:24:42.343385 16827 (token.c:540):processing result tokens. marker is ff(DONEINPROC) 12:24:42.343403 16827 (token.c:1905):tds_process_end: more_results = 1 was_cancelled = 0 error = 0 done_count_valid = 1 12:24:42.343412 16827 (token.c:1921): rows_affected = 0 12:24:42.343419 16827 (token.c:540):processing result tokens. marker is 79(RETURNSTATUS) 12:24:42.343425 16827 (token.c:540):processing result tokens. marker is ac(PARAM) 12:24:42.343432 16827 (token.c:601):processing parameters for sp 2 12:24:42.343438 16827 (token.c:603):calling tds_process_param_result 12:24:42.343444 16827 (token.c:1210):tds_process_param_result(0xa272a50, 0xbfed7b04) 12:24:42.343452 16827 (token.c:1551):tds_get_data_info(0xa272a50, 0x9093720, 1) [for parameter] 12:24:42.343459 16827 (token.c:1614):processing result. type = 38(integer-null), varint_size 1 12:24:42.343466 16827 (token.c:1619):processing result. column_size 4 12:24:42.343472 16827 (data.c:455):tds_get_data: type 38, varint size 1 12:24:42.343479 16827 (data.c:505):tds_get_data(): wire column size is 4 12:24:42.343485 16827 (log.c:491):type integer-null has value 180150003 12:24:42.343491 16827 (token.c:603):calling tds_process_param_result 12:24:42.343497 16827 (token.c:1210):tds_process_param_result(0xa272a50, 0xbfed7b04) 12:24:42.343504 16827 (token.c:1551):tds_get_data_info(0xa272a50, 0x9098cb0, 1) [for parameter] 12:24:42.343511 16827 (token.c:1614):processing result. type = 38(integer-null), varint_size 1 12:24:42.343517 16827 (token.c:1619):processing result. column_size 4 12:24:42.343523 16827 (data.c:455):tds_get_data: type 38, varint size 1 12:24:42.343529 16827 (data.c:505):tds_get_data(): wire column size is 4 12:24:42.343536 16827 (log.c:491):type integer-null has value 8 12:24:42.343542 16827 (token.c:603):calling tds_process_param_result 12:24:42.343548 16827 (token.c:1210):tds_process_param_result(0xa272a50, 0xbfed7b04) 12:24:42.343555 16827 (token.c:1551):tds_get_data_info(0xa272a50, 0xa230f10, 1) [for parameter] 12:24:42.343561 16827 (token.c:1614):processing result. type = 38(integer-null), varint_size 1 12:24:42.343568 16827 (token.c:1619):processing result. column_size 4 12:24:42.343574 16827 (data.c:455):tds_get_data: type 38, varint size 1 12:24:42.343580 16827 (data.c:505):tds_get_data(): wire column size is 4 12:24:42.343586 16827 (log.c:491):type integer-null has value 1 12:24:42.343592 16827 (token.c:603):calling tds_process_param_result 12:24:42.343598 16827 (token.c:1210):tds_process_param_result(0xa272a50, 0xbfed7b04) 12:24:42.343605 16827 (token.c:1551):tds_get_data_info(0xa272a50, 0x909a708, 1) [for parameter] 12:24:42.343612 16827 (token.c:1614):processing result. type = 38(integer-null), varint_size 1 12:24:42.343618 16827 (token.c:1619):processing result. column_size 4 12:24:42.343624 16827 (data.c:455):tds_get_data: type 38, varint size 1 12:24:42.343630 16827 (data.c:505):tds_get_data(): wire column size is 4 12:24:42.343637 16827 (log.c:491):type integer-null has value 1 12:24:42.343643 16827 (token.c:607):4 hidden return parameters 12:24:42.343649 16827 (token.c:540):processing result tokens. marker is fe(DONEPROC) 12:24:42.343656 16827 (token.c:1905):tds_process_end: more_results = 0 was_cancelled = 0 error = 0 done_count_valid = 0 12:24:42.343663 16827 (token.c:1921): rows_affected = 0 12:24:42.343669 16827 (token.c:1924):tds_process_end() state set to TDS_IDLE 12:24:42.343675 16827 (util.c:159):Changed query state from READING to IDLE 12:24:42.343681 16827 (util.c:99):logic error: cannot change query state from IDLE to PENDING 12:24:42.343687 16827 (util.c:159):Changed query state from IDLE to PENDING 12:24:42.343693 16827 (odbc.c:3554):odbc_process_tokens: tds_process_tokens returned 0 12:24:42.343699 16827 (odbc.c:3555): result_type=4052, TDS_DONE_COUNT=0, TDS_DONE_ERROR=0 12:24:42.343706 16827 (odbc.c:3600):odbc_process_tokens: processed TDS_DONE_RESULT 12:24:42.343718 16827 (token.c:525):tds_process_tokens(0xa272a50, 0xbfed7b74, 0xbfed7b70, 0x6914) 12:24:42.343725 16827 (token.c:528):tds_process_tokens() state is COMPLETED 12:24:42.343731 16827 (odbc.c:3554):odbc_process_tokens: tds_process_tokens returned 1 12:24:42.343737 16827 (odbc.c:3555): result_type=4052, TDS_DONE_COUNT=0, TDS_DONE_ERROR=0 12:24:42.343743 16827 (odbc.c:3379):_SQLExecute: odbc_process_tokens returned result_type 4046 12:24:42.343771 16827 (error_export.h:4):SQLGetDiagRec(3, 0xa230b10, 1, 0xbfed7c32, 0xbfed780c, 0xbfed7a32, 512, 0xbfed7808) 12:24:42.343787 16827 (error.c:575):SQLGetDiagRec: "[FreeTDS][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation." 12:24:42.343799 16827 (error_export.h:4):SQLGetDiagRec(3, 0xa230b10, 2, 0xbfed7c32, 0xbfed780c, 0xbfed7a32, 512, 0xbfed7808) 12:24:42.343810 16827 (odbc.c:4446):SQLNumResultCols(0xa230b10, 0x904104c) 12:24:42.343856 16827 (odbc.c:4002):SQLFreeHandle(3, 0xa230b10) 12:24:42.343865 16827 (odbc.c:4101):_SQLFreeStmt(0xa230b10, 1, 0) 12:24:42.343942 16827 (odbc.c:2249):SQLDisconnect(0x9104f00) 12:24:42.343956 16827 (mem.c:598):tds_free_all_results() 12:24:42.344023 16827 (util.c:159):Changed query state from IDLE to DEAD 12:24:42.344119 16827 (odbc.c:4064):SQLFreeConnect(0x9104f00) 12:24:42.344133 16827 (odbc.c:4028):_SQLFreeConnect(0x9104f00) 12:24:42.344140 16827 (odbc.c:4089):SQLFreeEnv(0xa272ec8) 12:24:42.344146 16827 (odbc.c:4074):_SQLFreeEnv(0xa272ec8)