Main Page   Modules   File List   Globals   Related Pages   Examples  

The easy interface

Functions in this group use basically bind variables passed as strings in an argv. More...

Functions

int sqlo_exists (sqlo_db_handle_t dbh, const char *table, const char *colname, const char *colval, const char *where)
 Tests if a value exists in a table.

int sqlo_count (sqlo_db_handle_t dbh, const char *table, const char *colname, const char *colval, const char *where)
 Counts the number of items in the table.

int sqlo_run (sqlo_db_handle_t dbh, const char *stmt, int argc, const char **argv)
 Run a simple sql statements with parameters.

sqlo_stmt_handle_t sqlo_open (sqlo_db_handle_t dbh, const char *stmt, int argc, const char **argv)
 Open a new cursor.

int sqlo_open2 (sqlo_stmt_handle_t *sthp, sqlo_db_handle_t dbh, const char *stmt, int argc, const char **argv)
 Open a new cursor.

int sqlo_reopen (sqlo_stmt_handle_t sth, int argc, const char **argv)
 Reopens a already used cursor.

int sqlo_fetch (sqlo_stmt_handle_t sth, unsigned int nrows)
 Fetches the data from an open cursor.

const char ** sqlo_values (sqlo_stmt_handle_t sth, int *num, int dostrip)
 Get one dataset.

const unsigned short * sqlo_value_lens (sqlo_stmt_handle_t sth, int *num)
 Get the length of the returned values.

const char ** sqlo_ocol_names (sqlo_stmt_handle_t sth, int *num)
 Get the select list columns.

const int * sqlo_ocol_name_lens (sqlo_stmt_handle_t sth, int *num)
 Get the select list columns name lengths.

int sqlo_ncols (sqlo_stmt_handle_t sth, int in)
 Get the number of bind/select-list variables.

const char * sqlo_command (sqlo_stmt_handle_t sth)
 Return the sql command.

int sqlo_close (sqlo_stmt_handle_t sth)
 Close the cursor.

int sqlo_exec (sqlo_db_handle_t dbh, const char *stmt)
 Execute a simple sql statement.

int sqlo_isopen (sqlo_stmt_handle_t sth)
 Test if a cursor is open.

int sqlo_prows (sqlo_stmt_handle_t sth)
 Return the number of processed rows by this statement.


Detailed Description

Functions in this group use basically bind variables passed as strings in an argv.

The query results are also converted to strings.


Function Documentation

int sqlo_close sqlo_stmt_handle_t    sth
 

Close the cursor.

Closes the cursor and releases the Oracle statement handle.

Returns:
  • SQLO_SUCCESS
  • < 0 on error
See also:
sqlo_open.
Examples:
ex12.c, ex13.c, ex14.c, ex15.c, ex16.c, ex17.c, ex18.c, ex19.c, ex7.c, and ex9.c.

const char* sqlo_command sqlo_stmt_handle_t    sth
 

Return the sql command.

Returns:
The active sql statement of the sth.
Parameters:
sth  I - A statement handle

int sqlo_count sqlo_db_handle_t    dbh,
const char *    table,
const char *    colname,
const char *    colval,
const char *    where
 

Counts the number of items in the table.

Counts the number of items where field = value [AND where]

Parameters:
dbh  I - A database handle
table  I - A table name
colname  I - A column name
colval  I - A column value
where  I - More conditions (optional)
Returns:
  • SQLO_SUCCESS
  • SQLO_NO_DATA
  • < 0 on error
Example:
/* $Id: ex2.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */
#include <stdio.h>
#include <stdlib.h>
#include "examples.h"

int col_count(sqlo_db_handle_t dbh, char * table_name)
{
  int stat;
  if ( 0 > (stat = sqlo_count(dbh, "USER_TAB_COLUMNS", "TABLE_NAME", table_name, NULL))) {
    error_exit(dbh, "sqlo_count");
  }
  return stat;
}

/* $Id: ex2.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */
Examples:
ex2.c.

int sqlo_exec sqlo_db_handle_t    dbh,
const char *    stmt
 

Execute a simple sql statement.

Use this to execute non-qeuery statements without bind variables.

Parameters:
dbh  A database handle
stmt  A sql statement
Returns:
The number of processed rows (DML statements), 0 (non DML statements) or < 0 on error.
Example:
/* $Id: ex8.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */
#include <stdio.h>
#include <stdlib.h>
#include "examples.h"

int update_emp(sqlo_db_handle_t dbh, double factor, const char * job)
{
  int stat;
  char stmt[1024];

  sprintf(stmt, "UPDATE EMP SET SAL = SAL * %f WHERE JOB = '%s'",
          factor, job);

  if ( 0 > (stat = sqlo_exec(dbh, stmt)))
    error_exit(dbh, "sqlo_run");

  return stat;                  /* number of processed rows */
}

