Prepare TMSQuery error

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

Prepare TMSQuery error

Post by jan.javurek@floresps.cz » Tue 07 Dec 2010 00:46

Hello,

I have a problem with certain types of PREPARE SQL SELECT queries.

Parameters to the SQL query I can not set using Query.Params [0]. ASSTRING ... because the parameters do not know. This is a test application of SQL queries. QUERY.Prepare is used to determine the syntactic correct SQL query.


==========================================
Variant A - Query.Options.DescribeParams := True;

var
Query: TMSQuery;
...
begin
...
Query.Options.DescribeParams := True;
Query.SQL.Text := INSERT SQL CODE;
Query.Prepare;


1.
SELECT Name FROM sys.tables WHERE UPPER(Name) = UPPER(:TableName)
or
SELECT Code FROM StoreCards WHERE UPPER(Name) = UPPER(:Name)

Prepare Error
---------------------------
Incorrect syntax near ')'.
---------------------------

SELECT Name FROM sys.tables WHERE UPPER(Name) = UPPER('test')
is OK
SELECT Code FROM StoreCards WHERE UPPER(Name) = UPPER('test')
is OK

2.
SELECT ID FROM PaymentsForDocument(:APDocumentType, :APDocument_ID, :AEndDate)

Prepare Error
---------------------------
Syntax error, permission violation, or other nonspecific error.
---------------------------


SELECT ID FROM PaymentsForDocument('', '', 0)
is OK

PaymentsForDocument is table function:
ALTER FUNCTION [dbo].[PaymentsForDocument]
(
@APDocumentType CHAR(2),
@APDocument_ID CHAR(10),
@AEndDate FLOAT
)
RETURNS @PaymentsForDocument_TABLE TABLE
(
ID CHAR(10) COLLATE Czech_CS_AS,
DocumentType CHAR(2) COLLATE Czech_CS_AS,
DocDate$DATE FLOAT,
Amount NUMERIC(15, 3),
LocalAmount NUMERIC(15, 3),
PAmount NUMERIC(15, 3),
Currency_ID VARCHAR(10) COLLATE Czech_CS_AS,
CashDesk_ID VARCHAR(10) COLLATE Czech_CS_AS,
BankAccount_ID VARCHAR(10) COLLATE Czech_CS_AS,
OrdNumber INTEGER,
DocQueue_ID CHAR(10) COLLATE Czech_CS_AS,
Period_ID CHAR(10) COLLATE Czech_CS_AS,
LocalPAmount NUMERIC(15, 3)
) ...

3.
SELECT ID, (SELECT ID FROM StoreSubCards WHERE StoreCards.ID = :ID AND StoreSubCards.ID = :ID2) FROM StoreCards

Prepare Error
---------------------------
Syntax error, permission violation, or other nonspecific error.

SELECT ID, (SELECT ID FROM StoreSubCards WHERE StoreCards.ID = '' AND StoreSubCards.ID = '') FROM StoreCards
is OK


==========================================

Variant B - Query.Options.DescribeParams := False;

1.
SELECT ID FROM PaymentsForDocument(:APDocumentType, :APDocument_ID, :AEndDate)
Prepare Error
---------------------------
Project Project1.exe raised exception class EMSError with message 'Statement(s) could not be prepared.
Implicit conversion from data type sql_variant to float is not allowed. Use the CONVERT function to run this query.
Implicit conversion from data type sql_variant to char is not allowed. Use the CONVERT function to run this query.
Implicit conversion from data type sql_variant to char is not allowed. Use the CONVERT function to run this query.'. Process stopped. Use Step or Run to continue.
---------------------------

Thanks for the reply.
Ing. Jan Javurek
Floresps s.r.o.
Praha
Ceska Republika

links to my previous questions about the PREPARE:
http://www.devart.com/forums/viewtopic. ... highlight=
http://www.devart.com/forums/viewtopic. ... highlight=
http://www.devart.com/forums/viewtopic. ... ede05ee76c

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

Post by Dimon » Tue 07 Dec 2010 13:53

This problem is connected with the specificity of the SQL Server work. The point is that when you use functions with parameters in a SQL query, then before preparing this query you should set information about data type of this parameter, because function can be overloaded and use different prarameter types.
In your case you should set the DescribeParams property to False and set data types of paramters before preparing.

jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

How can I test a query that contains parameters whether it i

Post by jan.javurek@floresps.cz » Sun 17 Jul 2011 23:21

I need to test the question whether it is correct without knowing the parameters.

