Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Why does FUNCTION occasionally return an Exception?
Wed, Oct 19 2022 6:29 AMPermanent 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 Smiley

Roy Lambert
Mon, Nov 7 2022 6:26 AMPermanent 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 Smiley

Roy Lambert
Image