Board index » delphi » Using TQUERY Parameters with MS-SQL 6.5

Using TQUERY Parameters with MS-SQL 6.5

Help!

I'm currently converting an Oracle base application to run on SQL
Server 6.5 and appear to be having a problem with TQUERY and
parameters.

If I hardcode the SQL statement as

SELECT * FROM COMPASS.FOCUS_ACCIDENTS WHERE REF = 199800285

Fine - no problems - sub-second response but if I use

SELECT * FROM COMPASS.FOCUS_ACCIDENTS WHERE REF = :REF

and PARMS[0].AsFloat := 199800285

the server disk light is on constant and it takes 40-50 seconds to get
the selected record

Is there a problem with using parameters with SQL 6.5 as the above
works fine with Oracle?

For info this is with Delphi 4.02/BDE 5.01

Cary.

 

Re:Using TQUERY Parameters with MS-SQL 6.5


Why are you using AsFloat instead of AsString???

Juan

Quote
Cary Moore wrote:

> Help!

> I'm currently converting an Oracle base application to run on SQL
> Server 6.5 and appear to be having a problem with TQUERY and
> parameters.

> If I hardcode the SQL statement as

> SELECT * FROM COMPASS.FOCUS_ACCIDENTS WHERE REF = 199800285

> Fine - no problems - sub-second response but if I use

> SELECT * FROM COMPASS.FOCUS_ACCIDENTS WHERE REF = :REF

> and PARMS[0].AsFloat := 199800285

> the server disk light is on constant and it takes 40-50 seconds to get
> the selected record

> Is there a problem with using parameters with SQL 6.5 as the above
> works fine with Oracle?

> For info this is with Delphi 4.02/BDE 5.01

> Cary.

Re:Using TQUERY Parameters with MS-SQL 6.5


Quote
>>Is there a problem with using parameters with SQL 6.5 as the above
>>works fine with Oracle?

Not that I have seen.  

What field type is REF on your SQL Server?

=Bill=

Re:Using TQUERY Parameters with MS-SQL 6.5


tquery seems to work better with 'paramsbyname' MsSql works better
if you use stored procedures either way the syntax for using the parameter
substitution is the same.

If Ref is an Integer try this:
query1.paramsbyname('ref').asinteger :=yourinteger.asinteger;
if Ref is a string =
query1.paramsbyname('ref').asstring :=yourstring.asstring
if Ref is a floating point =
query1.paramsbyname('ref').asfloat :=yourfloat.asfloat;

with MS SQL if possible - make your REF field an index

if you decide to use stored procedures change the
statment to:
Storedproc1.paramsbyname('@ref').asinteger := yourinteger.asinteger;

... Stored Procedures in MS SQL containing Parameters with an
"@"  i.e. @MyParameter

Hope this helps.

Mark Mueller

Re:Using TQUERY Parameters with MS-SQL 6.5


Many thanks for your replies - after a bit more investigation I found
that it was nothing to do with using parameters it was down to the fact
that I was using the same TDatabase connection as other queries.

If I provide different TDatabase connections to SQL Server then
everything works okay.

Does anyone know what sort of overhead/limits multiple connection will
have on SQL Server - is there any documentation on this "feature"?

Thanks
Cary.

Re:Using TQUERY Parameters with MS-SQL 6.5


Hi Cary (yes its me!)

Quote
>Many thanks for your replies - after a bit more investigation I found
>that it was nothing to do with using parameters it was down to the fact
>that I was using the same TDatabase connection as other queries.

>If I provide different TDatabase connections to SQL Server then
>everything works okay.

This should not be the case. You should not have to use a second TDatabase
for each query.

I would suggest changing your param setting to;

qry.ParamByName('REF').AsFloat := 199800285;
qry.Open;

Quote
>Does anyone know what sort of overhead/limits multiple connection will
>have on SQL Server - is there any documentation on this "feature"?

Well, if you connect 2 TDatabase components to SQL Server you will use 2
licences.
There is no problem in doing this, as long as you have sufficient licences.

Also, make sure you are running SQL Server 6.5 with the latest service pack
(4 I think).

If you are still having problems I will set up a small system on our test
server and replicate exacly what you are doing.

Cheers

John Hair
Designer Systems Ltd
(Remove .NOSPAM from address if responding by email)

Re:Using TQUERY Parameters with MS-SQL 6.5


MS SQL does not allow you to have two or more active queries on the same
database connection where one or more of the queries has not had the
entire result set fetched. That -may- or may not explain the problem.
It's the only reported issue that I know that may be related to your
problem.

Juan (TeamB)

Quote
Cary Moore wrote:

> Does anyone know what sort of overhead/limits multiple connection will
> have on SQL Server - is there any documentation on this "feature"?

> Thanks
> Cary.

Re:Using TQUERY Parameters with MS-SQL 6.5


Hi John, as you can see I've moved from Oracle to MS SQL

Quote
> >If I provide different TDatabase connections to SQL Server then
> >everything works okay.

> This should not be the case. You should not have to use a second TDatabase
> for each query.

The problem was due to something about MS SQL in that I had issued query to
populate a grid eg SELECT * FROM TABLE (4000+) and then issuing another Query
to retrieve a single record - both these queries were on the same TDatabase
Connection and as soon as the second TQuery was opened the first Query
decided it would first retrieve the rest of the records before the second
could execute.

Quote
> >Does anyone know what sort of overhead/limits multiple connection will
> >have on SQL Server - is there any documentation on this "feature"?
> Well, if you connect 2 TDatabase components to SQL Server you will use 2
> licences.
> There is no problem in doing this, as long as you have sufficient licences.

This didn't appear to be a problem - the application uses two connections and
we've had three people logging in to the application on out test 5 license
server.

I've been over in Oslo installing the system since my original posting, most
problem ironed out apart from storing in IMAGE columns...

Cary.

Other Threads