freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

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

From: Garrett Goebel <garrett AT scriptpro.com>
To: 'FreeTDS Development Group' <freetds AT lists.ibiblio.org>
Subject: [freetds] ODBC: syntax error? select datediff(d,{d 2003-04-15},{d 2003-04-1 5})
Date: Tue, 15 Apr 2003 15:39:00 -0500

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
>

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