ex19.c

Example for selecting from a nested table.

00001 /* $Id: ex19.c 221 2002-08-24 12:54:47Z kpoitschke $ */
00002 #include <stdio.h>
00003 #include <stdlib.h>
00004 #include "examples.h"
00005 
00006 int select_ntable(sqlo_db_handle_t dbh)
00007 {
00008   sqlo_stmt_handle_t sth = SQLO_STH_INIT;
00009   sqlo_stmt_handle_t st2h;                     /* handle of the ref cursor */
00010   int status;
00011   char ename[11];
00012   char dname[15];
00013   char loc[14];
00014   short eind, dind, lind;
00015   int deptno = 10;
00016 
00017                /* don't know why the bind variable for deptno causes a crash */
00018   CONST char * stmt = 
00019     "SELECT ENAME, CURSOR(SELECT DNAME, LOC FROM DEPT)\n"
00020     "  FROM EMP WHERE DEPTNO = :deptno";
00021 
00022 
00023   /* parse the statement */
00024   if ( 0 <= (sth = sqlo_prepare(dbh, stmt))) {
00025 
00026     /* bind all variables */
00027     if (SQLO_SUCCESS != 
00028         (sqlo_bind_by_name(sth, ":deptno", SQLOT_INT, &deptno, sizeof(deptno), 0, 0) ) ) {
00029       error_exit(dbh, "sqlo_bind_by_name");
00030     } 
00031 
00032     /* Do the defines */
00033     /* You could also do: sqlo_define_by_pos(sth, 2, SQLOT_RSET, &st2h, 0, 0, 0, 0) */
00034     if (SQLO_SUCCESS != 
00035         ( sqlo_define_by_pos(sth, 1, SQLOT_STR, ename, sizeof(ename), &eind, 0, 0) ) ||
00036         ( sqlo_define_ntable(sth, 2, &st2h)))
00037       error_exit(dbh, "sqlo_define_ntable");
00038     
00039     /* execute the main statement */
00040     if (SQLO_SUCCESS != sqlo_execute(sth, 1))
00041       error_exit(dbh, "sqlo_execute");
00042     
00043 
00044     /* fetch from the main statement */
00045     while (SQLO_SUCCESS == (status = sqlo_fetch(sth, 1))) {
00046       printf("ENAME=%11s\n", ename);
00047 
00048 
00049       /* define the output of the second cursor */
00050       if (SQLO_SUCCESS != 
00051           ( sqlo_define_by_pos(st2h, 1, SQLOT_STR, dname, sizeof(dname), &dind, 0, 0) ) ||
00052           ( sqlo_define_by_pos(st2h, 2, SQLOT_STR, loc, sizeof(loc), &lind, 0, 0) ) )  {
00053           error_exit(dbh, "sqlo_define_by_pos");
00054         }
00055 
00056       /* execute the cursor */
00057       if(SQLO_SUCCESS != sqlo_execute(st2h, 1))
00058         error_exit(dbh, "sqlo_execute");
00059 
00060       /* fetch from the second cursor */
00061       while (SQLO_SUCCESS == (status = sqlo_fetch(st2h, 1))) {
00062         printf("    DNAME=%15s LOC=%15s\n", dname, loc);
00063 
00064       }
00065       if (status != SQLO_NO_DATA)
00066         error_exit(dbh, "sqlo_fetch(st2)");
00067 
00068     }
00069     
00070     if (SQLO_SUCCESS != sqlo_close(sth))
00071       error_exit(dbh, "sqlo_close(1)");
00072     
00073     if (SQLO_SUCCESS != sqlo_close(st2h))
00074       error_exit(dbh, "sqlo_close(2)");
00075     
00076   } else {
00077     error_exit(dbh, "sqlo_prepare");
00078   }
00079 
00080   return 1;
00081 }
00082 
00083 /* $Id: ex19.c 221 2002-08-24 12:54:47Z kpoitschke $ */

Generated on Mon May 21 13:43:28 2007 for libsqlora8 by  doxygen 1.4.7