The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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)
    DECLARE id VARCHAR(65); 
    DECLARE highest INTEGER;
    DECLARE prefix VARCHAR(65);
    DECLARE byteOffset INTEGER;

    SET highest = 0;

    SET byteOffset = (2 * 4) + 1;

       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)));
       SET prefix = INTTOHEX (0x80000000 | recordId); 
    END IF;

    SET prefix = UPPER(REPLACE(LTRIM(REPLACE(prefix,'0',' ')),' ','0'));    
    SET id = STRING (prefix, '-', createdTimeStamp);
    RETURN id;

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

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

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 30 Mar, 09:57

question was seen: 166 times

last updated: 31 Mar, 10:11