[freetds] ODBC: syntax error? select datediff(d,{d 2003-04-15},{d 2003-04-1 5})

Garrett Goebel garrett at scriptpro.com
Tue Apr 15 16:39:00 EDT 2003


Any ideas on why 

  select datediff(d,{d 2003-04-15},{d 2003-04-15})

Would result in a syntax error?

Are the ODBC escape sequences for representing date and time values not
supported?

Any examples of working syntax for specifying a date, time, or datetime
value via ODBC?

I'm using:

Linux:     Gentoo 1.4
MSSQL:     7.0sp2
unixODBC:  2.0.6
FreeTDS:   0.61
Perl:      5.8.0
DBI:       1.35
DBD::ODBC: 1.05

Here's the perl script:
#!/usr/bin/perl -w
use DBI;
my $dbh = DBI->connect(
 
'dbi:ODBC:ServerName=RD_Q;Driver=TDS;Database=SPCentral;TDS_Version=4.2',
    'sa',
    'centralsp',
    {RaiseError => 1, AutoCommit => 1}
) or die DBI->errstr;
#$dbh->trace(1);
my $sth = $dbh->prepare("select datediff(d,{d '2003-04-15'},{d
'2003-04-14'}");
$sth->execute();
my $data;
while ($data = $sth->fetch) {
    print join(', ', @$data), "\n";
}

$dbh->disconnect;
1;
__END__


