| Author |
Message
|
| rkford11 |
 Posted: Tue Jan 16, 2007 1:08 pm Post subject: Database error |
 |
|
Partisan
Joined: 06 Jun 2004
Posts: 316
|
When i am executing the following ESQL
CREATE COMPUTE MODULE Test_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
CALL CopyEntireMessage();
RETURN TRUE;
END;
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER;
DECLARE J INTEGER;
SET I = 1;
SET J = CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;
CREATE PROCEDURE CopyEntireMessage() BEGIN
DECLARE A INTEGER;
SET A = CARDINALITY(SELECT X.* FROM Database.Test."Test.Table1" AS X);
SET OutputRoot = InputRoot;
END;
END MODULE;
database is throwing me the following error
Database error: SQL State ''HY000''; Native Error Code '933'; Error Text ''[DataDirect][ODBC Oracle driver][Oracle]ORA-00933: SQL command not properly ended ''.
The error has the following diagnostic information: SQL State ''HY000'' SQL Native Error Code '933' SQL Error Text ''[DataDirect][ODBC Oracle driver][Oracle]ORA-00933: SQL command not properly ended ''.
We have our broker v6.0 running on windows 2003 and Oracle 10g is the application database. Please guide me through this. |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Tue Jan 16, 2007 1:21 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I would use a passthru statement and have the database count the rows instead of pulling the entire table into broker memory, and then using cardinality to count the rows in the result set.
Also, I've been seeing this a lot - it's a very poor code practice to add your own stuff to CopyEntireMessage or CopyMessageHeaders. The person who has to maintain your code will never know to look there.
Add your own code directly to Main or create your own procedures.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jan 16, 2007 1:24 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Not only that, but this code also calls bothCopyMessageHeaders and CopyEntireMessage, which is always a bad idea.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| rkford11 |
| Posted: Tue Jan 16, 2007 3:30 pm Post subject: |
|
|
Partisan
Joined: 06 Jun 2004
Posts: 316
|
| mgk wrote: |
| Not only that, but this code also calls bothCopyMessageHeaders and CopyEntireMessage, which is always a bad idea. |
Thanks Jeff and mgk. Passthru statement worked for me and i will make sure that i write the code in main or create my own procedure. |
|
| Back to top |
|
|
| vk |
| Posted: Tue Jan 16, 2007 8:21 pm Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
We faced the same issue. While accessing Oracle DB, do not use AS keyword for specifying database alias. The query should work if executed as follows -
SELECT COUNT(*) FROM Database.<SchemaName>.<TableName>
OR
SELECT K.* FROM Database.<SchemaName>.<TableName> K (No AS)
Regards,
VK. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Jan 17, 2007 1:34 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| do not use AS keyword for specifying database alias |
This is not quite correct. You must use the AS when using an ESQL SELECT (but ESQL will remove the AS when sending the final ODBC string to Oracle). However, you must not use it in a PASSTHRU when talking to Oracle because as you say Oracle does not like it.
regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
