Reginald Bailey wrote:
>
> RDBMS Version (if applicable): 7.1.6
> Error Number (if applicable): 1036
> Product and Version: Pro*C 2.0
> Operating System: Sun Solaris V2 Sparc
>
> We have been experiencing problems with doing an insert into a table.
> The usual way of doing a Method 2 Dynamic SQL insert with bind variables
> does not seem to work. We could not get past the EXEC SQL PREPARE
> portion. The following is the code that finally got through the PREPARE
> without getting error ORA-917, "Missing comma". Now we get ORA-1036,
> "illegal variable name/number".
>
> The following is a cut down version of the program that tries to insert
> one record.
>
> #include
> #include
> #include
> #include
>
> /* #include "GCDMPrototypes.h"*/
> #include "SAICGeoDefines.h"
> #include "ora_halflink.h"
> #include
> #include
> /*===========================================================================
> SQL routines to import marker data from OpenWorks into GCDM
>
> =============================================================================*/
>
> /* Initialize the SQLCA */
> #ifndef SQLCA_INIT
> #define SQLCA_INIT
> #endif
> /* Initialize the ORACA */
> #ifndef ORACA_INIT
> #define ORACA_INIT
> #endif
> /* #define DP_LEN 5*/
> /* #define DATE_LEN 25*/
> #define MAXMESSAGELEN 256
> #define SUB_STRING 30
>
> EXEC SQL INCLUDE ORACA;
> EXEC ORACLE OPTION (ORACA=YES); /* Enable ORACA */
>
> EXEC SQL INCLUDE SQLCA;
> EXEC SQL INCLUDE SQLDA;
>
> /* extern int parse_flag; */
>
> long SQLCODE;
> /* SQLDA *bind_dp;*/
>
> EXEC SQL BEGIN DECLARE SECTION;
>
> char sql_stmt[2500];
> /* EXEC SQL VAR sql_stmt IS STRING (2500);
> VARCHAR lock_stmt[512];
> */
> char current_well_id[31];
> EXEC SQL VAR current_well_id IS STRING (31);
>
> char hor_dp_code[DP_LEN];
> EXEC SQL VAR hor_dp_code IS STRING (DP_LEN);
>
> char horizon_code[17];
> EXEC SQL VAR horizon_code IS STRING (17);
> char hor_dia_code[17];
> EXEC SQL VAR hor_dia_code IS STRING (17);
> int hor_seq_number;
>
> char table_name[36] = ""; /* current table name */
> EXEC SQL VAR table_name IS STRING (36);
>
> char well_brid[31];
> EXEC SQL VAR well_brid IS STRING (31);
> char dp_code[DP_LEN]; /* data provider code */
> EXEC SQL VAR dp_code IS STRING (DP_LEN);
> char geol_provider_code[17];
> EXEC SQL VAR geol_provider_code IS STRING (17);
> char marker_code[17]; /* usually marker name */
> EXEC SQL VAR marker_code IS STRING (17);
> char dia_code[17]; /* horizon data interpreter analyst code */
> EXEC SQL VAR dia_code IS STRING (17);
> /* this number is usually generated by the database on inserts */
> int seq_number;
> float up_md_depth; /* upper measured depth */
> float low_md_depth;
> char up_comment_code[5];
> EXEC SQL VAR up_comment_code IS STRING (5);
> char low_comment_code[5];
> EXEC SQL VAR low_comment_code IS STRING (5);
> char elev_ref_cd[5];
> EXEC SQL VAR elev_ref_cd IS STRING (5);
> char source_table_code[5];
> EXEC SQL VAR source_table_code IS STRING (5);
> char eff_date[DATE_LEN];
> EXEC SQL VAR eff_date IS STRING (DATE_LEN);
>
> /* int sql_code = 0; */
> /* char err_text[512] = "";
> EXEC SQL VAR err_text IS STRING (512);
> */
> /* null indicators */
> short well_brid_ind;
> short dp_code_ind;
> short geol_provider_code_ind;
> short marker_code_ind;
> short dia_code_ind; /* horizon data interpreter analyst code */
> short seq_number_ind;
> short up_md_depth_ind;
> short low_md_depth_ind;
> short up_comment_code_ind;
> short low_comment_code_ind;
> short elev_ref_cd_ind;
> short source_table_code_ind;
> short eff_date_ind;
>
> EXEC SQL END DECLARE SECTION;
>
> /* Geoshare Rules:
> Overwrite - if record does not exist, skip it or do nothing.
> Insert - if record exists, skip it or do nothing.
> Replace - as expected.
> Update - as expected.
> */
>
> /*==============================================================================
>
> imp_marker_data: Place marker data from wellbore horizon into
> prospective tables.
>
> RWB 9 JUN 97 Creation.
> RWB 20 JUN 97 Added seq_number to insert and update of tables.
> ==================================================================================*/
>
> int main ()
> {
> char *Module = "{import_only:main}";
> char Message[MAXMESSAGELEN] = "";
> char transaction_type[8] = "";
> char old_table_name[36] = "";
> char geol_prov[17] = "";
> char pick_type[5] = "";
> /* GCDM_WellMarker_T *current_well_marker = NULL; */
> int return_code = 0;
> int rows_updated;
> int bind_count;
> int db_error_code = 0;
> int stmt_len = 0;
> int date_length = 0;
> int parse_flag = 1;
> /*====================================================================*/
>
> connect_oracle("elasseter", "a");
>
> EXEC SQL COMMIT;
>
> /* initialize the sql_stmt buffer */
> memset ((void *) sql_stmt, 0, sizeof (sql_stmt));
> strcpy(well_brid, "GEOSHARE_TEST_1");
> strcpy(dp_code, "EXON");
> strcpy(dia_code, "UNK");
> strcpy(up_comment_code, "OKAY");
> strcpy(eff_date, "1997-06-27:00:00:00");
> up_md_depth = 55555.0;
> seq_number = 1;
> strcpy(geol_provider_code, "KAGEAN");
> strcpy(marker_code, "UNKNOWN");
>
> well_brid_ind = strlen(well_brid);
> dp_code_ind = strlen(dp_code);
> dia_code_ind = strlen(dia_code);
> up_comment_code_ind = strlen(up_comment_code);
> eff_date_ind = strlen(eff_date);
> up_md_depth_ind = 1;
> seq_number_ind = 1;
> geol_provider_code_ind = strlen(geol_provider_code);
> marker_code_ind = strlen(marker_code);
>
> parse_flag = 1;
> EXEC SQL WHENEVER SQLERROR
> DO return_code = sql_imp_error(Module);
> date_length = strlen(eff_date);
>
> /* strcpy ((char *) sql_stmt, "INSERT INTO WLBR_SEQ_BNDRY");*/
> /* strcat ((char *) sql_stmt, table_name); */
>
> strcpy ( sql_stmt,
> "INSERT INTO WLBR_SEQ_BNDRY (WLBR_ID, SEQ_STRAT_DP_CD, GEOL_PROV_CD,
> SEQ_BNDRY_ID,"
> " SEQ_DIA_CD, SEQ_NBR,"
> " BNDRY_MD_DPTH, PICK_CMNT_CD, INTRPT_EFV_DATE, OWNER_SECUR_CD)"/* );*/
> /* strcat ((char *) sql_stmt,*/ " VALUES (':m1:m1ind', ':m2:m2ind',
> ':m3:m3ind', "
> "':m4:m4ind', ':m5:m5ind', TO_NUMBER(':m6:m6ind'),
> TO_NUMBER(':m7:m7ind'), "
> "':m8:m8ind', TO_DATE(':m9','YYYY-MM-DD:HH24:MI:SS'), 'TST_D')");
> /* sql_stmt.len = strlen(((char *) sql_stmt) + 1); */
> sql_stmt[strlen(sql_stmt) + 1]='\0';
> stmt_len = strlen(sql_stmt);
> EXEC SQL PREPARE insert_stmt FROM :sql_stmt;
> if (return_code == SUCCESS)
> {
> EXEC SQL EXECUTE insert_stmt USING
> :well_brid:well_brid_ind,
> :dp_code:dp_code_ind,
> :geol_provider_code:geol_provider_code_ind,
> :marker_code:marker_code_ind,
> :dia_code:dia_code_ind,
> :seq_number:seq_number_ind,
> :up_md_depth:up_md_depth_ind,
> :up_comment_code:up_comment_code_ind,
> :eff_date; /* :eff_date_ind; */
> /* */
> db_error_code = SQLCODE;
> }
> EXEC SQL COMMIT;
> disconnect_oracle();
> exit (return_code);
> }
> /*==================================================================================*/
> int sql_imp_error (char *Module)
> {
>
> char Message[MAXMESSAGELEN];
> int buf_len, msg_len;
> int err_code = 0;
> int return_code = 0;
> extern long SQLCODE; /* local copy of SQLCODE */
> int parse_flag = 1;
> int offset = 0;
> char sub_sql_string[SUB_STRING] = "";
> err_code = SQLCODE;
> buf_len = sizeof (Message);
>
> /* NOT FOUND is not really an error condition */
> if ( (err_code == 1403)
> || (err_code == NO_DATA_FOUND)
> || (err_code == NO_DATA_FOUND2) )
> {
> return_code = DATA_NOT_FOUND;
> return (return_code);
> }
>
> /* ORACLE error handler */
> EXEC SQL WHENEVER SQLERROR CONTINUE;
>
> /* check for extents blowout problem */
> return_code = FATAL_ERROR;
>
> if ( err_code == LOGON_DENIED )
> {
> return_code = NO_LOGON;
> }
> if ( err_code == DUP_VAL_ON_INDEX )
> {
> return_code = DUPLICATE_VALUE;
> }
>
> GeoWriteMessage (INFO, Module, "An Oracle error occurred!");
> sprintf (Message, "%.70s",sqlca.sqlerrm.sqlerrmc);
> GeoWriteMessage (INFO, "{sql_imp_error}", Message);
>
> /* Print the text of the last SQL statement */
> if (parse_flag)
> {
> sprintf (Message, "Last SQL statement at or near line number %d\n %.*s",
> oraca.oraslnr, oraca.orastxt.orastxtl, oraca.orastxt.orastxtc);
> GeoWriteMessage (INFO, Module, Message);
>
> /* Get full text of error message. */
> if (err_code < 0)
> {
> sqlglm (Message, &buf_len, &msg_len);
> /* Null-terminate and print the text. */
> Message[msg_len] = '\0';
> GeoWriteMessage (0, Module, Message);
> printf("SQL Statement: \n\n%s\n", (char *) sql_stmt);
> }
> offset = sqlca.sqlerrd[4];
> sprintf (Message,
> "Parse error at character offset %d in SQL statement.",
> sqlca.sqlerrd[4]);
>
> GeoWriteMessage (INFO, Module, Message);
> if ( offset > 0 )
> {
> strncpy(sub_sql_string, sql_stmt[offset], SUB_STRING - 1);
> sprintf (Message, "Error Begins at: %s ", sub_sql_string);
> /* sql_stmt[offset],
> sql_stmt[offset+1],sql_stmt[offset+2],sql_stmt[offset+3],
> sql_stmt[offset+4],sql_stmt[offset+5],
> sql_stmt[offset+6], sql_stmt[offset+7], sql_stmt[offset+8],
> sql_stmt[offset+9], sql_stmt[offset+10]); */
> GeoWriteMessage (INFO, Module, Message);
> }
> }
>
> EXEC SQL ROLLBACK WORK;
>
> /* reset SQLCODE */
> if ( SQLCODE == 0 )
> SQLCODE = err_code;
>
> return (return_code);
> }
> /*============================================================================*/
>
> If anyone has a clue to what's going on, please help. In the Insert
> string
> we had to enclose the substitution variables within single quotes, which
> I
> have never seen it necessary to do before. When we tried the same query
> with
> SQL*Plus using substitution variables, we had to enclose the variables
> in
> single quotes, otherwise it complained of the variable being the name of
> a column.
>
> If anyone has any suggestions, please let me know.
>
> Reginald Bailey
> c/o
> sryan_at_lgc.com or rwbailey_at_usa.net
>
> --
> --------------------------------------------------------------
> Reginald W. Bailey
> Consultant
> c/o Landmark Graphics Corp. Houston, TX 281-368-8937
> rbailey_at_lgc.com
> rwbailey_at_usa.net
> --------------------------------------------------------------
A solution was found. By changing the libraries that were being used,
the "'" (singel quotes) could be removed from the substitution variables
and the INSERT behaved normally. I specifically included the library
sqlnet.a in the library list portion of the make file and removed
references to libclntsh.so.1.0 for Oracle 7.1.6. That library does not
exist. Also removed libpeer.a.
--
--------------------------------------------------------------
Reginald W. Bailey
Consultant
c/o Landmark Graphics Corp. Houston, TX 281-368-8937
rbailey_at_lgc.com
rwbailey_at_usa.net
--------------------------------------------------------------
Received on Tue Jul 08 1997 - 00:00:00 CDT
