SUBSCRIPT of a CHAR column gives error -201
The poster used Informix subscript syntax with placeholders (e.g. SELECT TABNAME[?,4]) in a prepared ESQL/C statement; it worked for a TEXT column but returned error -201 on a CHAR column. Art Kagel explained that replaceable parameters/host variables can't be used inside subscript expressions and recommended SUBSTR(colname, ?, ?) instead; John Miller suggested the same function. The poster confirmed SUBSTR works with host variables, but noted it doesn't work on TEXT, so a single approach covering both CHAR and TEXT was still unresolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We are using Informix subscript, COL[:1,:2], in ESQL/C program to get the substring of data. This works fine with TEXT datatype but it gives error -201 (Syntax error) when used for a CHAR column, It seems bind variables (COL[:1,:2]) are causing the error as it might not be supported for CHAR columns. I didn't find any IBM documention stating this limitation. Any idea if the error is expected one or I'm missiing anything..? Thanks,
It's the colons that are giving you the error. Why it works for TEXT, I don't know. The expression should be: ... COLNAME[1,2] ... --- or -- $int var1, var2; var1 = 1; var2 = 2; ... COLNAME[:var1, :var2] ... Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 23, 2009 at 1:57 AM, PRADEEP KUMAR <pkyadav1@hotmail.com> wrote: > We are using Informix subscript, COL[:1,:2], in ESQL/C program to get the > substring of data. This works fine with TEXT datatype but it gives error > -201 > (Syntax error) when used for a CHAR column, > > It seems bind variables (COL[:1,:2]) are causing the error as it might not > be > supported for CHAR columns. > > I didn't find any IBM documention stating this limitation. > > Any idea if the error is expected one or I'm missiing anything..? > > Thanks, > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bd590b81c7504790adead
Hi Art, Thanks for your response. I'm not using the colon but using ? to pass the parameter, sorry for the confusion . Below is the sample code which works fine when I use TEXT coulmn and gives error with CHAR: /* *************************************************************************** * **************************************************************************** */ #include <stdio.h> #include <stdlib.h> #include <conio.h> #include <windows.h> #include <sqltypes.h> #include <sys/timeb.h> #include <time.h> void InfConnect(); void InfDisConnect(); int InfBulkInsert(int nRows); int InfInsertSingle(int nRows); EXEC SQL define FNAME_LEN 15; EXEC SQL define LNAME_LEN 15; EXEC SQL BEGIN DECLARE SECTION; char dbname[100]; char user[100]; char password[100]; EXEC SQL END DECLARE SECTION; /* *************************************************************************** */ int main(int argc, char *argv[]) { strcpy(dbname, argv[1]); strcpy(user, argv[2]); strcpy(password, argv[3]); InfConnect(); InfInsertSingle(1); InfDisConnect(); return 0; } /* *************************************************************************** */ void InfDisConnect() { EXEC SQL disconnect current; printf("\\ select_bind demo program complete.\\ \\ "); return; } /* *************************************************************************** */ void InfConnect() { printf("\\ select_bind demo ESQL program running.\\ \\ "); EXEC SQL WHENEVER ERROR STOP; EXEC SQL connect to :dbname user :user using :password; return; } /* *************************************************************************** */ int InfInsertSingle(int nRows) { EXEC SQL BEGIN DECLARE SECTION; char stmt[1024] = "SELECT TABNAME[?,4] from 'informix'.systables where tabid=?"; int id = 0, i=0; int sqld = 1; char StatementID[11]="TESTStmtID"; char CursorID[11]="CursorID1"; struct sqlda *da_ptr; struct sqlda ParmDA; struct sqlda *pParmDA; struct sqlvar_struct *col_ptr; struct sqlda FetchDA; struct sqlda *lpFetchDA; EXEC SQL END DECLARE SECTION; EXEC SQL BEGIN; EXEC SQL PREPARE :StatementID from :stmt; printf("EXEC SQL PREPARE :StatementID from :stmt\\ "); EXEC SQL DESCRIBE :StatementID into da_ptr; printf("EXEC SQL DESCRIBE :StatementID into da_ptr\\ "); ParmDA.sqlvar = (struct sqlvar_struct *) malloc( sqld * sizeof(struct sqlda)); memcpy(&FetchDA, da_ptr, sizeof(struct sqlda)); FetchDA.sqlvar = (struct sqlvar_struct*) malloc(da_ptr->sqld * sizeof(struct sqlvar_struct)); memcpy(FetchDA.sqlvar, da_ptr->sqlvar, sizeof(struct sqlvar_struct) * da_ptr->sqld); col_ptr = FetchDA.sqlvar; col_ptr->sqltype = CCHARTYPE; col_ptr->sqllen = 255; col_ptr->sqldata = (char *) malloc(col_ptr->sqllen * 1); lpFetchDA = &FetchDA; EXEC SQL DECLARE :CursorID CURSOR FOR :StatementID; printf("EXEC SQL DECLARE :CursorID CURSOR FOR :StatementID; %d columns\\ ", da_ptr->sqld); for (id = 2000; id < 2002 + nRows; id++) { ParmDA.sqld = sqld; for( i = 0; i<sqld; i++) { ParmDA.sqlvar[i].sqlname = NULL; ParmDA.sqlvar[i].sqlformat = NULL; ParmDA.sqlvar[i].sqlitype = 0; ParmDA.sqlvar[i].sqlilen = 0; ParmDA.sqlvar[i].sqlidata = NULL; ParmDA.sqlvar[i].sqlind = NULL; ParmDA.sqlvar[i].sqltype = CINTTYPE; ParmDA.sqlvar[i].sqldata = (char*)&id; pParmDA = &ParmDA; printf("EXEC SQL OPEN :CursorID USING DESCRIPTOR pParmDA\\ "); EXEC SQL OPEN :CursorID USING DESCRIPTOR pParmDA; EXEC SQL FETCH :CursorID USING DESCRIPTOR lpFetchDA; printf("EXEC SQL FETCH :CursorID USING DESCRIPTOR lpFetchDA\\ "); printf("Data : %s\\ ", lpFetchDA->sqlvar[i].sqldata); // EXEC SQL EXECUTE :StatementID USING DESCRIPTOR pParmDA; } } if (strncmp(SQLSTATE, "02", 2) != 0) printf("SQLSTATE after fetch is %s\\ ", SQLSTATE); EXEC SQL commit; return 0; } /* *************************************************************************** */ Appreciate any help .
I have never tried it with host variables in the array position, but I have used the substr( source string, start position , [ length] ) John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 11/23/2009 07:51:16 AM: > [image removed] > > Re: SUBSCRIPT of a CHAR column gives error -201 [18182] > > PRADEEP KUMAR > > to: > > ids > > 11/23/2009 07:53 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi Art, > Thanks for your response. > > I'm not using the colon but using ? to pass the parameter, sorry for the > confusion . > > Below is the sample code which works fine when I use TEXT coulmn and gives > error with CHAR: > > /* > *************************************************************************** > * > **************************************************************************** > */ > #include <stdio.h> > #include <stdlib.h> > #include <conio.h> > #include <windows.h> > #include <sqltypes.h> > #include <sys/timeb.h> > #include <time.h> > void InfConnect(); > void InfDisConnect(); > int InfBulkInsert(int nRows); > int InfInsertSingle(int nRows); > EXEC SQL define FNAME_LEN 15; > EXEC SQL define LNAME_LEN 15; > EXEC SQL BEGIN DECLARE SECTION; > > char dbname[100]; > > char user[100]; > > char password[100]; > EXEC SQL END DECLARE SECTION; > /* > *************************************************************************** > */ > int main(int argc, char *argv[]) > { > strcpy(dbname, argv[1]); > strcpy(user, argv[2]); > strcpy(password, argv[3]); > InfConnect(); > InfInsertSingle(1); > InfDisConnect(); > return 0; > } > /* > *************************************************************************** > */ > void InfDisConnect() > { > > EXEC SQL disconnect current; > > printf("\\ select_bind demo program complete.\\ \\ "); > return; > } > /* > *************************************************************************** > */ > void InfConnect() > { > > printf("\\ select_bind demo ESQL program running.\\ \\ "); > > EXEC SQL WHENEVER ERROR STOP; > > EXEC SQL connect to :dbname user :user using :password; > > return; > } > /* > *************************************************************************** > */ > int InfInsertSingle(int nRows) > { > EXEC SQL BEGIN DECLARE SECTION; > > char stmt[1024] = "SELECT TABNAME[?,4] from 'informix'.systables where > tabid=?"; > > int id = 0, i=0; > > int sqld = 1; > > char StatementID[11]="TESTStmtID"; > > char CursorID[11]="CursorID1"; > > struct sqlda *da_ptr; > > struct sqlda ParmDA; > > struct sqlda *pParmDA; > > struct sqlvar_struct *col_ptr; > > struct sqlda FetchDA; > > struct sqlda *lpFetchDA; > EXEC SQL END DECLARE SECTION; > EXEC SQL BEGIN; > > EXEC SQL PREPARE :StatementID from :stmt; > > printf("EXEC SQL PREPARE :StatementID from :stmt\\ "); > > EXEC SQL DESCRIBE :StatementID into da_ptr; > > printf("EXEC SQL DESCRIBE :StatementID into da_ptr\\ "); > > ParmDA.sqlvar = (struct sqlvar_struct *) malloc( sqld * sizeof > (struct sqlda)); > > memcpy(&FetchDA, da_ptr, sizeof(struct sqlda)); > > FetchDA.sqlvar = (struct sqlvar_struct*) malloc(da_ptr->sqld * sizeof (struct > sqlvar_struct)); > > memcpy(FetchDA.sqlvar, da_ptr->sqlvar, sizeof(struct sqlvar_struct) * > da_ptr->sqld); > > col_ptr = FetchDA.sqlvar; > > col_ptr->sqltype = CCHARTYPE; > > col_ptr->sqllen = 255; > > col_ptr->sqldata = (char *) malloc(col_ptr->sqllen * 1); > > lpFetchDA = &FetchDA; > > EXEC SQL DECLARE :CursorID CURSOR FOR :StatementID; > > printf("EXEC SQL DECLARE :CursorID CURSOR FOR :StatementID; %d columns \\ ", > da_ptr->sqld); > > for (id = 2000; id < 2002 + nRows; id++) > > { > > ParmDA.sqld = sqld; > > for( i = 0; i<sqld; i++) > > { > > ParmDA.sqlvar[i].sqlname = NULL; > > ParmDA.sqlvar[i].sqlformat = NULL; > > ParmDA.sqlvar[i].sqlitype = 0; > > ParmDA.sqlvar[i].sqlilen = 0; > > ParmDA.sqlvar[i].sqlidata = NULL; > > ParmDA.sqlvar[i].sqlind = NULL; > > ParmDA.sqlvar[i].sqltype = CINTTYPE; > > ParmDA.sqlvar[i].sqldata = (char*)&id; > > pParmDA = &ParmDA; > > printf("EXEC SQL OPEN :CursorID USING DESCRIPTOR pParmDA\\ "); > > EXEC SQL OPEN :CursorID USING DESCRIPTOR pParmDA; > > EXEC SQL FETCH :CursorID USING DESCRIPTOR lpFetchDA; > > printf("EXEC SQL FETCH :CursorID USING DESCRIPTOR lpFetchDA\\ "); > > printf("Data : %s\\ ", lpFetchDA->sqlvar[i].sqldata); > > // EXEC SQL EXECUTE :StatementID USING DESCRIPTOR pParmDA; > > } > > } > > if (strncmp(SQLSTATE, "02", 2) != 0) > > printf("SQLSTATE after fetch is %s\\ ", SQLSTATE); > EXEC SQL commit; > > return 0; > } > /* > *************************************************************************** > */ > > Appreciate any help . > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
You cannot use replaceable parameters in subscript expressions. Use the substring function: ... SUBSTR( colname, ?, ? ) ... Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 23, 2009 at 10:51 AM, PRADEEP KUMAR <pkyadav1@hotmail.com>wrote: > Hi Art, > Thanks for your response. > > I'm not using the colon but using ? to pass the parameter, sorry for the > confusion . > > Below is the sample code which works fine when I use TEXT coulmn and gives > error with CHAR: > > /* > *************************************************************************** > * > > **************************************************************************** > */ > #include <stdio.h> > #include <stdlib.h> > #include <conio.h> > #include <windows.h> > #include <sqltypes.h> > #include <sys/timeb.h> > #include <time.h> > void InfConnect(); > void InfDisConnect(); > int InfBulkInsert(int nRows); > int InfInsertSingle(int nRows); > EXEC SQL define FNAME_LEN 15; > EXEC SQL define LNAME_LEN 15; > EXEC SQL BEGIN DECLARE SECTION; > > char dbname[100]; > > char user[100]; > > char password[100]; > EXEC SQL END DECLARE SECTION; > /* > *************************************************************************** > */ > int main(int argc, char *argv[]) > { > strcpy(dbname, argv[1]); > strcpy(user, argv[2]); > strcpy(password, argv[3]); > InfConnect(); > InfInsertSingle(1); > InfDisConnect(); > return 0; > } > /* > *************************************************************************** > */ > void InfDisConnect() > { > > EXEC SQL disconnect current; > > printf("\\ select_bind demo program complete.\\ \\ "); > return; > } > /* > *************************************************************************** > */ > void InfConnect() > { > > printf("\\ select_bind demo ESQL program running.\\ \\ "); > > EXEC SQL WHENEVER ERROR STOP; > > EXEC SQL connect to :dbname user :user using :password; > > return; > } > /* > *************************************************************************** > */ > int InfInsertSingle(int nRows) > { > EXEC SQL BEGIN DECLARE SECTION; > > char stmt[1024] = "SELECT TABNAME[?,4] from 'informix'.systables where > tabid=?"; > > int id = 0, i=0; > > int sqld = 1; > > char StatementID[11]="TESTStmtID"; > > char CursorID[11]="CursorID1"; > > struct sqlda *da_ptr; > > struct sqlda ParmDA; > > struct sqlda *pParmDA; > > struct sqlvar_struct *col_ptr; > > struct sqlda FetchDA; > > struct sqlda *lpFetchDA; > EXEC SQL END DECLARE SECTION; > EXEC SQL BEGIN; > > EXEC SQL PREPARE :StatementID from :stmt; > > printf("EXEC SQL PREPARE :StatementID from :stmt\\ "); > > EXEC SQL DESCRIBE :StatementID into da_ptr; > > printf("EXEC SQL DESCRIBE :StatementID into da_ptr\\ "); > > ParmDA.sqlvar = (struct sqlvar_struct *) malloc( sqld * sizeof(struct > sqlda)); > > memcpy(&FetchDA, da_ptr, sizeof(struct sqlda)); > > FetchDA.sqlvar = (struct sqlvar_struct*) malloc(da_ptr->sqld * > sizeof(struct > sqlvar_struct)); > > memcpy(FetchDA.sqlvar, da_ptr->sqlvar, sizeof(struct sqlvar_struct) * > da_ptr->sqld); > > col_ptr = FetchDA.sqlvar; > > col_ptr->sqltype = CCHARTYPE; > > col_ptr->sqllen = 255; > > col_ptr->sqldata = (char *) malloc(col_ptr->sqllen * 1); > > lpFetchDA = &FetchDA; > > EXEC SQL DECLARE :CursorID CURSOR FOR :StatementID; > > printf("EXEC SQL DECLARE :CursorID CURSOR FOR :StatementID; %d columns\\ ", > da_ptr->sqld); > > for (id = 2000; id < 2002 + nRows; id++) > > { > > ParmDA.sqld = sqld; > > for( i = 0; i<sqld; i++) > > { > > ParmDA.sqlvar[i].sqlname = NULL; > > ParmDA.sqlvar[i].sqlformat = NULL; > > ParmDA.sqlvar[i].sqlitype = 0; > > ParmDA.sqlvar[i].sqlilen = 0; > > ParmDA.sqlvar[i].sqlidata = NULL; > > ParmDA.sqlvar[i].sqlind = NULL; > > ParmDA.sqlvar[i].sqltype = CINTTYPE; > > ParmDA.sqlvar[i].sqldata = (char*)&id; > > pParmDA = &ParmDA; > > printf("EXEC SQL OPEN :CursorID USING DESCRIPTOR pParmDA\\ "); > > EXEC SQL OPEN :CursorID USING DESCRIPTOR pParmDA; > > EXEC SQL FETCH :CursorID USING DESCRIPTOR lpFetchDA; > > printf("EXEC SQL FETCH :CursorID USING DESCRIPTOR lpFetchDA\\ "); > > printf("Data : %s\\ ", lpFetchDA->sqlvar[i].sqldata); > > // EXEC SQL EXECUTE :StatementID USING DESCRIPTOR pParmDA; > > } > > } > > if (strncmp(SQLSTATE, "02", 2) != 0) > > printf("SQLSTATE after fetch is %s\\ ", SQLSTATE); > EXEC SQL commit; > > return 0; > } > /* > *************************************************************************** > */ > > Appreciate any help . > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bd590faab78047912192a
Yes, SUBSTR works with the host variable. We had this implementation before but since SUBSTR doesn't works for TEXT, we are trying to figure out a way which might work with CHAR and TEXT both. Thanks for your help.