/* $Id: ex8.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */

See also:
sqlo_run
Examples:
ex13.c, ex8.c, and ex9.c.

int sqlo_exists sqlo_db_handle_t    dbh,
const char *    table,
const char *    colname,
const char *    colval,
const char *    where
 

Tests if a value exists in a table.

Tests if a record exists in a table where field = value [AND where].

Parameters:
dbh  I - A database handle
table  I - A table name
colname  I - A column name
colval  I - A column value
where  I - More conditions (optional)
Returns:
  • SQLO_SUCCESS
  • SQLO_NO_DATA
  • < 0 on error.
Example:
/* $Id: ex1.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */
#include <stdio.h>
#include <stdlib.h>
#include "examples.h"

int table_exists(sqlo_db_handle_t dbh, char * table_name)
{
  int stat;
  if ( 0 > (stat = sqlo_exists(dbh, "USER_TABLES", "TABLE_NAME", table_name, NULL))) {
    error_exit(dbh, "sqlo_exists");
   } 
  return stat == SQLO_SUCCESS ? 1 : 0;
 }

/* $Id: ex1.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */
Examples:
ex1.c.

int sqlo_fetch sqlo_stmt_handle_t    sth,
unsigned int    nrows
 

Fetches the data from an open cursor.

This functions fetches data from an open cursor, if the sql was a query. For non-queries, the statement is executed. Use The easy interface to get the data.

Attention:
nrows must be 1 for cursors opened with The easy interface or @sqlo_open2. For cursors where the output variables were defined manually with @sqlo_define_by_pos, this can be for example the size of the array in which you are fetching.
Parameters:
sth  I - A statement handle
nrows  I - The number of rows to fetch.
Returns:
  • SQLO_SUCCESS
  • SQLO_NO_DATA
  • < 0 on error
See also:
sqlo_open2, sqlo_values, sqlo_close
Examples:
ex11.c, ex12.c, ex17.c, ex18.c, ex19.c, and ex7.c.

int sqlo_isopen sqlo_stmt_handle_t    sth
 

Test if a cursor is open.

Parameters:
sth  I - A statement handle.
Returns:
  • SQLO_SUCCESS if the cursor is open
  • 1 if not (unused sth)
  • SQLO_INVALID_STMT_HANDLE.
See also:
sqlo_open2

int sqlo_ncols sqlo_stmt_handle_t    sth,
int    in
 

Get the number of bind/select-list variables.

Parameters:
sth  I - A statement handle
in  I - 1 returns the number of bind variables, 0 returns the number of select list columns.
Returns:
  • The number of columns
  • SQLO_INVALID_STMT_HANDLE

const int* sqlo_ocol_name_lens sqlo_stmt_handle_t    sth,
int *    num
 

Get the select list columns name lengths.

Use this function to get the length of each select list column. Call this when you need the length of the column, for formatting purposes etc.

Parameters:
sth  I - A statement handle
num  O - A destination where the function can write the size of the returned array.
Returns:
A pointer to an array of integers containing the lengths
See also:
sqlo_ocol_names, sqlo_fetch, sqlo_open2, sqlo_close.
Examples:
ex7.c.

const char** sqlo_ocol_names sqlo_stmt_handle_t    sth,
int *    num
 

Get the select list columns.

Use this function to get the select list column names. Most usefull for dynamic sql, where you don't know the sql statement at all.

Parameters:
sth  I - A statement handle
num  O - A destination where the function can write the size of the returned array (optional).
Returns:
A pointer to an array of strings containing the column names
See also:
sqlo_fetch, sqlo_values, sqlo_open2, sqlo_close, sqlo_ocol_name_lens.
Examples:
ex7.c.

sqlo_stmt_handle_t sqlo_open sqlo_db_handle_t    dbh,
const char *    stmt,
int    argc,
const char **    argv
 

Open a new cursor.

This function opens a new cursor for a query statement.

If the stmt is a SELECT statement, the function sets the attribute OCI_ATTR_PREFETCH rows to the max arraysize parameter of the library. This is a kind of internal array fetch Oracle provides to speed up the fetching.

Deprecated:
For new developments please use The easy interface
Parameters:
dbh  I - A database handle
stmt  I - A sql statement
argc  I - Number of arguments in argv
argv  I - Arguments
Returns:
  • A statement handle
  • < 0 on error
Example:
/* $Id: ex4.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */
#include <stdio.h>
#include <stdlib.h>
#include "examples.h"

