topic Re: Using SQL cursors in C program in Operating System - Linux

Using SQL cursors in C program

Steve Hosto — Fri, 26 Aug 2005 10:15:08 GMT

Greetings everyone,

I'm attempting to write some C applications to do basic queries of our ALLBASE/SQL database. I have two programs that make use of cursors to obtain and display multiple row results. On program works as expected while the other is unable to obtain a multiple row result set. The main difference between the two are their SQL cursor declarations.

Here's the "broken" one:
// SQL Cursor declarations
EXEC SQL DECLARE comp_ids_crs CURSOR FOR
SELECT COMPONENT_ID FROM REL_COMP_DETAIL
WHERE COMPONENT_NAME = :CompName
AND COMP_VERSION = :CompVer;

and the one that works:
// SQL Cursor declare
EXEC SQL DECLARE dest_ids_crs CURSOR FOR
SELECT DESTINATION_ID FROM COMP_DEST
WHERE COMPONENT_ID = :ComponentID;

Attached is the broken C source file. I would attach the working one, but can only attach one file to a post by the looks of things.

Any help would be greatly appreciated.

Re: Using SQL cursors in C program

Antoniov. — Fri, 26 Aug 2005 11:42:45 GMT

Steve,
it's not easy without database design help you. Statement seems to be right. May be a stupid question, but does exist some record with CompName and CompVer passed? Did you try direct sql command with console?

Antonio Vigliotti

Re: Using SQL cursors in C program

Steve Hosto — Fri, 26 Aug 2005 11:48:34 GMT

Here's the table structure as defined in the create table command:
CREATE TABLE Rel_Comp_Detail (
Component_ID INTEGER NOT NULL,
Component_Name CHAR(20),
Comp_Version CHAR(5),

When the broken SQL select statement is run via isql a multiple row result set is returned.

Re: Using SQL cursors in C program

Hein van den Heuvel — Fri, 26 Aug 2005 12:39:23 GMT

I suspect the SQL is just fine, but the string manipulation is incorrect.
The query for 'ComponentID' is relatively easy because it is a simple integer.

But the Name/Ver query can have trouble with fill characters and null terminators.

I suggest you print those variables and their length just before the query and print them with some delimiter to make it clear where they end, much like how you print the result, but now before the query:

printf("CompName = '%s' %d\n", CompName, strlen(CompName));

fwiw,
Hein.

Re: Using SQL cursors in C program

Steve Hosto — Fri, 26 Aug 2005 13:04:38 GMT

Thanks a bunch.

I thought that might be the problem too, but our C "experts" disagreed with me.

I ended up hard coding in the lenth of the strncat() call to populate CompName. I had previously been using sizeof() instead of strlen() to determine the length of the strncat() call.

$ ./getCompIDs.r -v GR423A30
RawInput = 'GR423A30' and is 8 bytes long
CompName = 'GR423' and is 5 bytes long
CompVer = 'A30' and is 3 bytes long
GR423A30 - 26888
GR423A30 - 26889
GR423A30 - 26897

Updated C source file attached.