:( OPENQUERY Problem passing parameters

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply

Would you like to see DEVART install this feature?

 
Total votes: 0

mkintner
Posts: 2
Joined: Wed 19 Nov 2008 15:14

:( OPENQUERY Problem passing parameters

Post by mkintner » Wed 19 Nov 2008 15:25

:( Passing Parameters into a SQL Query using OPENQUERY.

Below is the syntax for using OPENQUERY:

Code: Select all

SELECT * FROM OPENQUERY([AS400_Test], 'Select * From QS36FTEST.ZBBORDH') RS_ORHD
OPENQUERY is used through SQL Server to OPEN other ODBC sources within the SQL Server however when using OPENQUERY because of the single quotes the parameters do not work inside of the OPENQUERY. It appears that your component will not take into effect the single quotes. Is there a work around to using parameters with OPENQUERY?

Example:

Code: Select all

SELECT * FROM OPENQUERY([AS400_Test], 'Select * From QS36FTEST.ZBBORDH WHERE TEMP=:inTEMP') RS_ORHD
It appears your component does not reconize the parameter :inTEMP and is because the parameter is inside the single quotes of the OPENQUERY statement. Do you have a solution for this. Current we are re-writing each SQL statement on the fly however would like to use parameters.

Can you help? :)

dhastas
Posts: 2
Joined: Wed 19 Nov 2008 15:29

Post by dhastas » Wed 19 Nov 2008 15:35

That would be a great feature I have experienced this several times
and would love to have a solution

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 21 Nov 2008 09:59

SDAC does not allow using parameters inside of SQL Statement of OPENQUERY, because it is a parameter of OPENQUERY function oneself.

mkintner
Posts: 2
Joined: Wed 19 Nov 2008 15:14

Post by mkintner » Fri 21 Nov 2008 12:42

This may be true, however why couldn't the DevART's SQL component have a feature to handle single quotes inside of the OPENQUERY? The component could have a feature request to reconize OPENQUERY and then be able to handle the paramaters passed within the OPENQUERY.

This option is becoming more and more needed with the advent of SQL server being able to handle multiple systems within the SQL environment. The feature would make DevART components more powerful to handling multiple database systems not to mention convience.

I would only hope that DevART takes some sort of action vs just ignoring the issue by not trying to providing some sort of workable solution. :)

bajaluna
Posts: 1
Joined: Fri 21 Nov 2008 12:52

Post by bajaluna » Fri 21 Nov 2008 13:02

You know you are correct this would be a great feature!

I use both the SQL and Oracle through Adelphia Communications or now Time Warner and we have requested this feature as well but with no luck in supporting a change.

A feature like this could be done by DevART but they only seem to build requests they deem useful. Its too bad because we use there component in alot of our applications. I'll send a private message to you on who we use to perform this feature, they built this feature for us in there last update and works greats.

Hopefully others will vote your poll and I will pass the word at Time Warner.

Good luck...

dhastas
Posts: 2
Joined: Wed 19 Nov 2008 15:29

Post by dhastas » Fri 21 Nov 2008 14:28

That would be great if you have a solution to this as it would be
very useful. It would be GREAT if you could share this solution with all.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 26 Nov 2008 15:12

Restriction of using parameters inside of OPENQUERY is a peculiarity of SQL Server work and doesn't depend on SDAC.
SQL Server 2005 Books Online in remarks to OPENQUERY statement say:
OPENQUERY does not accept variables for its arguments.

Post Reply