Prepare TMSQuery error
Posted: 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
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