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

asked 30 Mar, 09:57

david_ching's gravatar image

david_ching
4648
accept rate: 0%

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?

(30 Mar, 10:32) Nick Elson S...

Thank you Nick, changing ALTER to CREATE TEMPORARY was exactly what I needed!

(31 Mar, 10:08) david_ching

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.

permanent link

answered 30 Mar, 11:02

mmellon's gravatar image

mmellon
1462410
accept rate: 25%

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, 10:10) david_ching

Is it possible to copy/paste the code into the query and use it directly?

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:

  • Use expressions like the CASE or IF expression (which are different from the CASE or IF statements!) to define some conditional, expresssion-based logic which is allowed in queries
  • Use a temporary function
  • Check whether the (permanent) function exists, and create it if not (see your other question...)
  • Use CREATE OR REPLACE function as a handy alternative.

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

  • either calls the builtin function (if that is available)
  • or calls a homebrown one.

(I'm not sure whether "older version of the database" refers to different versions of SQL Anywhere or different versions of your database schema...)

permanent link

answered 30 Mar, 10:32

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

edited 30 Mar, 11:19

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, 10:09) david_ching
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×28

question asked: 30 Mar, 09:57

question was seen: 256 times

last updated: 31 Mar, 10:11