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. |
The query results are also converted to strings.
int sqlo_close | ( | sqlo_stmt_handle_t | sth | ) |
const char* sqlo_command | ( | sqlo_stmt_handle_t | sth | ) |
Return the sql command.
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]
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) |
/* $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 $ */
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.
dbh | A database handle | |
stmt | A sql statement |
/* $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 $ */
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].
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) |
/* $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 $ */
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.
sth | I - A statement handle | |
nrows | I - The number of rows to fetch. |
int sqlo_isopen | ( | sqlo_stmt_handle_t | sth | ) |
Test if a cursor is open.
sth | I - A statement handle. |
int sqlo_ncols | ( | sqlo_stmt_handle_t | sth, | |
int | in | |||
) |
Get the number of bind/select-list variables.
sth | I - A statement handle | |
in | I - 1 returns the number of bind variables, 0 returns the number of select list columns. |
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.
sth | I - A statement handle | |
num | O - A destination where the function can write the size of the returned array. |
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.
sth | I - A statement handle | |
num | O - A destination where the function can write the size of the returned array (optional). |
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.
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. |
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.
dbh | I - A database handle | |
stmt | I - A sql statement | |
argc | I - Number of arguments in argv | |
argv | I - Arguments |
/* $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 $ */
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.
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 |
/* $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 $ */
int sqlo_prows | ( | sqlo_stmt_handle_t | sth | ) |
Return the number of processed rows by this statement.
sth | I - A statement handle |
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.
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) |
/* $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 $ */
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.
sth | I - The sth you want to rebind. | |
argc | I - Number of arguments in argv | |
argv | I - Arguments |
/* $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 $ */
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.
dbh | I - A database handle | |
stmt | I - A sql statement (non-query). | |
argc | I - Number of arguments in argv. | |
argv | I - The arguments |
/* $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 $ */
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.
sth | I - A statement handle | |
num | O - A destination where the function can write the size of the returned array (optional). |
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.
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). |
/* $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 $ */