sqlo_stmt_handle_t open_cursor(sqlo_db_handle_t dbh)
{
  sqlo_stmt_handle_t sth;
  int argc = 0;
  const char * argv[1];
  
  argv[argc++] = "1000";

  if ( 0 > (sth = (sqlo_open(dbh, "SELECT ENAME, SAL FROM EMP WHERE SAL >= :1", 
                             argc, argv)))) {
    error_exit(dbh, "sqlo_open");
  }
  return sth;
}

/* $Id: ex4.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */

See also:
sqlo_open2, sqlo_fetch, sqlo_values, sqlo_close
Examples:
ex4.c.

int sqlo_open2 sqlo_stmt_handle_t   sthp,
sqlo_db_handle_t    dbh,
const char *    stmt,
int    argc,
const char **    argv
 

Open a new cursor.

This function opens a new cursor for a query statement. Use this function if your bind variables are all strings. If you need native datatype support, use The advanced interface.

If the stmt is a SELECT statement, the function sets the attribute OCI_ATTR_PREFETCH rows to the max arraysize parameter of the library. This is a kind of internal array fetch Oracle provides to speed up the fetching.

Attention:
You have to init the passed statement handle with SQLO_STH_INIT. This is required escpecially in non-blocking mode.
Parameters:
sthp  I/O - Returns the new sth in *sthp.
dbh  I - A database handle
stmt  I - A sql statement
argc  I - Number of arguments in argv
argv  I - Arguments
Returns:
  • SQLO_SUCCESS
  • SQLO_STILL_EXECUTING in non-blocking mode
  • < 0 on error
Example:
/* $Id: ex5.c,v 1.5 2002/08/24 12:54:47 kpoitschke Exp $ */
#include <stdio.h>
#include <stdlib.h>
#include "examples.h"

sqlo_stmt_handle_t open_cursor2(sqlo_db_handle_t dbh, double min_income)
{
  sqlo_stmt_handle_t sth = SQLO_STH_INIT;
  int argc = 0;
  const char * argv[1];
  char hlp[64];

  sprintf(hlp, "%f", min_income);

  argv[argc++] = hlp;

  if ( 0 > (sqlo_open2(&sth, dbh, 
                       "SELECT ENAME, SAL FROM EMP WHERE SAL >= :1", 
                       argc, argv))) {
    error_exit(dbh, "sqlo_open");
  }

  return sth;
}
/* $Id: ex5.c,v 1.5 2002/08/24 12:54:47 kpoitschke Exp $ */
See also:
sqlo_fetch, sqlo_values, sqlo_close
Since:
Version 2.2
Examples:
ex5.c, and ex6.c.

int sqlo_prows sqlo_stmt_handle_t    sth
 

Return the number of processed rows by this statement.

Parameters:
sth  I - A statement handle
Returns:
  • Number of processed rows
  • 0 if it is not a dml statement
  • < 0 on error
Examples:
ex12.c.

int sqlo_reopen sqlo_stmt_handle_t    sth,
int    argc,
const char **    argv
 

Reopens a already used cursor.

This function reopens an already used cursor with new bind variables. Reopening cursors improve the speed, because no new parse is necessary.

Parameters:
sth  I - The sth you want to rebind.
argc  I - Number of arguments in argv
argv  I - Arguments
Returns:
  • SQLO_SUCCESS
  • SQLO_STILL_EXECUTING in non-blocking mode
  • < 0 on error
Example:
/* $Id: ex6.c,v 1.5 2002/08/24 12:54:47 kpoitschke Exp $ */
#include <stdio.h>
#include <stdlib.h>
#include "examples.h"

sqlo_stmt_handle_t reopen_cursor(sqlo_db_handle_t dbh, double min_income)
{
  static sqlo_stmt_handle_t sth = SQLO_STH_INIT;
  int argc = 0;
  const char * argv[1];
  char hlp[64];

  sprintf(hlp, "%f", min_income);

  argv[argc++] = hlp;

  if (SQLO_STH_INIT == sth) {
    /* first time, open a new cursor */
    if ( 0 > (sqlo_open2(&sth, dbh, 
                         "SELECT ENAME, SAL FROM EMP WHERE SAL >= :1", 
                         argc, argv))) {
      error_exit(dbh, "sqlo_open");
    }
  } else {
    /* next time, bind again with new variables */
    if ( 0 > sqlo_reopen(sth, argc, argv))
      error_exit(dbh, "sqlo_reopen");
  }

  return sth;
}
/* $Id: ex6.c,v 1.5 2002/08/24 12:54:47 kpoitschke Exp $ */
See also:
sqlo_open2, sqlo_fetch, sqlo_values, sqlo_close
Examples:
ex6.c.

