|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Return values for switch_core_db_exec() and switch_core_db_step() |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Each open database is represented by an instance of the following opaque structure. |
|
||||||||||||||||
|
In the SQL strings input to switch_core_db_prepare(), one or more literals can be replace by parameters "?" or ":AAA" or "$VVV" where AAA is an identifer and VVV is a variable name according to the syntax rules of the TCL programming language. The value of these parameters (also called "host parameter names") can be set using the routines listed below. In every case, the first parameter is a pointer to the sqlite3_stmt structure returned from switch_core_db_prepare(). The second parameter is the index of the parameter. The first parameter as an index of 1. For named parameters (":AAA" or "$VVV") you can use sqlite3_bind_parameter_index() to get the correct index value given the parameters name. If the same named parameter occurs more than once, it is assigned the same index each time. The sqlite3_bind_* routine must be called before switch_core_db_step() after an switch_core_db_prepare() or switch_core_db_reset(). Unbound parameterss are interpreted as NULL. 00144 {
00145 return sqlite3_bind_double(pStmt, i, dValue);
00146 }
|
|
||||||||||||||||
|
In the SQL strings input to switch_core_db_prepare(), one or more literals can be replace by parameters "?" or ":AAA" or "$VVV" where AAA is an identifer and VVV is a variable name according to the syntax rules of the TCL programming language. The value of these parameters (also called "host parameter names") can be set using the routines listed below. In every case, the first parameter is a pointer to the sqlite3_stmt structure returned from switch_core_db_prepare(). The second parameter is the index of the parameter. The first parameter as an index of 1. For named parameters (":AAA" or "$VVV") you can use switch_core_db_bind_parameter_index() to get the correct index value given the parameters name. If the same named parameter occurs more than once, it is assigned the same index each time. The switch_core_db_bind_* routine must be called before switch_core_db_step() after an switch_core_db_prepare() or sqlite3_reset(). Unbound parameterss are interpreted as NULL. 00129 {
00130 return sqlite3_bind_int(pStmt, i, iValue);
00131 }
|
|
||||||||||||||||
|
In the SQL strings input to switch_core_db_prepare(), one or more literals can be replace by parameters "?" or ":AAA" or "$VVV" where AAA is an identifer and VVV is a variable name according to the syntax rules of the TCL programming language. The value of these parameters (also called "host parameter names") can be set using the routines listed below. In every case, the first parameter is a pointer to the sqlite3_stmt structure returned from switch_core_db_prepare(). The second parameter is the index of the parameter. The first parameter as an index of 1. For named parameters (":AAA" or "$VVV") you can use switch_core_db_bind_parameter_index() to get the correct index value given the parameters name. If the same named parameter occurs more than once, it is assigned the same index each time. The switch_core_db_bind_* routine must be called before switch_core_db_step() after an switch_core_db_prepare() or sqlite3_reset(). Unbound parameterss are interpreted as NULL. 00134 {
00135 return sqlite3_bind_int64(pStmt, i, iValue);
00136 }
|
|
||||||||||||||||||||||||
|
In the SQL strings input to switch_core_db_prepare(), one or more literals can be replace by parameters "?" or ":AAA" or "$VVV" where AAA is an identifer and VVV is a variable name according to the syntax rules of the TCL programming language. The value of these parameters (also called "host parameter names") can be set using the routines listed below. In every case, the first parameter is a pointer to the sqlite3_stmt structure returned from switch_core_db_prepare(). The second parameter is the index of the parameter. The first parameter as an index of 1. For named parameters (":AAA" or "$VVV") you can use switch_core_db_bind_parameter_index() to get the correct index value given the parameters name. If the same named parameter occurs more than once, it is assigned the same index each time. The fifth parameter to switch_core_db_bind_blob(), switch_core_db_bind_text(), and switch_core_db_bind_text16() is a destructor used to dispose of the BLOB or text after SQLite has finished with it. If the fifth argument is the special value SQLITE_STATIC, then the library assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data. The switch_core_db_bind_* routine must be called before switch_core_db_step() after an switch_core_db_prepare() or sqlite3_reset(). Unbound parameterss are interpreted as NULL. 00139 {
00140 return sqlite3_bind_text(pStmt, i, zData, nData, xDel);
00141 }
|
|
|
Call this routine to find the number of rows changed by the last statement. 00169 {
00170 return sqlite3_changes(db);
00171 }
|
|
|
A function to close the database. Call this function with a pointer to a structure that was previously returned from switch_core_db_open() and the corresponding database will by closed. All SQL statements prepared using switch_core_db_prepare() must be deallocated using switch_core_db_finalize() before this routine is called. Otherwise, SWITCH_CORE_DB_BUSY is returned and the database connection remains open. 00054 {
00055 return sqlite3_close(db);
00056 }
|
|
|
Return the number of columns in the result set returned by the compiled SQL statement. This routine returns 0 if pStmt is an SQL statement that does not return data (for example an UPDATE). 00069 {
00070 return sqlite3_column_count(pStmt);
00071 }
|
|
||||||||||||
|
The first parameter is a compiled SQL statement. This function returns the column heading for the Nth column of that statement, where N is the second function parameter. The string returned is UTF-8. 00064 {
00065 return sqlite3_column_name(stmt, N);
00066 }
|
|
||||||||||||
|
The next group of routines returns information about the information in a single column of the current result row of a query. In every case the first parameter is a pointer to the SQL statement that is being executed (the switch_core_db_stmt_t* that was returned from switch_core_db_prepare()) and the second argument is the index of the column for which information should be returned. iCol is zero-indexed. The left-most column as an index of 0. If the SQL statement is not currently point to a valid row, or if the the colulmn index is out of range, the result is undefined. These routines attempt to convert the value where appropriate. For example, if the internal representation is FLOAT and a text result is requested, sprintf() is used internally to do the conversion automatically. The following table details the conversions that are applied: Internal Type Requested Type Conversion ------------- -------------- -------------------------- NULL INTEGER Result is 0 NULL FLOAT Result is 0.0 NULL TEXT Result is an empty string NULL BLOB Result is a zero-length BLOB INTEGER FLOAT Convert from integer to float INTEGER TEXT ASCII rendering of the integer INTEGER BLOB Same as for INTEGER->TEXT FLOAT INTEGER Convert from float to integer FLOAT TEXT ASCII rendering of the float FLOAT BLOB Same as FLOAT->TEXT TEXT INTEGER Use atoi() TEXT FLOAT Use atof() TEXT BLOB No change BLOB INTEGER Convert to TEXT then use atoi() BLOB FLOAT Convert to TEXT then use atof() BLOB TEXT Add a "\000" terminator if needed Return the value as UTF-8 text. 00059 {
00060 return sqlite3_column_text(stmt, iCol);
00061 }
|
|
|
Return a pointer to a UTF-8 encoded string describing in english the error condition for the most recent switch_core_db_* API call. The returned string is always terminated by an 0x00 byte. The string "not an error" is returned when the most recent API call was successful. 00074 {
00075 return sqlite3_errmsg(db);
00076 }
|
|
||||||||||||||||||||||||
|
A function to executes one or more statements of SQL. If one or more of the SQL statements are queries, then the callback function specified by the 3rd parameter is invoked once for each row of the query result. This callback should normally return 0. If the callback returns a non-zero value then the query is aborted, all subsequent SQL statements are skipped and the switch_core_db_exec() function returns the SWITCH_CORE_DB_ABORT. The 4th parameter is an arbitrary pointer that is passed to the callback function as its first parameter. The 2nd parameter to the callback function is the number of columns in the query result. The 3rd parameter to the callback is an array of strings holding the values for each column. The 4th parameter to the callback is an array of strings holding the names of each column. The callback function may be NULL, even for queries. A NULL callback is not an error. It just means that no callback will be invoked. If an error occurs while parsing or evaluating the SQL (but not while executing the callback) then an appropriate error message is written into memory obtained from malloc() and *errmsg is made to point to that message. The calling function is responsible for freeing the memory that holds the error message. Use switch_core_db_free() for this. If errmsg==NULL, then no error message is ever written. The return value is is SWITCH_CORE_DB_OK if there are no errors and some other return code if there is an error. The particular return value depends on the type of error. If the query could not be executed because a database file is locked or busy, then this function returns SWITCH_CORE_DB_BUSY. (This behavior can be modified somewhat using the sswitch_core_db_busy_handler() and switch_core_db_busy_timeout() functions below.) 00079 {
00080 int ret = 0;
00081 int sane = 500;
00082 char *err = NULL;
00083
00084 while (--sane > 0) {
00085 ret = sqlite3_exec(db, sql, callback, data, &err);
00086 if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED) {
00087 if (sane > 1) {
00088 switch_safe_free(err);
00089 switch_yield(20000);
00090 continue;
00091 }
00092 } else {
00093 break;
00094 }
00095 }
00096
00097 if (errmsg) {
00098 *errmsg = err;
00099 } else if (err) {
00100 switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "SQL ERR [%s]\n", err);
00101 switch_core_db_free(err);
00102 err = NULL;
00103 }
00104
00105 return ret;
00106 }
|
Here is the call graph for this function:
|
|
This function is called to delete a compiled SQL statement obtained by a previous call to switch_core_db_prepare(). If the statement was executed successfully, or not executed at all, then SWITCH_CORE_DB_OK is returned. If execution of the statement failed then an error code is returned. This routine can be called at any point during the execution of the virtual machine. If the virtual machine has not completed execution when this routine is called, that is like encountering an error or an interrupt. (See switch_core_db_interrupt().) Incomplete updates may be rolled back and transactions cancelled, depending on the circumstances, and the result code returned will be SWITCH_CORE_DB_ABORT. 00109 {
00110 return sqlite3_finalize(pStmt);
00111 }
|
|
|
Call this routine to free the memory that switch_core_db_get_table() allocated. 00164 {
00165 sqlite3_free(z);
00166 }
|
|
|
Call this routine to free the memory that sqlite3_get_table() allocated. 00159 {
00160 sqlite3_free_table(result);
00161 }
|
|
||||||||||||||||||||||||||||
|
This next routine is really just a wrapper around switch_core_db_exec(). Instead of invoking a user-supplied callback for each row of the result, this routine remembers each row of the result in memory obtained from malloc(), then returns all of the result after the query has finished. As an example, suppose the query result where this table: Name | Age ----------------------- Alice | 43 Bob | 28 Cindy | 21 If the 3rd argument were &azResult then after the function returns azResult will contain the following data: azResult[0] = "Name"; azResult[1] = "Age"; azResult[2] = "Alice"; azResult[3] = "43"; azResult[4] = "Bob"; azResult[5] = "28"; azResult[6] = "Cindy"; azResult[7] = "21"; Notice that there is an extra row of data containing the column headers. But the *nrow return value is still 3. *ncolumn is set to 2. In general, the number of values inserted into azResult will be ((*nrow) + 1)*(*ncolumn). After the calling function has finished using the result, it should pass the result data pointer to switch_core_db_free_table() in order to release the memory that was malloc-ed. Because of the way the malloc() happens, the calling function must not try to call free() directly. Only switch_core_db_free_table() is able to release the memory properly and safely. The return value of this routine is the same as from switch_core_db_exec(). 00154 {
00155 return sqlite3_get_table(db, sql, resultp, nrow, ncolumn, errmsg);
00156 }
|
|
|
Each entry in a table has a unique integer key. (The key is the value of the INTEGER PRIMARY KEY column if there is such a column, otherwise the key is generated at random. The unique key is always available as the ROWID, OID, or _ROWID_ column.) The following routine returns the integer key of the most recent insert in the database. This function is similar to the mysql_insert_id() function from MySQL. 00149 {
00150 return sqlite3_last_insert_rowid(db);
00151 }
|
|
||||||||||||
|
Open the database file "filename". The "filename" is UTF-8 encoded. A switch_core_db_t* handle is returned in *Db, even if an error occurs. If the database is opened (or created) successfully, then SWITCH_CORE_DB_OK is returned. Otherwise an error code is returned. The switch_core_db_errmsg() routine can be used to obtain an English language description of the error. If the database file does not exist, then a new database is created. The encoding for the database is UTF-8. Whether or not an error occurs when it is opened, resources associated with the switch_core_db_t* handle should be released by passing it to switch_core_db_close() when it is no longer required. 00049 {
00050 return sqlite3_open(filename, ppDb);
00051 }
|
|
||||||||||||||||||||||||
|
To execute an SQL query, it must first be compiled into a byte-code program using the following routine. The first parameter "db" is an SQLite database handle. The second parameter "zSql" is the statement to be compiled, encoded as UTF-8. If the next parameter, "nBytes", is less than zero, then zSql is read up to the first nul terminator. If "nBytes" is not less than zero, then it is the length of the string zSql in bytes (not characters). *pzTail is made to point to the first byte past the end of the first SQL statement in zSql. This routine only compiles the first statement in zSql, so *pzTail is left pointing to what remains uncompiled. *ppStmt is left pointing to a compiled SQL statement that can be executed using switch_core_db_step(). Or if there is an error, *ppStmt may be set to NULL. If the input text contained no SQL (if the input is and empty string or a comment) then *ppStmt is set to NULL. On success, SWITCH_CORE_DB_OK is returned. Otherwise an error code is returned. 00114 {
00115 return sqlite3_prepare(db, zSql, nBytes, ppStmt, pzTail);
00116 }
|
|
|
The switch_core_db_reset() function is called to reset a compiled SQL statement obtained by a previous call to switch_core_db_prepare() back to it's initial state, ready to be re-executed. Any SQL statement variables that had values bound to them using the switch_core_db_bind_*() API retain their values. 00124 {
00125 return sqlite3_reset(pStmt);
00126 }
|
|
|
After an SQL query has been compiled with a call to either switch_core_db_prepare(), then this function must be called one or more times to execute the statement. The return value will be either SWITCH_CORE_DB_BUSY, SWITCH_CORE_DB_DONE, SWITCH_CORE_DB_ROW, SWITCH_CORE_DB_ERROR, or SWITCH_CORE_DB_MISUSE. SWITCH_CORE_DB_BUSY means that the database engine attempted to open a locked database and there is no busy callback registered. Call switch_core_db_step() again to retry the open. SWITCH_CORE_DB_DONE means that the statement has finished executing successfully. switch_core_db_step() should not be called again on this virtual machine. If the SQL statement being executed returns any data, then SWITCH_CORE_DB_ROW is returned each time a new row of data is ready for processing by the caller. The values may be accessed using the switch_core_db_column_*() functions described below. switch_core_db_step() is called again to retrieve the next row of data. SWITCH_CORE_DB_ERROR means that a run-time error (such as a constraint violation) has occurred. switch_core_db_step() should not be called again on the VM. More information may be found by calling switch_core_db_errmsg(). SWITCH_CORE_DB_MISUSE means that the this routine was called inappropriately. Perhaps it was called on a virtual machine that had already been finalized or on one that had previously returned SWITCH_CORE_DB_ERROR or SWITCH_CORE_DB_DONE. Or it could be the case the the same database connection is being used simulataneously by two or more threads. 00119 {
00120 return sqlite3_step(stmt);
00121 }
|
1.3.9.1