SQL Server Direct Mode errors on PREPARE

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
tonymeadors
Posts: 42
Joined: Wed 28 Feb 2007 17:56

SQL Server Direct Mode errors on PREPARE

Post by tonymeadors » Tue 14 Jan 2020 15:34

We are moving to Direct mode in our code to satisfy
clients tightening security with TLS 1.2.

When we test prDirect as the OLEDBPRovider we do get
various SQL complaints we will have to work through.

Perhaps I am missing something but I am surprised at
the errors all our PREPARE statements get:

//simple test code against 2008 express
q4.sql.text:='select id from cd_med where com_set_amt > :csa' ;
q4.prepare;

//error is
Invalid column name :csa

//or
q4.sql.text:='select id from cd_med where com_set_amt > :csa' and id < :cutoff';
q4.prepare;

//error is
Invalid column name :csa
invalid column name :cutoff

I'm missing something I'm sure...

thanks,
tonyM

tonymeadors
Posts: 42
Joined: Wed 28 Feb 2007 17:56

Re: SQL Server Direct Mode errors on PREPARE

Post by tonymeadors » Wed 15 Jan 2020 15:11

I found a couple of locations where the prepare was not erroring,
so naturally I began trial and error investigation.

It seems the SQL Server prDirect mode (in my current testing against SQL 2008 anyway)
requires the parameter names to match field names.

In the past, before a loop we might do something like:
q4.sql.text:='select id from cd_med where com_set_amount > :csa and id < :cutoff';
q4.prepare;

But that gives an error in prDirect mode to SQL Server 2008:
Invalid column name 'cutoff'
Invalid column name 'csa'

Changing the parameter names to match existing fieldnames seems to help
q4.sql.text:='select id from cd_med where com_set_amount > :com_set_amount and id < :id';
q4.prepare;

I can probably use this workaround throughout.

Q: most of these prepare cases were written 15 years ago (usually for inserts).
We hit Firebird, SQL server and Oracle in different client cases.
Is it still considered good practice to prepare in this manner if you
have parameterized SQL within a tight loop of thousands of executions?

tonyM

Stellar
Devart Team
Posts: 350
Joined: Tue 03 Oct 2017 11:00

Re: SQL Server Direct Mode errors on PREPARE

Post by Stellar » Thu 16 Jan 2020 10:56

Unfortunately, we can't reproduce the issue. To investigate this behavior of UniDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

tonymeadors
Posts: 42
Joined: Wed 28 Feb 2007 17:56

Re: SQL Server Direct Mode errors on PREPARE

Post by tonymeadors » Thu 16 Jan 2020 18:05

I began building a sample app to send,
and as often happens I ran across what looks like the issue.

When connected to SQL Server we initialize every query object with:
SpecificOptions.Values['DescribeParams'] := 'TRUE';

This was added some years ago to speed up parameter filling.
(I have a couple of pages of test notes from those tests and the workaround found)

Anyway, when connected to SQL Server 2008 using prDirect,
with the DESCRIBEPARAMS set to True a PREPARE errors for param names that
are arbitrary as in:

insert into cd_med(id)values(:xyzabc);

But if you use paramnames that match an existing column it allows.

If a full app example would still help let me know.
But I think we can work around everything just by using
param names that are acceptable (match fieldnames).

Thanks,
tonyM

Post Reply