int sqlo_run sqlo_db_handle_t    dbh,
const char *    stmt,
int    argc,
const char **    argv
 

Run a simple sql statements with parameters.

Like The easy interface, but with bind parameters. This is basically the same as calling The easy interface followed by The easy interface and The easy interface.

Parameters:
dbh  I - A database handle
stmt  I - A sql statement (non-query).
argc  I - Number of arguments in argv.
argv  I - The arguments
Returns:
  • The number of processed rows.
  • SQLO_STILL_EXECUTING in non-blocking mode.
  • < 0 on error.
Example:
/* $Id: ex3.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */
#include <stdio.h>
#include <stdlib.h>
#include "examples.h"

int update_manager(sqlo_db_handle_t dbh)
{
  const char * argv[2];
  int stat;

  argv[0] = "0.5";
  argv[1] = "MANAGER";

  stat = sqlo_run(dbh, "UPDATE EMP SET SAL = SAL * :1 WHERE JOB = :2",
                 2, argv);
  if (0 > stat) {
    error_exit(dbh, "sqlo_run");
  }
  return stat;
}
/* $Id: ex3.c,v 1.4 2002/08/24 12:54:47 kpoitschke Exp $ */

Examples:
ex3.c.

const unsigned short* sqlo_value_lens sqlo_stmt_handle_t    sth,
int *    num
 

Get the length of the returned values.

Returns the length in number of characters (bytes for non-unicode chars) for a dataset fetched by sqlo_fetch.

Parameters:
sth  I - A statement handle
num  O - A destination where the function can write the size of the returned array (optional).
Returns:
A pointer to an array of unsigned shorts containing the lengths
See also:
sqlo_fetch, sqlo_values, sqlo_open2, sqlo_close.
Examples:
ex7.c.

const char** sqlo_values sqlo_stmt_handle_t    sth,
int *    num,
int    dostrip
 

Get one dataset.

Returns the data for one set of data fetched via The easy interface.

Parameters:
sth  I - A statement handle
num  O - A destination where the function could write the size of the returned array (optional)
dostrip  I - A flag indicating whether trailing blanks should be stripped off (leading blanks in case of numbers).
Returns:
A pointer to an array of strings containing the data values
Example:
/* $Id: ex7.c,v 1.5 2002/08/24 12:54:47 kpoitschke Exp $ */
#include <stdio.h>
#include <stdlib.h>
#include "examples.h"

int do_select(sqlo_db_handle_t dbh, double min_income)
{
  sqlo_stmt_handle_t sth;       /* statement handle */
  int status;                   /* status of sqlo calls */
  unsigned int i,j;                      /* loop counter */
  const char ** v;              /* values */
  const char ** n;              /* column names */
  CONST unsigned int *nl;       /* column name lengths */
  CONST unsigned short *vl;     /* value lengths */
  unsigned int nc;              /* number of columns */


  sth = reopen_cursor(dbh, min_income); /* see example of sqlo_reopen (ex6.c) */

  /* get the output column names */
  n = sqlo_ocol_names(sth, &nc);

  /* get the output column name lengths */
  nl = sqlo_ocol_name_lens(sth, NULL);

  printf("Employees with SAL > %-8.2f:\n", min_income);

  /* print the header */
  for (i = 0; i < nc; ++i)
    printf("%-*s ", nl[i], n[i]);

  printf("\n");
  for (i = 0; i < nc; ++i) {
    for (j = 0; j < nl[i]; ++j) {
      putchar('-');
    }
    putchar('+');
  }
  putchar('\n');

  /* fetch the data */
  while ( SQLO_SUCCESS == (status = (sqlo_fetch(sth, 1)))) {
    
    /* get one record */
    v = sqlo_values(sth, NULL, 1);

    /* get the length of the data items */
    vl = sqlo_value_lens(sth, NULL);

    /* print the column values */
    for (i = 0; i < nc; ++i)
      printf("%-*s ", (vl[i] > nl[i] ? vl[i] : nl[i]), v[i]);

    printf("\n");

  }

  if (0 > status) {
    error_exit(dbh, "sqlo_fetch");
  }

  if ( SQLO_SUCCESS != sqlo_close(sth))
    error_exit(dbh, "sqlo_close");

  return 1;

}

/* $Id: ex7.c,v 1.5 2002/08/24 12:54:47 kpoitschke Exp $ */
See also:
sqlo_fetch, sqlo_value_lens, sqlo_open, sqlo_close.
Examples:
ex17.c, and ex7.c.


Generated on Thu Aug 14 18:02:53 2003 for libsqlora8 by doxygen1.2.18