Here's the FreeTDS dump:
[...]
2003-04-15 09:04:14.689185 inside tds_process_result_tokens() state is
COMPLETED
Sending packet @ 2003-04-15 09:04:14.689699
0000  01 01 00 33 00 00 00 00  73 65 6c 65 63 74 20 64   |...3.... select d|
0010  61 74 65 64 69 66 66 28  64 2c 27 32 30 30 33 2d   |atediff( d,'2003-|
0020  30 34 2d 31 35 27 2c 27  32 30 30 33 2d 30 34 2d   |04-15',' 2003-04-|
0030  31 34 27                                           |14'|


Received header @ 2003-04-15 09:04:14.690948
0000  04 01 00 4f 00 00 00 00                            |...O....|


Received packet @ 2003-04-15 09:04:14.691020
0000  aa 3b 00 aa 00 00 00 01  0f 2b 00 4c 69 6e 65 20   |.;...... .+.Line |
0010  31 3a 20 49 6e 63 6f 72  72 65 63 74 20 73 79 6e   |1: Incor rect syn|
0020  74 61 78 20 6e 65 61 72  20 27 32 30 30 33 2d 30   |tax near  '2003-0|
0030  34 2d 31 34 27 2e 04 52  44 5f 51 00 01 00 fd 02   |4-14'..R D_Q.....|
0040  00 fd 00 00 00 00 00                               |.......|


2003-04-15 09:04:14.691166 processing result tokens.  marker is  aa(ERROR)
2003-04-15 09:04:14.691217 inside tds_process_default_tokens() marker is
aa(ERROR)
2003-04-15 09:04:14.691288 processing result tokens.  marker is  fd(DONE)
2003-04-15 09:04:14.691339 inside tds_process_end() more_results = 0,
was_cancelled = 0
SQLFreeHandle(3, 0x824edb0)
SQLFreeHandle(2, 0x81a0258)
SQLFreeHandle(1, 0x8170d90)
--
Garrett Goebel
IS Development Specialist

ScriptPro                   Direct: 913.403.5261
5828 Reeds Road               Main: 913.384.1008
Mission, KS 66202              Fax: 913.384.2180
www.scriptpro.com          garrett at scriptpro.com



> -----Original Message-----
> From: Garrett Goebel [mailto:garrett at scriptpro.com]
> Sent: Tuesday, April 15, 2003 1:45 PM
> To: 'FreeTDS Development Group'
> Subject: [freetds] ODBC: { ? = CALL myproc() } [x-adr][x-bayes]
> 
> 
> I've got a problem using ODBC CALL syntax with inout bound 
> parameters to get
> at a stored procedure's return value.  
> 
> I'm using...
> 
> Linux:     Gentoo 1.4
> MSSQL:     7.0sp2
> unixODBC:  2.0.6
> FreeTDS:   0.61
> Perl:      5.8.0
> DBI:       1.35
> DBD::ODBC: 1.05
> 
> I've got 3 different variations on the same thing that have different
> outcomes:
> o  syntax error on valid syntax
> o  segmentation fault on valid syntax
> o  no error, but unexpected result
> 
> 1) If I create a stored procedure:
> 
>   create procedure odbc_call as return 1
> 
> and a perl script:
> 
> #!usr/bin/perl -w
> use DBI;
> my $rv = 0;
> my $dbh = DBI->connect(
>     'dbi:ODBC:ServerName=RD_Q;Driver=TDS;Database=SPCentral',
>     'sa', 'mypasswd',
>     {RaiseError => 1, AutoCommit => 1}
> ) or die DBI->errstr;
> $dbh->trace(1);
> my $sth = $dbh->prepare('{ ? = call odbc_call() }');
> $sth->bind_param_inout(1, \$rv, 4);
> $sth->execute();
> $sth->finish();
> print "rv: $rv\n";
> $dbh->disconnect;
> 1;
> __END__
> 
> 
> I get an error during execute:
> 
> [BEGIN: DBI trace level 1]
>     DBI::db=HASH(0x8240a10) trace level set to 1 in DBI 1.35-nothread
>     <- prepare('{ ? = call odbc_call() }')= DBI::st=HASH(0x8240f0c) at
> odbc_call.pl line 14
>     <- bind_param_inout(1 SCALAR(0x812cf10) ...)= 1 at 
> odbc_call.pl line 15
>     !! ERROR: -1 '[unixODBC] Msg 170, Level 15, State 1, 
> Server RD_Q, Line 1
> Line 1: Incorrect syntax near '0'.
>  (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)'
>     <- execute= undef at odbc_call.pl line 16
> DBD::ODBC::st execute failed: [unixODBC] Msg 170, Level 15, 
> State 1, Server
> RD_Q, Line 1
> [END: DBI trace level 1]
> 
> [BEGIN: hopefully relevant portion of FreeTDS dump file]
> Starting log file with debug level 99.
> 2003-04-15 07:11:11.344391 iconv will convert client-side 
> data to the ""
> character set
> 2003-04-15 07:11:11.344804 Connecting addr 192.168.197.29 
> port 1433 with TDS
> version 7.0
> 2003-04-15 07:11:11.345677 inside tds_process_login_tokens()
> 
> [...]
> 
> 2003-04-15 07:11:11.355554 processing result tokens.  marker 
> is  fd(DONE)
> 2003-04-15 07:11:11.355604 inside tds_process_end() more_results = 0,
> was_cancelled = 0
> 2003-04-15 07:11:11.355663 inside tds_process_result_tokens() state is
> COMPLETED
> Sending packet @ 2003-04-15 07:11:11.356692
> 0000  01 01 00 36 00 00 01 00  27 00 30 00 27 00 20 00   
> |...6.... '.0.'. .|
> 0010  3d 00 20 00 63 00 61 00  6c 00 6c 00 20 00 6f 00   |=. 
> .c.a. l.l. .o.|
> 0020  64 00 62 00 63 00 5f 00  63 00 61 00 6c 00 6c 00   
> |d.b.c._. c.a.l.l.|
> 0030  28 00 29 00 20 00                                  |(.). .|
> 
> Received header @ 2003-04-15 07:11:11.357944
> 0000  04 01 00 6c 00 00 00 00                            |...l....|
> 
> Received packet @ 2003-04-15 07:11:11.358016
> 0000  aa 58 00 aa 00 00 00 01  0f 22 00 4c 00 69 00 6e   
> |.X...... .".L.i.n|
> 0010  00 65 00 20 00 31 00 3a  00 20 00 49 00 6e 00 63   |.e. 
> .1.: . .I.n.c|
> 0020  00 6f 00 72 00 72 00 65  00 63 00 74 00 20 00 73   
> |.o.r.r.e .c.t. .s|
> 0030  00 79 00 6e 00 74 00 61  00 78 00 20 00 6e 00 65   
> |.y.n.t.a .x. .n.e|
> 0040  00 61 00 72 00 20 00 27  00 30 00 27 00 2e 00 04   
> |.a.r. .' .0.'....|
> 0050  52 00 44 00 5f 00 51 00  00 01 00 fd 02 00 fd 00   
> |R.D._.Q. ........|
> 0060  00 00 00 00                                        |....|
> 
> 
> 2003-04-15 07:11:11.358211 processing result tokens.  marker 
> is  aa(ERROR)
> 2003-04-15 07:11:11.358261 inside 
> tds_process_default_tokens() marker is
> aa(ERROR)
> 2003-04-15 07:11:11.358338 processing result tokens.  marker 
> is  fd(DONE)
> 2003-04-15 07:11:11.358389 inside tds_process_end() more_results = 0,
> was_cancelled = 0
> SQLFreeHandle(3, 0x8244150)
> SQLFreeHandle(2, 0x8139358)
> SQLFreeHandle(1, 0x81b4b10)
> [END: hopefully relevant portion of FreeTDS dump file]
> 
> 
> 2) If I alter the prepared statement to remove all 
> unnecessary whitespace:
> 
>   {?=call odbc_call()}
> 
> I get a segmentation fault. The segfault goes away if I:
> o  place a space between the opening '{' and '?'
>    { ?=call odbc_call()}
> o  alter the stored proc to return a result set
>    create procedure odbc_call as select 1 return 1
> 
> [BEGIN: DBI Trace level 1]
>     DBI::db=HASH(0x8240a10) trace level set to 1 in DBI 1.35-nothread
>     <- prepare('{?=call odbc_call()}')= DBI::st=HASH(0x8240f0c) at
> odbc_call.pl line 14
>     <- bind_param_inout(1 SCALAR(0x812cf10) ...)= 1 at 
> odbc_call.pl line 15
>     <- execute= '0E0' at odbc_call.pl line 16
>     <- finish= 1 at odbc_call.pl line 17
> Segmentation fault
> [END: DBI Trace level 1]
> 
> [BEGIN: hopefully relevant portion of FreeTDS dump file]
> Starting log file with debug level 99.
> 2003-04-15 07:00:15.650551 iconv will convert client-side 
> data to the ""
> character set
> 2003-04-15 07:00:15.650961 Connecting addr 192.168.197.29 
> port 1433 with TDS
> version 7.0
> 2003-04-15 07:00:15.651584 inside tds_process_login_tokens()
> 
> [...]
> 
> 2003-04-15 07:00:15.661354 processing result tokens.  marker 
> is  fd(DONE)
> 2003-04-15 07:00:15.661405 inside tds_process_end() more_results = 0,
> was_cancelled = 0
> 2003-04-15 07:00:15.661464 inside tds_process_result_tokens() state is
> COMPLETED
> Sending packet @ 2003-04-15 07:00:15.662462
> 0000  01 01 00 28 00 00 01 00  65 00 78 00 65 00 63 00   
> |...(.... e.x.e.c.|
> 0010  20 00 6f 00 64 00 62 00  63 00 5f 00 63 00 61 00   | 
> .o.d.b. c._.c.a.|
> 0020  6c 00 6c 00 20 00 20 00                            |l.l. . .|
> 
> Received header @ 2003-04-15 07:00:15.664235
> 0000  04 01 00 1f 00 00 00 00                            |........|
> 
> Received packet @ 2003-04-15 07:00:15.664307
> 0000  ff 11 00 c1 00 01 00 00  00 79 01 00 00 00 fe 00   
> |........ .y......|
> 0010  00 e0 00 01 00 00 00                               |.......|
> 
> 2003-04-15 07:00:15.664384 processing result tokens.  marker is
> ff(DONEINPROC)
> 2003-04-15 07:00:15.664436 inside tds_process_end() more_results = 1,
> was_cancelled = 0
> 2003-04-15 07:00:15.664495 processing result tokens.  marker is
> 79(RETURNSTATUS)
> convert_tds2sql: src is 56 dest = 12
> 2003-04-15 07:00:15.664597 processing result tokens.  marker is
> fe(DONEPROC)
> 2003-04-15 07:00:15.664649 inside tds_process_end() more_results = 0,
> was_cancelled = 0
> 2003-04-15 07:00:15.664708 inside tds_process_result_tokens() state is
> COMPLETED
> 2003-04-15 07:00:15.664798 inside tds_process_result_tokens() state is
> COMPLETED
> [END: hopefully relevant portion of FreeTDS dump file]
> 
> 
> 3) If I then alter the stored procedure to return something:
> 
>   create procedure odbc_call as select 1 return 1 
> 
> I get no DBI error, however the bound inout parameter $rv 
> does not receive
> the return value.
> 
> [BEGIN: Hopefully relevant portion of FreeTDS dump file]
> 2003-04-15 07:14:19.265838 inside tds_process_result_tokens() state is
> COMPLETED
> Sending packet @ 2003-04-15 07:14:19.266850
> 0000  01 01 00 28 00 00 01 00  65 00 78 00 65 00 63 00   
> |...(.... e.x.e.c.|
> 0010  20 00 6f 00 64 00 62 00  63 00 5f 00 63 00 61 00   | 
> .o.d.b. c._.c.a.|
> 0020  6c 00 6c 00 20 00 20 00                            |l.l. . .|
> 
> Received header @ 2003-04-15 07:14:19.269302
> 0000  04 01 00 36 00 00 00 00                            |...6....|
> 
> Received packet @ 2003-04-15 07:14:19.269375
> 0000  81 01 00 00 00 00 00 38  00 d1 01 00 00 00 ff 11   
> |.......8 ........|
> 0010  00 c1 00 01 00 00 00 ff  11 00 c1 00 01 00 00 00   
> |........ ........|
> 0020  79 01 00 00 00 fe 00 00  e0 00 01 00 00 00         
> |y....... ......|
> 
> 2003-04-15 07:14:19.269483 processing result tokens.  marker is
> 81(TDS7_RESULT)
> 2003-04-15 07:14:19.269539 tds7_get_data_info:1083:
>         type = 56 (int)
>         column_varint_size = 0
>         colname =
>         colnamelen = 0
> odbc:SQLColAttributes: fDescType is 6
> odbc:SQLColAttributes: fDescType is 3
> Sending packet @ 2003-04-15 07:14:19.269886
> 0000  06 01 00 08 00 00 01 00                            |........|
> 
> 2003-04-15 07:14:19.269978 inside 
> tds_process_default_tokens() marker is
> d1(ROW)
> 2003-04-15 07:14:19.270036 processing row.  column is 0 
> varint size = 0
> 2003-04-15 07:14:19.270087 processing row.  column size is 4
> 2003-04-15 07:14:19.270133 clearing column 0 NULL bit
> 2003-04-15 07:14:19.270177 inside 
> tds_process_default_tokens() marker is
> ff(DONEINPROC)
> 2003-04-15 07:14:19.270231 inside tds_process_end() more_results = 1,
> was_cancelled = 0
> 2003-04-15 07:14:19.270290 inside 
> tds_process_default_tokens() marker is
> ff(DONEINPROC)
> 2003-04-15 07:14:19.270344 inside tds_process_end() more_results = 1,
> was_cancelled = 0
> 2003-04-15 07:14:19.270402 inside 
> tds_process_default_tokens() marker is
> 79(RETURNSTATUS)
> 2003-04-15 07:14:19.270457 inside 
> tds_process_default_tokens() marker is
> fe(DONEPROC)
> 2003-04-15 07:14:19.270510 inside tds_process_end() more_results = 0,
> was_cancelled = 0
> 2003-04-15 07:14:19.270568 inside 
> tds_process_default_tokens() setting state
> to COMPLETED
> Received header @ 2003-04-15 07:14:19.270643
> 0000  04 01 00 11 00 00 00 00                            |........|
> 
> Received packet @ 2003-04-15 07:14:19.270713
> 0000  fd 20 00 00 00 00 00 00  00                        |. ...... .|
> 
> 2003-04-15 07:14:19.270767 inside tds_process_end() more_results = 0,
> was_cancelled = 1
> SQLFreeHandle(2, 0x8139358)
> SQLFreeHandle(1, 0x81b4b10)
> [END: Hopefully relevant portion of FreeTDS dump file]
> 
> --
> Garrett Goebel
> IS Development Specialist
> 
> ScriptPro                  Direct: 913.403.5261
> 5828 Reeds Road            Main:   913.384.1008
> Mission, KS 66202          Fax:    913.384.2180
> www.scriptpro.com          garrett at scriptpro dot com
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 



More information about the FreeTDS mailing list