Example Program: A Simple Query

http://download-west.oracle.com/docs/cd/B12037_01/appdev.101/a97269/pc_02prc.htm#sthref99

1. /*
2. *  sample1.pc
3. *
4. *  Prompts the user for an employee number,
5. *  then queries the emp table for the employee's
6. *  name, salary and commission.  Uses indicator
7. *  variables (in an indicator struct) to determine
8. *  if the commission is NULL.
9. *
10. */
12. #include <stdio.h>
13. #include <string.h>
16. /* Define constants for VARCHAR lengths. */
17. #define     UNAME_LEN      20
18. #define     PWD_LEN        40
20. /* Declare variables.No declare section is needed if MODE=ORACLE.*/
21. VARCHAR     username[UNAME_LEN]; 
22. /* VARCHAR is an Oracle-supplied struct */
23. varchar     password[PWD_LEN];   
24. /* varchar can be in lower case also. */
25. /*
26. Define a host structure for the output values of a SELECT statement.
27. */
28. struct {
29.   VARCHAR   emp_name[UNAME_LEN];
30.   float     salary;
31.   float     commission;
32. } emprec;
33. /*
34. Define an indicator struct to correspond to the host output struct. */
35. struct
36. {
37.   short     emp_name_ind;
38.   short     sal_ind;
39.   short     comm_ind;
40. } emprec_ind;
42. /*  Input host variable. */
43. int         emp_number;
44. int         total_queried;
45. /* Include the SQL Communications Area.
46.   You can use #include or EXEC SQL INCLUDE. */
47. #include <sqlca.h>
49. /* Declare error handling function. */
50. void sql_error();
52. main()
53. {
54.   char temp_char[32];
56. /* Connect to ORACLE--
57. * Copy the username into the VARCHAR.
58. */
59.   strncpy((char *) username.arr, "SCOTT", UNAME_LEN);
60. /* Set the length component of the VARCHAR. */
61.   username.len = strlen((char *) username.arr);
62. /* Copy the password. */
63.   strncpy((char *) password.arr, "TIGER", PWD_LEN);
64.   password.len = strlen((char *) password.arr);
65. /* Register sql_error() as the error handler. */
66.   EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
68. /* Connect to ORACLE.  Program will call sql_error()
69. * if an error occurs when connecting to the default database.
70. */
71.   EXEC SQL CONNECT :username IDENTIFIED BY :password;
72.   printf("\nConnected to ORACLE as user: %s\n", username.arr);
73. /* Loop, selecting individual employee's results */
74.   total_queried = 0;
75.   for (;;)
76.   {
77. /* Break out of the inner loop when a
78. * 1403 ("No data found") condition occurs.
79. */
80.        EXEC SQL WHENEVER NOT FOUND DO break;
81.        for (;;)
82.        {
83.            emp_number = 0;
84.            printf("\nEnter employee number (0 to quit): ");
85.            gets(temp_char);
86.            emp_number = atoi(temp_char);
87.            if (emp_number == 0)
88.                break;
89.            EXEC SQL SELECT ename, sal, NVL(comm, 0)
90.                INTO :emprec INDICATOR :emprec_ind
91.                FROM EMP
92.                WHERE EMPNO = :emp_number;
93. /* Print data. */
94.            printf("\n\nEmployee\tSalary\t\tCommission\n");
95.            printf("--------\t------\t\t----------\n");
96. /* Null-terminate the output string data. */
97.            emprec.emp_name.arr[emprec.emp_name.len] = '\0';
98.            printf("%-8s\t%6.2f\t\t",
99.                emprec.emp_name.arr, emprec.salary);
100.            if (emprec_ind.comm_ind == -1)
101.                printf("NULL\n");
102.            else
103.                printf("%6.2f\n", emprec.commission);
105.            total_queried++;
106.        }  /* end inner for (;;) */
107.        if (emp_number == 0) break;
108.        printf("\nNot a valid employee number - try again.\n");
109.   } /* end outer for(;;) */
111.   printf("\n\nTotal rows returned was %d.\n", total_queried);
112.   printf("\nG'day.\n\n\n");
114. /* Disconnect from ORACLE. */
115.   EXEC SQL COMMIT WORK RELEASE;
116.   exit(0);
117. }
118. void sql_error(msg)
119. char *msg;
120. {
121.   char err_msg[128];
122.   int buf_len, msg_len;
124.   EXEC SQL WHENEVER SQLERROR CONTINUE;
125.   printf("\n%s\n", msg);
126.   buf_len = sizeof (err_msg);
127.   sqlglm(err_msg, &buf_len, &msg_len);
128.   printf("%.*s\n", msg_len, err_msg);
129.   EXEC SQL ROLLBACK RELEASE;
130.   exit(1);
131. }