Post by James K. LowdenOn Wed, 09 Oct 2013 10:56:09 +0200
Post by Axel WesthagenI 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.
Post by Axel WesthagenIt 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.
$ 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)