Hello, I want to write a query used on several versions of a database. The later versions have defined a stored procedure/function that I can use, (but it is missing in the earlier versions). If it does not exist, is it possible to copy/paste the code into the query and use it directly? Here is the abbreviated stored function: ALTER FUNCTION "AdminGroup"."MakeInternalId" (IN recordId INTEGER, IN createdTimeStamp INTEGER) RETURNS VARCHAR(65) DETERMINISTIC BEGIN DECLARE id VARCHAR(65); DECLARE highest INTEGER; DECLARE prefix VARCHAR(65); DECLARE byteOffset INTEGER; SET highest = 0; SET byteOffset = (2 * 4) + 1; SELECT CAST(CAST(STRING( BYTE_SUBSTR(CAST(blob AS VARCHAR), byteOffset + 3, 1), BYTE_SUBSTR(CAST(blob AS VARCHAR), byteOffset + 2, 1), BYTE_SUBSTR(CAST(blob AS VARCHAR), byteOffset + 1, 1), BYTE_SUBSTR(CAST(blob AS VARCHAR), byteOffset, 1)) AS BINARY) AS INTEGER ) INTO highest FROM metadata WHERE external_key = 'highest'; SET highest = ISNULL (highest, 0); IF (recordId <= highest) THEN SET prefix = INTTOHEX ((recordId * power(2, 16))); ELSE SET prefix = INTTOHEX (0x80000000 | recordId); END IF; SET prefix = UPPER(REPLACE(LTRIM(REPLACE(prefix,'0',' ')),' ','0')); SET id = STRING (prefix, '-', createdTimeStamp); RETURN id; END I tried copy/pasting the above code into Sybase Central - Interactive SQL window, just above my query and got a syntax error where my query starts. Thanks, David |
Let me guess: Syntax error near (the first keyword of your next statement). Did you remember to add a semicolon after the END? That's a Watcom-SQL dialect procedure. When working in Watcom-SQL, semi-colons are required between each statement. Thank you nmelion - after adding the semicolons (and defining the function at the top as CREATE TEMPORARY), my query worked great, calling the temporary function and returning data. :-)
(31 Mar '17, 10:10)
david_ching
|
No, AFAIK a query cannot contain a compound statement or control statements, it can only directly call stored functions and select from stored procedures. Some alternatives:
If your actual issue has to do with a builtin function that only exists in newer version of the database software, you could write a wrapper function that
(I'm not sure whether "older version of the database" refers to different versions of SQL Anywhere or different versions of your database schema...) Thank you Volker - yes, I did use CREATE TEMPORARY. The SQL Anywhere version is sufficient, it was my database scehema that had changed to include the stored function in later versions.
(31 Mar '17, 10:09)
david_ching
|
Does it work if you simply change the word "ALTER" into "CREATE"? Depending upon the versions involved, an if "CREATE OR REPLACE PROCEDURE ... " syntax is supported you could also resort to that.
Or is your issue some else like ... Maybe pasting a query with the BYTE_SUBSTR( ) into a pre-8 version of the software?
Thank you Nick, changing ALTER to CREATE TEMPORARY was exactly what I needed!