[Info-ingres] ESQLC: What am I doing wrong?

Hi Everyone,

I've been banging my head on this for a while and I can't see what I've 
done wrong, so could someone please tell me what I've done wrong. It 
should be so very,very simple!

I'm trying to check the number of times a record exists in a table.
ie select count(*) from table where record='xxxx'
The 'xxxx' part is passed by parameter and built into a nice host 
variable.

I have a variable: int counter declared within an EXEC SQL 
BEGIN/END DECLARE SECTION block.

Now in this case the result type of the select is fixed - clearly a non 
nullable integer. 

Hence I would expect an execute immediate would work:
EXEC SQL EXECUTE IMMEDIATE :stmt_buffer into :counter;

Instead it failed with:
Error Code 40204:
Reason: E_LQ000A Conversion failure from column 1 into program 
variable.

Somewhat astounded I prepared the statement buffer and scanned the 
contents. It's adamant the return type is date!

So I forced the issue...
sqlda->sqld=1;
sqlda->sqlvar[0].sqltype = IISQ_INT_TYPE;
sqlda->sqlvar[0].sqlind=(short *) 0;
sqlda->sqlvar[0].sqllen=sizeof(int);
sqlda->sqlvar[0].sqldata=(char *) &counter;

EXEC SQL EXECUTE IMMEDIATE :stmt_buffer using descriptor 
sqlda;

And it still gives me the E_LQ00A error.

Martin Bowes

Hi Karl ,


Server is IIR3. The appllication is running on the host.

What happens if you do select int4(count(*))

Same


The only place I'm looking at a date is where I do an:
 EXEC SQL select date('now') into :current_time;

This seems to work perfectly.

The program is listening for events while connected to one database 
and I use the select date('now') as the SQL to be interrupted by the 
event.

What is supposed to happen is that the program checks its original 
database for some base data (the select count(*)) and then on the 
basis of that makes it mind up to switch connections to another 
database to do some work before switching back to continue listening.

I don't see how that can effect things.

Marty

Hi Karl,

When I replace the EXEC SQL select date('now')....
with EXEC SQL select count(*) into :j from iidbconstants where 1=0;

And then...
strcpy(stmt_buffer, "select int4(count(*)) from users where 
login_name='");
strcat(stmt_buffer, user_name);
strcat(stmt_buffer,"'");

EXEC SQL PREPARE checker into sqlda from :stmt_buffer;

The prepare into sqlda now describes the sqlvar[0] as an integer 
(type=30) and not a date (type=3).

But the final execute immediate of the statement returns the wrong 
data.
EXEC SQL BEGIN DECLARE...
int counter;
EXEC SQL END DECLARE...

sqlda->sqlvar[0].sqllen=sizeof(int);
sqlda->sqlvar[0].sqldata=(char *) &counter;

EXEC SQL EXECUTE IMMEDIATE :stmt_buffer using descriptor 
sqlda;

yields the clearly {*filter*}value of -1073751184

Marty

Hi  Rob

Exactly right! Doing the EXECUTE IMMEDIATE is what *should* be 
done just as you describe. Which is what I originally did, only to have 
the application crash out telling me that it couldn't coerce variable 1 into 
the correct format. I was surprised.

The reason I'm preparing the statement is to try and get details about 
what return variable format the program thinks its got. This is how I 
found out that it thinks its retrieving a date and not an integer.

Marty

Hi Karl et al,

Why do interesting things keep happening to me?

I suspected that I must have got a pointer confused somehow, so I 
seeded the original counter with the value -99 and then printed out the 
value of sqlda->sqlvar[0].sqldata both before and after the EXECUTE. 
Both gave me the value -99. So I'm pretty sure I haven't done anything 
weird with pointers.

I ran on IngresIIR3 and Ingres2006 with the same result.

I then rewrote the PREPARE into sqlda from :stmt_buffer
to...
EXEC SQL PREPARE astatement from :stmt_buffer;
if (sqlca.sqlcode!=0) {
    EXEC SQL INQUIRE_SQL(:errorno=dbmserror, :errortext=errortext);
    printf("addUser(): Failed to prepare select for %s\n", user_name);
    printf("Error Code %d:\nReason: %s\n", errorno, errortext);
    EXEC SQL ROLLBACK;
    return (0);
    };
printf("Survived prepare\n");
EXEC SQL DESCRIBE astatement into sqlda;
if (sqlca.sqlcode!=0) {
    EXEC SQL INQUIRE_SQL(:errorno=dbmserror, :errortext=errortext);
    printf("addUser(): Failed to describe select for %s\n", user_name);
    printf("Error Code %d:\nReason: %s\n", errorno, errortext);
    EXEC SQL ROLLBACK;
    return (0);
    };
printf("Survived describe\n");

I get (wait for this!)
Survived prepare
Exiting session because of communications failure.
E_SC0206 An internal error prevents further processing of this query. 
    Associated error messages which provide more detailed information 
about
    the problem can be found in the error log, II_CONFIG:errlog.log

The errlog shows:
E_AD2004_BAD_DTID	ADF routine was passed an invalid datatype id.
 E_SC021C_SCS_INPUT_ERROR	SCS_INPUT internal error.

So the initial Prepare into works, but the equivalent two step statement 
fails.

Marty

Have you been striking irreligious poses in front of pious Christian 
artworks?

Roy 

Hi Roy,


	I got confused. I just turned the other cheeks.

But on a serious note....

I got desperate enough to rebuild the program from scratch - adding in 
processes along the way...I'm working on a stations of the Cross{*filter*}
but I'd better keep that to myself...

Its when I add the bit where the program receives and processes an 
event that the problem starts. Upto then the code works perfectly. But 
as soon as the eventhandler is called I get the:
Error Code 40204:
Reason: E_LQ000A Conversion failure from column 1 into program 
variable.

The main program is simply looping doing a select date('now') every ten 
seconds. If an event occurs it calls an eventhandler routine which 
determines what subroutine to call on the basis of the event_name. It 
calles the correct subroutine, it passes the eventtext as an argument.

If I call the routine directly - it works perfectly.

strcpy(stmt_buffer, "select count(*) from iitables where table_owner='");
strcat(stmt_buffer, user_name);
strcat(stmt_buffer,"'");

EXEC SQL EXECUTE IMMEDIATE :stmt_buffer into :counter;