Pro * C dynamic SQL example

Environment:

Winxpsp2 + vc6 + Oracle10g

Code:

// Define the symbol constant <br/> # define username "Scott" <br/> # define password "X" <br/> # include <stdio. h> </P> <p> // description of sqlca and consumer a <br/> exec SQL include sqlca; <br/> exec SQL include consumer; </P> <p> // enable the Oracle Communication zone: required a = Yes, so that it can be used <br/> exec Oracle option (required a = yes ); </P> <p> // description of the SQL variable <br/> exec SQL begin declare section; <br/> char * username = username; <br/> char * Password = password; <br/> varchar sqlstmt [80]; <br/> int emp_nu Mber; <br/> varchar emp_name [15]; <br/> varchar job [50], job1 [50], job2 [50]; <br/> float salary; <br/> exec SQL end declare section; <br/> main () <br/>{< br/> exec SQL whenever sqlerror goto sqlerror; </P> <p> // when an error occurs, save the SQL statement to limit a. <br/> limit. orastxtf = orastferr; </P> <p> // log on to Oracle. <br/> exec SQL CONNECT: username identified by: password; <br/> printf ("/nconnect to Oracle. /n "); </P> <p> // construct a dynamic SQL statement <br/> sqlstmt. Len = sprintf (sqlstmt. arr, "insert into EMP (empno, ename, job, Sal) values (: V1,: V2,: V3,: V4 )"); </P> <p> // display SQL statements <br/> puts (sqlstmt. ARR); </P> <p> // use the prepare statement to analyze the current dynamic insert statement. The statement name is S <br/> exec SQL prepare s from: sqlstmt; </P> <p> // loop insert table <br/> for (;) <br/> {<br/> printf ("/nenter employee number: "); <br/> scanf (" % d ", & emp_number); <br/> If (emp_number = 0) break; <br/> printf ("/nenter employee name:"); <br/> scanf ("% s", & E Mp_name.arr); <br/> emp_name.len = strlen (emp_name.arr); <br/> printf ("/nenter employee job:"); <br/> scanf ("% s ", & job. ARR); <br/> job. len = strlen (job. ARR); <br/> salary = 0; // With vc6, missing this line will cause c run-time error r6002. <br/> printf ("/nsalenter ary: "); <br/> scanf (" % F ", & Salary); <br/> exec SQL execute s using: emp_number,: emp_name,: Job,: salary; <br/>}</P> <p> // submit a transaction and exit Oracle <br/> exec SQL Commit release; <br/> printf ("/nhave a good day! /N "); <br/> exit (0); <br/> sqlerror: <br/> // print the error message <br/> printf ("/n %. * s/n ", sqlca. sqlerrm. sqlerrml, sqlca. sqlerrm. sqlerrmc); </P> <p> // print the SQL statement with an error <br/> printf ("/n/" %. * s... /"/N", then. orastxt. orastxtl, category. orastxt. orastxtc); </P> <p> // print the row number and file name of the SQL statement with an error. <br/> printf ("On line % d of %. * s/n ", then. oraslnr, <br/> specify. orasfnm. orasfnml, before. orasfnm. orasfnmc); </P> <p> // rolls back the transaction and exits Oracle <br/> exec SQL whenever sqlerror continue; <br/> exec SQL rollback release; <br/> exit (1); <br/>}