Home » Developer & Programmer » Precompilers, OCI & OCCI » weird No data found error in Pro*C code.
| weird No data found error in Pro*C code. [message #94272] |
Mon, 28 June 2004 20:12 |
Jai Vrat Singh
Messages: 205
Registered: September 2002
Location: Singapore
|
Senior Member |
|
|
hi.
I am getting the No DATA found error (ORA-1403) wierdly raised when i run my executable generated by my pro*C code.
This query gives no data found--[>]
EXEC SQL SELECT SUBSTR(AC.TABLE_NAME,1,30) table_name,
SUBSTR(ACC.COLUMN_NAME,1,30) column_name,
SUBSTR(ACC.CONSTRAINT_NAME,1,30) constraint_name,
AC.CONSTRAINT_TYPE INTO :constraint_arr INDICATOR :h_constraint_arr
FROM ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
WHERE AC.TABLE_NAME = ACC.TABLE_NAME --AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND AC.TABLE_NAME = 'CDA_BOOK_STATUS';
But the data is there , becuase if I use the query
EXEC SQL SELECT count(*) INTO :count:h_count
FROM ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
WHERE AC.TABLE_NAME = ACC.TABLE_NAME AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND AC.TABLE_NAME = 'CDA_BOOK_STATUS';
The count gives 7 rows which is correct.
I Cannot really understand what is wrong ??
Can someone please help.
MY while code is posted below ( queries above are taken from this code only)
/*************START OF CODE*******************/
#include<stdio.h>
#include <sqlda.h>
#include <sqlcpr.h>
#include<string.h>
#include <stdlib.h>
#define BLK_SIZE 1024
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE hostvar.h;
EXEC SQL INCLUDE utility.h;
/* int createOutputRow(struct table_struct *ptr, struct host_table_struct *h_ptr ); */
int line_num;
int main( int argc , char* argv[[]]){
char login[[255]];
char password[[255]];
int i,j;
char date[[30]];
FILE* fptr=NULL;
char buf[[BLK_SIZE]];
/**********************BEGINS HOST VAR DECL LOCAL to main function ****************/
EXEC SQL BEGIN DECLARE SECTION;
struct table_struct table_arr[[256]];
varchar input_table[[31]];
int count=0;
short h_count=0;
struct host_table_struct h_table_polulate[[256]];
short length=0;
varchar curr_table[[31]];
short h_curr_table=0;
EXEC SQL DECLARE c_table CURSOR FOR
SELECT TABLE_NAME
FROM ALL_TABLES
WHERE SUBSTR(TABLE_NAME,1,:length) = :input_table
ORDER BY TABLE_NAME;
struct index_struct index_arr[[256]];
struct host_index_struct h_index_arr[[256]];
struct constraint_struct constraint_arr[[256]];
struct host_constraint_struct h_constraint_arr[[256]];
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR do myerr("SQL Error reporting from program");
/**********************END HOST VAR DECL ****************/
/** MAIN PROCESSING STRATS FROM HERE **/
if (getinfo("QLNCDM10","LOGIN",login) == -1){
fprintf(stderr,"Cannot read login from confign");
exit(-1);
}
getinfo("QLNCDM10","PASSWORD",password);
strcat(login,"@");
strcat(login,"QLNCDM10");
printf("nnn");
line_num = __LINE__ ;
EXEC SQL CONNECT :login identified by :password;
getdate(date);
printf("DATE IS %sn",date);
line_num = __LINE__ ;
/** Main loop which processes each of the tables
** passed as argumaents **
**/
for (j=1; j<argc; j++)
{
strcpy(input_table.arr,argv[[j]]);
input_table.len = strlen(argv[[j]]);
length = input_table.len;
line_num = __LINE__ ;
EXEC SQL SELECT COUNT(TABLE_NAME) INTO :count:h_count
FROM ALL_TABLES
WHERE SUBSTR(TABLE_NAME,1,:length) = :input_table;
if(count > 0){
printf(" NO of tables for %s is %dn",input_table.arr,count);
line_num = __LINE__ ;
EXEC SQL OPEN c_table;
#ifdef DEBUG
printf("After OPEN c_table sqlca.sqlcode=%dn",sqlca.sqlcode);
#endif
for(;;)
{
memset(curr_table.arr,' ',31*sizeof(char));
h_curr_table = 0;
line_num = __LINE__;
EXEC SQL FETCH c_table INTO :curr_table INDICATOR :h_curr_table;
/* printf("After FETCH sqlca.sqlcode=%d indicator is %d n",sqlca.sqlcode,h_curr_table); */
curr_table.len = strlen(curr_table.arr);
if(sqlca.sqlcode != 0)
{
break;
}
if(h_curr_table !=0){
printf("Fetch trucated the table name i think n");
}
/* printf("Going to execute query for >>%s<< n",curr_table.arr); */
/** QUERY 2 **/
sqlca.sqlcode =0;
memset(table_arr,0,265*(sizeof(struct table_struct)));
EXEC SQL SELECT COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE
INTO :table_arr INDICATOR :h_table_polulate
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME= :curr_table;
EXEC SQL SELECT count(*)
INTO :count
FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = :curr_table ;
/*
printf("AFter Going to execute query for >>%s<< count=%d n",curr_table.arr,count);
exit(1);
if(sqlca.sqlcode != 0){
fprintf(stderr,"Error Occurred just above line %d for QUERY 2n",__LINE__);
fprintf(stderr,"Sql error:%sn",sqlca.sqlerrm.sqlerrmc);
exit(-1);
}
*/
sprintf(buf,"nnTABLE %s---[>]n",curr_table.arr);
fprintf(stdout,"%s",buf);
fprintf(stdout,"%snn","------------------------------------------------------------------------------");
sprintf(buf,"%32-s %20-s %10-sn","C O L U M N - N A M E",
"D A T A - T Y P E",
"N U L L / N O T - N U L L"
);
if( count > 0)
fprintf(stdout,"%s",buf);
for ( i=0;i<count;i++){
table_arr[[i]].column_name.len = strlen(table_arr[[i]].column_name.arr);
table_arr[[i]].data_type_name.len = strlen(table_arr[[i]].data_type_name.arr);
if(!createOutputRow(table_arr+i, h_table_polulate +i)){
fprintf(stderr,"Cannot create output rown");
}
}
memset(constraint_arr,0,256*(sizeof(struct constraint_struct)));
memset(h_constraint_arr,0,256*(sizeof(struct host_constraint_struct)));
sqlca.sqlcode = 0;
EXEC SQL SELECT count(*) INTO :count:h_count
FROM ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
WHERE AC.TABLE_NAME = ACC.TABLE_NAME AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND AC.TABLE_NAME = 'CDA_BOOK_STATUS';
printf(" The count of constraints is %dn",count);
EXEC SQL SELECT SUBSTR(AC.TABLE_NAME,1,30) table_name,
SUBSTR(ACC.COLUMN_NAME,1,30) column_name,
SUBSTR(ACC.CONSTRAINT_NAME,1,30) constraint_name,
AC.CONSTRAINT_TYPE INTO :constraint_arr INDICATOR :h_constraint_arr
FROM ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
WHERE AC.TABLE_NAME = ACC.TABLE_NAME AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND AC.TABLE_NAME = 'CDA_BOOK_STATUS';
printf( "AFTER GETTING CONSTRAINTS FOR %s SQLCODE IS :%dn",curr_table.arr,sqlca.sqlcode);
if( sqlca.sqlcode != 1403 ) {
printf("CPMES AFTER EXECUTING CONSTRAIONT QUERY n");
i=0 ; /** intialize value of i =0 before printing constraints **/
while(1){ /** to print the constarints obtained abobve **/
if(i ==0)/** to print header **/
fprintf(stdout,"nn %-30s %-30s %-30sn", "C O L U M N _ N A M E","C O N S T R A I N T _ N A M E","C O N S T R A I N T _ T Y P E");
if( h_constraint_arr[[i]].h_column_name !=0 )
break;
fprintf(stdout," %30-s %-30s %-2sn", constraint_arr[[i]].constraint_name.arr,
constraint_arr[[i]].column_name.arr,
constraint_arr[[i]].constraint_type.arr);
i++;
} /** end to print the constarints obtained abobve **/
}
else
fprintf(stdout,"nn %-100s n", " N O C O N S T R A I N T S F O R T H I S T A B L E");
EXEC SQL SELECT count(*) INTO :count:h_count
FROM ALL_IND_COLUMNS
WHERE TABLE_NAME = :curr_table;
/*printf("Count of indexes is %dn",count); */
EXEC SQL SELECT aic.INDEX_NAME,
SUBSTR(aic.COLUMN_NAME,1,30) ,
ai.INDEX_TYPE
INTO :index_arr INDICATOR :h_index_arr
FROM ALL_IND_COLUMNS aic,
ALL_INDEXES ai
WHERE aic.TABLE_NAME =:curr_table
and ai.TABLE_NAME = aic.TABLE_NAME
and aic.INDEX_NAME = ai.INDEX_NAME
order by aic.table_name,aic.index_name,substr(aic.column_name,1,30) ;
if( count > 0)
fprintf(stdout,"nn %-30s %-30s %-30sn", "I N D E X _ N A M E","C O L U M N _ N A M E","I N D E X _ T Y P E");
else
fprintf(stdout,"nn %-100s n", " N O I N D E X E S ON A N Y O F T H E C O L U M N S");
for(i=1; i<=count; i++)
fprintf(stdout," %30-s %-30s %-30sn", index_arr[[i]].index_name.arr,index_arr[[i]].column_name.arr,index_arr[[i]].index_type.arr);
}
EXEC SQL CLOSE c_table;
} /** end of if -- when count >0**/
else {
fprintf(stderr,"nnNo table starts with %sn",argv[[j]]);
printf("************************************n");
}
}
return 0;
}
int createOutputRow(struct table_struct *ptr, struct host_table_struct *h_ptr ){
char data_type[[6]];
char dataDescription[[100]];
char buf[[BLK_SIZE]];
const char three_eight[[]]={'3','8',' '};
/** COPY n bytes into data_type **/
/** null terminate in case lenght of source is more **/
strncpy(data_type,ptr->data_type_name.arr,5*sizeof(char));
*(data_type +5) =' ';
if( (strcmp(data_type,"VARCH") ==0)||(strcmp(data_type,"CHAR")==0)){
strcpy(dataDescription,ptr->data_type_name.arr);
strcat(dataDescription,"(");
sprintf(dataDescription,"%s%d",dataDescription,ptr->data_length);
strcat(dataDescription,")");
}
else if(strcmp(data_type,"DATE") ==0){
strcpy(dataDescription,ptr->data_type_name.arr);
}
else if(strcmp(data_type,"NUMBE") ==0){
strcpy(dataDescription,ptr->data_type_name.arr);
strcat(dataDescription,"(");
if(( h_ptr->h_data_precision == -1) || ( h_ptr->h_data_scale == -1) ){
strcat(dataDescription,"38");
} else{
if(ptr->data_scale ==0){
sprintf(dataDescription,"%s%d",dataDescription,ptr->data_precision);
}
else{
sprintf(dataDescription,"%s%d",dataDescription,ptr->data_precision);
strcat(dataDescription,",");
sprintf(dataDescription,"%s%d",dataDescription,ptr->data_scale);
}
}
strcat(dataDescription,")");
} /** IF NUMBER **/
else{
strcpy(dataDescription,ptr->data_type_name.arr);
}
sprintf(buf,"%32-s %20-s %10-sn",(ptr->column_name).arr,
dataDescription,
!strcmp(ptr->nullable.arr,"N")?"NOT NULL":" "
);
fprintf(stdout,"%s",buf);
return(1);
}
/* Error handling function. */
int myerr( char * msg){
size_t clen, fc;
char cbuf[[128]];
clen = sizeof (cbuf);
sqlgls((char *)cbuf, (size_t *)&clen, (size_t *)&fc);
printf("n%sn", msg);
printf("Statement is--n%sn", cbuf);
printf("Function code is %ldnn", fc);
sqlglm((char *)cbuf, (size_t *) &clen, (size_t *) &clen);
printf ("n%.*sn", clen, cbuf);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
fprintf(stderr,"Error after line %dn",line_num);
fprintf(stderr,"Error Detected>>%sn",(char *) sqlca.sqlerrm.sqlerrmc);
exit(EXIT_FAILURE);
}
/*************END OF CODE*********************/
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 23:01:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
