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.
int sqlo_ocol_names2 (sqlo_stmt_handle_t sth, int *num, const char ***ocol_names)
 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.
int sqlo_query_result (sqlo_stmt_handle_t sth, unsigned int *ncols, char ***values, unsigned short **value_lens, char ***colnames, unsigned int **colname_lens)
 Fetch the next row from the resultset and return the columns.
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, ex20.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 221 2002-08-24 12:54:47Z kpoitschke $ */
#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 221 2002-08-24 12:54:47Z kpoitschke $ */
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 221 2002-08-24 12:54:47Z kpoitschke $ */
#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 221 2002-08-24 12:54:47Z kpoitschke $ */

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 221 2002-08-24 12:54:47Z kpoitschke $ */
#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 221 2002-08-24 12:54:47Z kpoitschke $ */
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 sqlo_values to get the data.

Attention:
nrows must be 1 for cursors opened with sqlo_open 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
  • SQLO_SUCCESS_WITH_INFO
  • < 0 on error
SQLO_SUCCESS_WITH_INFO is returned for the following oracle errors (from the Oracle documentation of OCIStmtFetch):
  • ORA-24344 Success with compilation error
  • ORA-23445 A truncation or null fetch error occurred
  • ORA-23447 Warning of a NULL column in an aggregate function
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.

Deprecated:
For new developments please use sqlo_ocol_names2
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, NULL on error
See also:
sqlo_fetch, sqlo_values, sqlo_open2, sqlo_close, sqlo_ocol_name_lens.

int sqlo_ocol_names2 ( sqlo_stmt_handle_t  sth,
int *  num,
const char ***  ocol_names 
)

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
ocol_names O - The address of a char ** which receives the array of ocol_names. array.
Returns:
SQLO_SUCCESS or <0 on error.
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. In case of an error, the allocated resources are freed. You don't have to call sqlo_close for an invalid sth.

Deprecated:
For new developments please use sqlo_open2
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 221 2002-08-24 12:54:47Z kpoitschke $ */
#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 221 2002-08-24 12:54:47Z kpoitschke $ */

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 sqlo_prepare

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. In case of an error, the allocated resources are freed. You don't have to call sqlo_close for an invalid sth.

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 221 2002-08-24 12:54:47Z kpoitschke $ */
#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 221 2002-08-24 12:54:47Z kpoitschke $ */
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_query_result ( sqlo_stmt_handle_t  sth,
unsigned int *  ncols,
char ***  values,
unsigned short **  value_lens,
char ***  colnames,
unsigned int **  colname_lens 
)

Fetch the next row from the resultset and return the columns.

Parameters:
sth I - A statement handle.
ncols O - The number of ouput columns
values O - The column values array
value_lens O - The value lengths array (leave NULL if you are not interested in)
colnames O - The column names array (leave NULL if you are not interested in)
colname_lens O - The column name lengths array (leave NULL if you are not interested in)
Returns:
  • SQLO_SUCCESS
  • SQLO_INVALID_STMT_TYPE if sth is not a query
  • OCI status code
Example:
/* $Id: ex20.c 301 2005-01-08 17:28:49Z kpoitschke $ */
#include <stdio.h>
#include <stdlib.h>
#include "examples.h"

/* Select employees with salary > min_income using sqlo_query_result */

int ex20(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 */
  char ** v;                    /* values */
  char ** col_names;           /* column names */
  unsigned int *nl;            /* column name lengths */
  unsigned short *vl;           /* value lengths */
  unsigned int nc;              /* number of columns */
  int nrows;

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


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


  nrows = 0;
  /* fetch the data */
  while ( SQLO_SUCCESS == (status = (sqlo_query_result(sth, /* statement */
                               &nc, /* number of columns */
                               &v,  /* values */
                               &vl, /* value lengths */
                               &col_names, /* column names */
                               &nl   /* column name lengths */
                               )))) {
    
    if ( nrows == 0 )
      {
    /* print the header */
    for (i = 0; i < nc; ++i)
      printf("%-*s ", nl[i]+4, col_names[i]);
    printf("\n");
    for (i = 0; i < nc; ++i) {
      for (j = 0; j < nl[i]+4; ++j) {
        putchar('-');
      }
      putchar('+');
    }
    putchar('\n');
      }
    
    /* print the column values */
    for (i = 0; i < nc; ++i)
      printf("%-*s ", (vl[i] > nl[i] ? vl[i] : nl[i])+4, v[i]);
    
    printf("\n");

    ++nrows;
  }

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

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

  return 1;

}

/* $Id: ex20.c 301 2005-01-08 17:28:49Z kpoitschke $ */
   
Examples:
ex20.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 221 2002-08-24 12:54:47Z kpoitschke $ */
#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 221 2002-08-24 12:54:47Z kpoitschke $ */
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 sqlo_exec, but with bind parameters. This is basically the same as calling sqlo_open followed by sqlo_fetch and sqlo_close.

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 221 2002-08-24 12:54:47Z kpoitschke $ */
#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 221 2002-08-24 12:54:47Z kpoitschke $ */

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 sqlo_fetch.

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 287 2004-07-30 16:28:21Z kpoitschke $ */
#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 ** col_names;      /* 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 */
  status = sqlo_ocol_names2(sth, &nc, &col_names);

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

  /* 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], col_names[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 287 2004-07-30 16:28:21Z kpoitschke $ */
See also:
sqlo_fetch, sqlo_value_lens, sqlo_open, sqlo_close.
Examples:
ex17.c, and ex7.c.


Generated on Mon May 21 13:42:17 2007 for libsqlora8 by  doxygen 1.4.7