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

DATABASE

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

Postby martin.bowes » Fri, 08 Dec 2006 23:53:50 GMT

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

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

Postby martin.bowes » Sat, 09 Dec 2006 00:47:24 GMT

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




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

Postby martin.bowes » Sat, 09 Dec 2006 01:06:06 GMT

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


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

Postby martin.bowes » Sat, 09 Dec 2006 17:30:09 GMT

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


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

Postby martin.bowes » Sat, 09 Dec 2006 17:49:53 GMT

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




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

Postby Roy Hann » Sat, 09 Dec 2006 18:03:31 GMT





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

Roy 



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

Postby martin.bowes » Sat, 09 Dec 2006 19:43:51 GMT

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;



Similar Threads:

1.Comparing two dates -- What am I doing wrong?

I have a date field "Expiration Date" and a computed value "Current Date". 
Expiration Date is a date field with a value entered by the user. Current 
Date is derived by Get (CurrentDate).

What I want to do is to produce a text value, either "Active" or "Expired" 
depending on whether the value in Current Date is or is not equal to or 
subsequent to the value in Expiration Date.

But strange things are happening in the containing case statement which has 
two conditions, one specified if the result should be Active and one if the 
result should be Expired. I get one of the two wanted text values, but they 
don't match the expectations of the deriving expression, a case statement.


Here is the case statement....


Case (

Expiration Date > Current Date;
"Active"';

Expiration Date < Current Date;
"Expired";

)


Substitute the FileMaker less/equal symbol for the less-than in the above.

So where is it that I am being stupid?


-- James L. Ryan -- TaliesinSoft

2.I am doing somthing wrong ......

I have inserted a field on 3 layouts (the same field).  It displays on 2
layouts no problem, but it will not show on the third layout, why.   I have
tried everything I know to fix it with no luck.

I know it some stupid thing I have over looked, what is it ?

FYI: Using FileMaker Pro 6.2 - PC Windows only

Thanks
Charlie

3.[Info-Ingres] floor() and ceil()...wrong, wrong, wrong!

4.[Info-ingres] OME: User defined function - What am I doing wrong?

Hi Everyone,

I'm experimenting with OME to add User Defined Functions - not 
datatypes (they are way to freaky).

I've managed to get the system to accept that my function exists. The 
function simply takes an integer as input and returns a psuedo random 
sequence value based upon this input.

However, rather than return the expected value, it seems to work the 
first time through, but then wont alter this return value for subsequent 
executions. Before I have to go and add heaps of tracing to it could 
someone try to spot the (probably screamingly obvious problem).

II_STATUS rand_seq_gen (
    II_SCB          *scb,
    II_DATA_VALUE   *p1,
    II_DATA_VALUE   *rdv
) {
/*
**  returns the next integer in the random sequence. This is done on
**  the basis of a calculation from Roy Hann. All values in the range
**  of an int4 will be returned before the sequence repeats.
*/
int n;
II_STATUS ret_value=II_OK;

/* Fill out the basics for returning an integer value */
rdv->db_length   = sizeof(int);
rdv->db_datatype = II_INTEGER;
rdv->db_prec     = 0;

/*
** Now check the data value passed in and if possible return the next 
value in
** the sequence.
*/

if (p1) {
    n=(int) p1->db_data;
    if (n < 0) {
        ret_value=II_ERROR;
        us_error(scb,
            0x200000, /* Error Code */
            "rand_seq_gen: Do not call with a negative value"
            );
        }
    else {
        n=n>>1 | ((n^n>>3) & 1) << 30; /* I wonder if Roy's bullshitting me
                                       ** on this one!
                                       */
        *(rdv->db_data)=n; /*Perhaps I need to copy the bytes???*/
        };
    }
else { /* I don't think this is possible, but what the hell */
    ret_value=II_ERROR;
    us_error(scb,
        0x200001, /* Error Code */
        "rand_seq_gen: Null value passed!"
        );
    };
return (ret_value);
} /*rand_seq_gen*/

Martin Bowes

Random Duckman Quote #96:
Cornfed: And to think, Entertainment Weekly panned us...

5.[Info-ingres] RES: [Info-ingres] Wrong results returned from outer join

6. am i asking in the wrong place?

7. [Info-ingres] ESQLC 'ld' problems

8. [Info-ingres] ESQLC asynchronous or synchronous ?



Return to DATABASE

 

Who is online

Users browsing this forum: No registered users and 83 guest