Mssql to not overload the user function.
CREATE FUNCTION test_over(@a int)
RETURNS INTEGER
AS
begin
return 1
end

CREATE FUNCTION test_over(@a varchar(100))
RETURNS INTEGER
AS
begin
return 1
end

There is already an object named 'test_over' in the database.

How can I test a query that contains parameters whether it is valid?

Thank you for your reply.

AndreyZ

Post by AndreyZ » Mon 18 Jul 2011 12:25

If you want to avoid the "There is already an object named 'test_over' in the database." error, you can use the following code:

Code: Select all

MSQuery.SQL.Text := 'IF OBJECT_ID(''test_over'') IS NOT NULL DROP FUNCTION test_over';
MSQuery.Execute;
MSQuery.SQL.Clear;
MSQuery.SQL.Add('CREATE FUNCTION test_over(@a INT)'); // or MSQuery.SQL.Add('CREATE FUNCTION test_over(@a VARCHAR(100))');
MSQuery.SQL.Add('RETURNS INTEGER');
MSQuery.SQL.Add('AS');
MSQuery.SQL.Add('BEGIN');
MSQuery.SQL.Add('RETURN 1');
MSQuery.SQL.Add('END');
MSQuery.Execute;
If you want to check whether you will be using the correct version of the test_over function, you can use the following code:

Code: Select all

MSStoredProc.StoredProcName := 'test_over';
MSStoredProc.PrepareSQL;
if MSStoredProc.ParamByName('a').DataType = ftInteger then
  ShowMessage('Valid')
else
  ShowMessage('Not valid');

jan.javurek@floresps.cz
Posts: 20
Joined: Fri 29 Jan 2010 10:05
Contact:

SQL query verify syntax.

Post by jan.javurek@floresps.cz » Wed 20 Jul 2011 08:24

Hello,
Please read the previous posts do not answer my question.

In your last response before it is written that you can not get the data types of parameters, because the function can be Overloaded.
===============================
Dimon
Devart Team
Joined: 05 Mar 2007
Posts: 2527
PostPosted: Tue 07 Dec 2010 13:53 Post subject: Reply with quote
This problem is connected with the specificity of the SQL Server work. Is that the point When you use functions with parameters in a SQL query, then this query Preparing before you should set information about data type of this parameter, because the function Can Be Overloaded and use prarameter Different types.
In your case you should set the property to DescribeParams False and set the data types of paramters Preparing before.
===============================

The example I have demonstrated that the function of the MSSQL can not override.

Let's go back to my problem.

************************************************** *********************
I need to validate the SQL query that contains parameters.
I need to test whether the query syntax correct.
************************************************** *********************

Unfortunately, with MSSQL or not if your components are the parameters placed in the call to the function or sub in the sql query (described in my first post).

Another example:
Query.Options.DescribeParams: = True;

Update Test
Set a = 0
Where b in
(Select id
From Test
WHERE id =: objid
)
Parameter Information can not Be derived from SQL statements with sub-select queries. Set parameter information before Preparing command.

Thank you for your answer.

AndreyZ

Post by AndreyZ » Thu 21 Jul 2011 09:34

As we wrote earlier, this problem is connected with the specifity of SQL Server work and not with SDAC. You cannot prepare query with parameters without setting their values before preparing.
But if you want to check only the validity of SQL statement syntax, you can use the PARSEONLY SET statement of SQL Server. You can find more information about it here: http://msdn.microsoft.com/en-us/library/ms178629.aspx
For example, you can write the function like this:

Code: Select all

function IsSQLStatementSyntaxValid(con: TMSConnection; SQL: string): boolean;
var
  q: TMSQuery;
begin
  Result := False;
  if (SQL = '') then exit;
  q := nil;
  try
    q := TMSQuery.Create(nil);
    q.Connection := con;
    q.SQL.Text := 'SET PARSEONLY ON';
    q.Execute;
    try
      try
        q.SQL.Text := SQL;
        q.Execute;
        Result := True;
      except
      end;
    finally
      q.SQL.Text := 'SET PARSEONLY OFF';
      q.Execute;
    end;
  finally
    if Assigned(q) then
      FreeAndNil(q);
  end;
end;
, and use it to check your SQL statements like this:

Code: Select all

if IsSQLStatementSyntaxValid(MSConnection, 'Update Test Set a = 0 Where b in (Select id From Test WHERE id=:objid)') then
  ShowMessage('Valid')
else
  ShowMessage('Not valid');

Post Reply