FreeTDS Development Group

[Axel Westhagen <axel.westhagen AT limbas.com>]

> On Wed, 09 Oct 2013 10:56:09 +0200
> Axel Westhagen <axel.westhagen AT limbas.com> 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)