MSSQL driver question

1

Hi,

I have question about MSSQL driver.

I have enabled LOG for MSSQL in Trace application. And there I have noticed that driver sends SELECT statement in WHERE section filed values with ?.

What I mean there are SELECT lines in log with values (A.Field1 < ?) OR (A.Field2 <= ?). As far I know ? does not work with MSSQL. Is it possible to disable this?

I have tried to COPY/PASTE line in Management Studio and I get syntax error for ?.

2

Those are parameterized values. The ? represents a parameter position. The actual value is substituted in.
This is normal and the proper way to have parameters in ODBC/SQL.

3

As Rick says those ? marks are just placeholders for actual values that are passed.
If you turn on Trace calls to back end, and Show Bound data you’ll see each ? has a matching ‘Binding…’ line eg (DebugView output truncated)

UPDATE dbo.USERS SET NO_LOG_ONS = ? WHERE USE_SQL_ID = ?
Binding ? 1 for input with C type SSHORT as SMALLINT value = 252
Binding ? 2 for input with C type SLONG as INTEGER value = 12

4

It’s also a little easier to see the parameters if you trace the statements using SQL Profiler.
Mitten Software has a free utility to take the statements from SQL Profiler and parse them into a statement you can execute in mgmt studio.
https://www.mittensoftware.com/Downloads.htm
The “Clipboard Fixed for Debugging Clarion SQL Browses” link.

2 Likes
5

I know that is valid value in some SQL databases, but I did not choose in Driver ODBC option. I have set it to MSSQL.

In Microsoft SQL that parameter it is not supported. You will get error:

image

And I saw that there is binding, but that is unnecessary since SQL returned error.

It looks like MSSQL and ODBC are using same code. As result of that for MSSQL option there would be invalid requests to server and also unnecessary processing of the code in Clarion. That is not efficient.

6

I also looked in SQL Profiler, and it looks like that data in Trace LOG file are actually incorrect.

In SQL Profiler requests are correct and SELECT statement have valid parameters.

7

Clarion only uses ODBC. The MSSQL driver is a special ODBC driver that understands MSSQL weird stuff. It uses the Windows MS SQL ODBC driver. So all the ? seems correct to me.

8

You got it wrong. You cannot simply copy such query from your trace file and execute it in Management Studio, but Sql Server obviously supports parameterized queries.

To execute such a query from Management Studio, you need to declare the parameters and set their values and of course they won’t appear as “?” in the query, e.g.:

DECLARE @Param1 INT = 123, @Param2 INT = 20

SELECT *
FROM log A
WHERE A.Field1 < @Param1 OR A.Field2 <= @Param2

So you can replace the “?” characters with appropriately declared variables, set their values based on the bindings visible in trace and then execute.

Of course you can also replace “?” characters with inline values, e.g.:

SELECT *
FROM log A
WHERE A.Field1 < 123 OR A.Field2 <= 20

in this case you also need to find the appropriate bindings in trace, check their values and substitute for the “?” characters.

Regards,
Andrzej

1 Like