|
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!