Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Why does FUNCTION occasionally return an Exception? |
Wed, Oct 19 2022 6:29 AM | Permanent Link |
Adam Brett Orixa Systems | I have the following SQL FUNCTION
FUNCTION "TypeID" (IN "aValue" VARCHAR COLLATE "ANSI") RETURNS INTEGER BEGIN DECLARE Crsr Cursor FOR Stmt; DECLARE Result INTEGER; SET Result = -1; IF (aValue IS NOT NULL) AND (aValue <> '') THEN PREPARE Stmt FROM ' SELECT ID FROM Types WHERE UPPER(Name) = UPPER(?) '; OPEN Crsr USING aValue; ... more SQL follows. -- Occasionally my applications report an error which says (I paraphrase slightly): "The Function TypeID reported an error at line 13 column 8 ..." Line 13 column 8 is the point at which the Cursor is opened, so the error implies to me that the SELECT Statement is failing. The statement is so simple, I can't understand how this can happen. I am testing for a NULL or empty string value in the SELECT statement, so the Cursor is only opened if there is some text in "aValue" Any ideas of what could be causing the error/exception? |
Wed, Oct 19 2022 9:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
If it happened consistently I'd say ot was your SQL and suggest testing outside the function. Since its intermittent I'd suggest the data. What is aValue and where does it come from? My other thought is that I've never really understood the line numbering that gets reported for errors in SQL. Mainly I don't remember where Tim starts counting, so it might be worth looking at a line or two afterwards. It might be worth showing us the rest of the SQL just in case you have a small typo in there Roy Lambert |
Mon, Nov 7 2022 6:26 AM | Permanent Link |
Adam Brett Orixa Systems | Thanks for the response Roy.
The full SQL of the function is: FUNCTION "TypeID" (IN "aValue" VARCHAR COLLATE "ANSI") RETURNS INTEGER BEGIN DECLARE Crsr Cursor FOR Stmt; DECLARE Result INTEGER; SET Result = -1; IF (aValue IS NOT NULL) AND (aValue <> '') THEN PREPARE Stmt FROM ' SELECT ID FROM Types WHERE UPPER(Name) = UPPER(?) '; OPEN Crsr USING aValue; IF ROWCOUNT(Crsr) > 0 THEN FETCH FIRST FROM Crsr('ID') INTO Result; END IF; CLOSE Crsr; END IF; IF Result is NULL THEN SET Result = -1; END IF; RETURN Result; END I have scratched my head about this one for a while, and can't see an issue. The definition of the Types table is straightforward: CREATE TABLE "Types" ( "ID" INTEGER DEFAULT UID() NOT NULL, "LinkTable" VARCHAR(40) COLLATE "ANSI", "LinkField" VARCHAR(50) COLLATE "ANSI", "Color" INTEGER DESCRIPTION '[Properties] DisplayControl=ColorCombo', "Name" VARCHAR(200) COLLATE "ANSI", "Description" CLOB COLLATE "ANSI", "DateCreated" TIMESTAMP DEFAULT Current_Timestamp, "Current" BOOLEAN DEFAULT true ) It is really just a holder for "lookup" values such as types of product, locations in a warehouse etc. I am wondering whether it may actually be raising the exception due to the places it is used. When used in a SELECT such as SELECT P.Name as Product FROM Products WHERE ProductsTypeID = TypeID('Retail') I never see errors. However it is also used in table-definitions: CREATE TABLE "Products" ( "ID" INTEGER DEFAULT UID() NOT NULL, "Name" VARCHAR(100), "ProductsTypeID" INTEGER DEFAULT TypeID('Retail') ) I am wondering whether this is completely safe in all situations with EDB, or whether calling out to a function in an INSERT might sometimes mess things up. I feel it should work fine. |
Mon, Nov 7 2022 7:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
There's something in the back of my mind saying user defined functions are not allowed in.... but I can't remember where. I'm guessing UID() is also a user defined function and that doesn't give you any grief or does it? Whilst I can't see an error try this slightly different version FUNCTION "TypeID" (IN "aValue" VARCHAR COLLATE "ANSI") RETURNS INTEGER BEGIN DECLARE Crsr Cursor FOR Stmt; DECLARE Result INTEGER DEFAULT -1; IF COALESCE(aValue,'') <> '' THEN PREPARE Stmt FROM ' SELECT ID FROM Types WHERE UPPER(Name) = UPPER(QUOTEDSTR(aValue)) '; OPEN Crsr; IF ROWCOUNT(Crsr) > 0 THEN FETCH FIRST FROM Crsr('ID') INTO Result; END IF; CLOSE Crsr; END IF; RETURN Result; END wouldn't be the first time just faffing about has cured a problem Roy Lambert |
This web page was last updated on Thursday, March 28, 2024 at 06:05 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |