Devart Forums

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

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

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

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

We're glad you have it resolved.
In Direct mode, we use parameter names to create a prepare statement when describing parameters for MS SQL Server 2008R2 or older.
We'll investigate a possibility to correctly display SQL statements when descirbing parameters.