SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,570 questions
How to bind the SQL query datetime string literal with datetime instead of datetime2 with SNAC 11 driver in case of SQLBindParameter().
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(48, 51%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESM%3C/text%3E%3C/svg%3E)
Srinivas M_025
0
Reputation points
While using the SNAC 11 driver for ODBC connection with our application, the query that is filtering on a datetime field and comparing it to a datetime value on the right of the comparison generates the SQL query with a program variable bound to the datetime2 datatype instead of datetime, which is not the case with the SQL Server driver. The following are the SQL statements observed using the SQL Server profiler for SNAC11 and the SQL Server driver, respectively:
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 int,@P2 datetime2',N'SELECT "prrowid", "custnum", "country", "name", "address", "address2", "city", "state", "postalcode", "contact", "phone", "salesrep", "creditlimit", "balance", "terms", "discount", "comments", "fax", "emailaddress", "Pro2SrcPDB", "pro2created", "pro2modified" FROM "sports2000"."dbo"."customer" WHERE (("custnum" > @P1) AND ("pro2created" <= @P2)) ORDER BY "prrowid" ',5,'2023-08-10 14:39:55.5770000'
select @p1
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 int,@P2 datetime',N'SELECT "prrowid", "custnum", "country", "name", "address", "address2", "city", "state", "postalcode", "contact", "phone", "salesrep", "creditlimit", "balance", "terms", "discount", "comments", "fax", "emailaddress", "Pro2SrcPDB", "pro2created", "pro2modified" FROM "sports2000"."dbo"."customer" WHERE (("custnum" > @P1) AND ("pro2created" <= @P2)) ORDER BY "prrowid" ',5,'2023-08-10 15:03:34.400'
select @p1
{count} votes
-
Erland Sommarskog 92,881 Reputation points2023-11-08T22:58:08.2666667+00:00 The SQL Server driver is way to old too know about
datetime2. How does your ODBC code look like? -
Srinivas M_025 0 Reputation points
2023-11-09T04:36:18.19+00:00 In the SNAC 11 driver, is there any way we can bind datetime datatype instead of datetime2 ? Currently, we are binding with the following configuration, as observed in the ODBC traces below:
prowin sp2sh - 1984-c38 ENTER SQLBindParameter HSTMT 0x00000006C8EF4F80 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 99 <SQL_C_DEFAULT> SWORD 11 <SQL_TIMESTAMP> SQLULEN 23 SWORD 3 PTR 0x00000006C9156110 SQLLEN 16 SQLLEN * 0x00000006C91560C8If there is any configuration in SNAC 11 driver or different datatype for binding with datetime kindly suggest.
-
Erland Sommarskog 92,881 Reputation points
2023-11-09T19:45:47.5966667+00:00 I don't do ODBC programming myself, but it appears that SQL_TYPE_TIMESTAMP is preferred over SQL_TIMESTAMP. I would not be surprised if this also results in datetime2, since this is a "better" data type. Are you having any issues with datetime2?
-
Srinivas M_025 0 Reputation points
2023-11-10T05:34:29.4666667+00:00 Thank you for the update, Erland.
Coming to your question, when filtering records on datetime in our application using the SNAC 11 ODBC connection, performance degrades when compared to the SQL Server driver.
The only difference I am able to figure out in the SQL Server profiler traces is that the SNAC 11 driver uses datetime2, whereas the SQL Server driver uses datetime in the query.
-
Erland Sommarskog 92,881 Reputation points
2023-11-10T07:48:20.6766667+00:00 Whlie ugly, you could try
cast(? as datetime)to see if that resolves the issue.
-
Srinivas M_025 0 Reputation points
2023-11-12T16:06:13.06+00:00 In which API can I use this statement? We are using the SQLPrepare() and SQLBindParameter() API's of ODBC for the C language in SQL query generation.
-
Erland Sommarskog 92,881 Reputation points
2023-11-13T01:02:03.3033333+00:00 You would change the query you submit from instead of reading:
WHERE (("custnum" > ?) AND ("pro2created" <= ?))To read
WHERE (("custnum" > ?) AND ("pro2created" <= cast(? as datetime)) -
Srinivas M_025 0 Reputation points
2023-11-13T01:17:56.15+00:00 Thank you for suggesting this. One question I have is whether it is the SNAC 11 driver's default behavior to prepare the SQL query using the datetime2 datatype, because even after trying all of the datatypes in SQLBindParameter(), it returns the same query. Please confirm this behavior.
-
Erland Sommarskog 92,881 Reputation points
2023-11-13T04:19:26.29+00:00 As I said previously, I have not done ODBC programming myself, and unfortunately, I don't have the time to research this issue myself, as I am currently in Seattle for the PASS Data Community Summit, and I have a very busy week ahead.
Sign in to comment