1

I've been at this for a little longer then I'd like to admit.

From a SQL Server database, I'm trying to run a dynamic OPENQUERY on a Firebird database, but I'm getting in all kinds of trouble with the datetime condition. Now, I'm trying to use a parametrized approach to circumvent potential issues with the datetime format, but getting a token unknown error. Any advise is appreciated; full details below:

FYI, I know the query is redundant, but I'm just trying to get it to work.

t-sql:

DECLARE @days INT
DECLARE @start DATETIME
DECLARE @end DATETIME
DECLARE @sql nVARCHAR(MAX)

SET @days = 1
SET @end = GETDATE()
SET @start = DATEADD(DAY,-@days,@end)
SET @sql = N'(SELECT OBJID FROM SALE WHERE MODIFIED >= @test1)'

DECLARE @TEST nvarchar(max)
SET @TEST = N'SELECT * FROM OPENQUERY(x,'+CHAR(39)+'SELECT * FROM SALE WHERE OBJID IN '+@sql+CHAR(39)+')'

EXEC sp_executesql @TEST, N'@test1 datetime', @start

error:

LE DB provider "MSDASQL" for linked server "x" returned message "[ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 77
@".

I also tried the non-parametrized approach, but couldn't figure out what formats to put the datetime in.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Guitoux
  • 23
  • 4
  • Firebird only supports `?` as parameter placeholders. – Mark Rotteveel Jan 19 '23 at 07:29
  • Also, [Datetime Literals](https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-commons-datetime-literal) (you should prefer the ISO-8601 format which is specified in the SQL standard, that is YYYY-MM-DD HH:mm:ss.ffff) – Mark Rotteveel Jan 19 '23 at 09:35
  • Thx I'll give this a try i.e., ? instead of @test1. i see it in the doc now that you've mentioned it just wish it was a little bit more detailed. – Guitoux Jan 19 '23 at 14:25
  • If you're missing something in the docs, don't hesitate to create a ticket in https://github.com/FirebirdSQL/firebird-documentation/issues, preferably with some details on what you're missing or would like to see improved, and I can see if I can improve the docs further. – Mark Rotteveel Jan 19 '23 at 14:29
  • thx sorry maybe i misspoke. it's really doing this from sql server to firebird that's thrown me off. appreciate your help. different error msg now. [ODBC Firebird Driver]COUNT field incorrect. I don't have a count field arg. – Guitoux Jan 19 '23 at 14:49
  • It might be that you have to explicitly enumerate the columns instead of just using select *. – Mark Rotteveel Jan 19 '23 at 15:01
  • Something else but thx anyhow – Guitoux Jan 19 '23 at 15:51
  • @MarkRotteveel any idea why this will not exec? SELECT * FROM OPENQUERY(x,'SELECT * FROM SALE WHERE OBJID IN (SELECT OBJID FROM SALE WHERE MODIFIED BETWEEN '2023-01-17 10:55:49.7333333' AND '2023-01-19 10:55:49.7333333')'); gives incorrect syntax near 2023 – Guitoux Jan 19 '23 at 16:58
  • It is probably because you didn't properly escape (double) the `'`, which prematurely terminates the string you're passing to SQL Server, but if you fix that, you'll run into the problem that Firebird only supports 4 fractional digits (100 microseconds), so the `.7333333` will produce a _"Dynamic SQL Error conversion error from string "2023-01-19 10:55:49.7333333""_, so you need to use `'2023-01-19 10:55:49.7333'` (or better, `timestamp '2023-01-19 10:55:49.7333'`) – Mark Rotteveel Jan 19 '23 at 17:20
  • ok that last part should be ok to fix. guess i'm not sure what you mean by escape quote, thought I had it down correctly but quotes always mess me up tbh. so this for example should work & i think the quotes are the right places...select * from openquery(x, 'SELECT OBJID FROM SALE WHERE MODIFIED >= '2023-01-19T11:40:09.3600'') – Guitoux Jan 19 '23 at 17:36
  • If you need a single quote inside a string literal, you need to double them, so, if you use `SET @TEST = N'select * from openquery(x, ''SELECT OBJID FROM SALE WHERE MODIFIED >= ''''2023-01-19T11:40:09.3600'''''')'` (double for quotes in the outer literal, quadruple for quotes inside the inner literal. Or if you use a plain statement, then `select * from openquery(x, 'SELECT OBJID FROM SALE WHERE MODIFIED >= ''2023-01-19T11:40:09.3600''')` – Mark Rotteveel Jan 20 '23 at 08:25

0 Answers0