I am trying to use Embedded SQL in C to fetch rows from an Ingres database and return the results into the SQLDA structure. I will be selecting ints, longs and strings. So far my program fetches the strings fine, but I have issues with the ints and longs.
EXEC SQL DECLARE select_string STATEMENT;
EXEC SQL BEGIN DECLARE SECTION;
char sql[1000];
int errorno; /* ingres error number */
char errortext[257];
EXEC SQL END DECLARE SECTION;
strcpy(sql, "SELECT 18, 100, 3774, 2147483647, 'dfsdfsf'");
printf("%s\n",sql);
EXEC SQL DECLARE csr0 CURSOR FOR select_string;
EXEC SQL PREPARE select_string FROM :sql;
EXEC SQL OPEN csr0 FOR READONLY;
//sqlda
IISQLDA *sqlda;
sqlda = (IISQLDA *)calloc(1, IISQDA_HEAD_SIZE + ((longs+ints+strings) * IISQDA_VAR_SIZE));
sqlda->sqln = (5);
EXEC SQL DESCRIBE select_string INTO :sqlda;
int i,j;
for( i = 0; i < sqlda->sqld; i++)
{
printf("%d ",sqlda->sqlvar[i].sqllen);
sqlda->sqlvar[i].sqldata = malloc(sqlda->sqlvar[i].sqllen);
sqlda->sqlvar[i].sqlind = malloc(sizeof(short));
}
printf("\n");
EXEC SQL FETCH csr0 USING DESCRIPTOR sqlda;
for( i=0; i<sqlda->sqld; i++)
{
printf("len:%d | ",sqlda->sqlvar[i].sqllen);
for(j=0;j<sqlda->sqlvar[i].sqllen;j++){
printf("%d ",sqlda->sqlvar[i].sqldata[j]);
}
printf(" | ");
for(j=0;j<sqlda->sqlvar[i].sqllen;j++){
printf("%c ",sqlda->sqlvar[i].sqldata[j]);
}
printf(" | %ld",*sqlda->sqlvar[i].sqldata);
printf("\n");
}
for( i = 0; i < sqlda->sqld; i++)
{
free(sqlda->sqlvar[i].sqldata);
free(sqlda->sqlvar[i].sqlind);
}
free(sqlda);
The output is as follows.
SELECT 18, 100, 3774, 2147483647, 'dfsdfsf'
2 2 2 4 7
len:2 | 18 0 | | 18
len:2 | 100 0 | d | 100
len:2 | -66 14 | ▒ | 4294967230
len:4 | -1 -1 -1 127 | ▒ ▒ ▒ | 4294967295
len:7 | 7 0 100 102 115 100 102 | d f s d f | 7
Observations: small ints (1 byte) work as expected. The information is encoded into the char array (sqlda->sqlvar[]->sqldata) in the first position. Once you try and go above 1 byte it starts to get weird. The string is cut off by 2 bytes, however if you read two more bytes into the char array the data is there, so I'm not worried about that.
What am I doing wrong / how do I properly fetch into a sqlda so that ints/longs are saved in a manner that I can retrieve them later?
When displaying one of the int2 values retrieved from the database, try something like: *(short *)sqlda->sqlvar[i].sqldata
The string value 'dfsdfsf' is being retrieved as a varchar rather than a char (see sqlda->sqlvar[i].sqltype), the first 2 bytes of a varchar contain the string length.