if (!$ret) {
print "\ttable already exists<br>\n";
} else {
print "\ttable created<br>\n";
}
print "Inserting values:<br>\n";
sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (1,'michael',32)");
sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (2,'bob',27)");
sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (3,'martin',12)");
/*
* Create a query
*/
print "SQL query:<br>\n";
$query = sqlite3_query($db, "SELECT * FROM test ORDER BY age DESC");
if (!$query) {
die(sqlite3_error($db));
}
/*
* sqlite3_fetch_array() returns an associative array
* for each row in the result set. Key indexes are
* the columns names.
*
*/
while ($row = sqlite3_fetch_array($query)) {
printf("\t%-20s %u<br>\n", $row['name'], $row['age']);
}
/*
* do not forget to release all handles !
*
*/
print "Closing:<br>\n";
function DBexecute($query, $skip_error_messages = 0)
{
global $DB;
//COpt::savesqlrequest($query);
$result = false;
if (isset($DB['DB']) && !empty($DB['DB'])) {
$DB['EXECUTE_COUNT']++;
// WRONG FOR ORACLE!!
//SDI('SQL Exec: '.$query);
switch ($DB['TYPE']) {
case 'MYSQL':
$result = mysql_query($query, $DB['DB']);
if (!$result) {
error('Error in query [' . $query . '] [' . mysql_error() . ']');
}
break;
case 'POSTGRESQL':
if (!($result = pg_query($DB['DB'], $query))) {
error('Error in query [' . $query . '] [' . pg_last_error() . ']');
}
break;
case 'ORACLE':
$stid = OCIParse($DB['DB'], $query);
if (!$stid) {
$e = @ocierror();
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
}
$result = @OCIExecute($stid, $DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS);
if (!$result) {
$e = ocierror($stid);
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
} else {
$result = $stid;
}
break;
case 'SQLITE3':
if (!$DB['TRANSACTIONS']) {
lock_db_access();
}
$result = sqlite3_exec($DB['DB'], $query);
if (!$result) {
error('Error in query [' . $query . '] [' . sqlite3_error($DB['DB']) . ']');
}
if (!$DB['TRANSACTIONS']) {
unlock_db_access();
}
break;
}
if ($DB['TRANSACTIONS'] && !$result) {
$DB['TRANSACTION_STATE'] &= $result;
// SDI($query);
// SDI($DB['TRANSACTION_STATE']);
}
}
return $result;
}
/**
* Gets the DBMS' native error code produced by the last query
*
* @return mixed the DBMS' error code. A DB_Error object on failure.
*/
function errorNative()
{
return sqlite3_error($this->connection);
}
function error_str($errno = null)
{
return sqlite3_error($this->db);
}
function sqlite3_command($query, $db)
{
$han = sqlite3_open($db);
$aa = sqlite3_query($han, $query);
if (!$aa) {
echo "SQLITE3 QUERY {$query} returned error: " . sqlite3_error($han);
}
return $aa;
}
* create a SQLite3 handle.
*
* Note: in-memory database are created by the magic keyword ":memory:"
*
*/
$db = sqlite3_open(":memory:");
if (!$db) {
die("Could not create in-memory database..");
}
/*
* Define a new SQL function: sha1, which takes
* only one argument.
* SQLite3 library will call the PHP function get_sha1()
* to get the result.
*
*/
if (!sqlite3_create_function($db, "sha1", 1, "get_sha1")) {
die("sqlite3_create_function() failed.");
}
$res = sqlite3_query($db, "select sha1('my password')");
if (!$res) {
die(sqlite3_error($db));
}
$row = sqlite3_fetch_array($res);
if (!$row) {
echo "error: " . sqlite3_error($db);
} else {
var_dump($row);
}
sqlite3_query_close($res);
sqlite3_close($db);
function DBexecute($query, $skip_error_messages = 0)
{
global $DB;
$result = false;
$time_start = microtime(true);
if (isset($DB['DB']) && !empty($DB['DB'])) {
$DB['EXECUTE_COUNT']++;
//SDI('SQL xec: '.$query);
switch ($DB['TYPE']) {
case 'MYSQL':
$result = mysql_query($query, $DB['DB']);
if (!$result) {
error('Error in query [' . $query . '] [' . mysql_error() . ']');
}
break;
case 'POSTGRESQL':
$result = (bool) pg_query($DB['DB'], $query);
if (!$result) {
error('Error in query [' . $query . '] [' . pg_last_error() . ']');
}
break;
case 'ORACLE':
$result = OCIParse($DB['DB'], $query);
if (!$result) {
$e = @ocierror();
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
} else {
if (!@OCIExecute($result, $DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS)) {
$e = ocierror($result);
error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']');
} else {
/* It should be here. The function must return boolean */
$result = true;
}
}
break;
case 'IBM_DB2':
$options = array();
if (!($result = db2_prepare($DB['DB'], $query))) {
$e = @db2_stmt_errormsg($result);
error('SQL error [' . $query . '] in [' . $e . ']');
} else {
if (true !== @db2_execute($result)) {
$e = @db2_stmt_errormsg($result);
error('SQL error [' . $query . '] in [' . $e . ']');
} else {
/* It should be here. The function must return boolean */
$result = true;
}
}
break;
case 'SQLITE3':
if (!$DB['TRANSACTIONS']) {
lock_db_access();
}
$result = sqlite3_exec($DB['DB'], $query);
if (!$result) {
error('Error in query [' . $query . '] [' . sqlite3_error($DB['DB']) . ']');
}
if (!$DB['TRANSACTIONS']) {
unlock_db_access();
}
break;
}
if ($DB['TRANSACTIONS'] && !$result) {
$DB['TRANSACTION_STATE'] &= $result;
}
}
COpt::savesqlrequest(microtime(true) - $time_start, $query);
return (bool) $result;
}
public function lastErrorMsg()
{
return sqlite3_error($this->conn);
}
function sql($command, $type = "BLANK", $override = false)
{
$this->debug("Running SQL Command {$command}", 4);
// Ensure we're connected to the database.
if ($this->dbhandle == null) {
if (!($this->dbhandle = $this->sql_database_connect())) {
$this->debug('SEVERE: Unable to connect to database.', 1);
return false;
}
}
// Check for non-portable stuff.
if ($override != true) {
$result = $this->sql_check($command);
// sql_check returns a sanitized SQL command, or false if error.
if ($result == false) {
return false;
}
} else {
$result = $command;
}
// Check the TYPE
switch ($type) {
case "BLANK":
$this->debug("WARNING: Please provide the type of the query for SQL command '{$result}'. Defaulting to BOTH", 2);
$type = "BOTH";
break;
case "ASSOC":
case "NUM":
case "BOTH":
case "NONE":
break;
default:
$this->errstr = "SEVERE: Uknown Query type '{$type}' for query '{$result}'";
$this->debug($this->errstr, 1);
return false;
}
// Actually do the SQL
$sqlresult = null;
switch ($this->db) {
case "mysql":
$res = mysql_query($result, $this->dbhandle);
if (!$res) {
$this->errstr = "MySQL Error: " . mysql_error() . " with query {$result}";
$this->debug($this->errstr, 1);
return false;
}
// Loop through the returned result set, loading it into the array to return
// to the caller.
$this->numrows = mysql_num_rows($res);
// Return the correct type.
if ($type == "NONE") {
return true;
}
for ($i = 0; $i <= $this->numrows; $i++) {
if ($type == "NUM") {
$sqlresult[$i] = mysql_fetch_array($res, MYSQL_NUM);
} elseif ($type = "ASSOC") {
$sqlresult[$i] = mysql_fetch_array($res, MYSQL_ASSOC);
} else {
$sqlresult[$i] = mysql_fetch_array($res, MYSQL_BOTH);
}
}
return $sqlresult;
case "sqlite":
$res = $this->dbhandle->query($result);
if (!$res) {
$this->errstr = "SQLite3 Error: '" . $this->dbhandle->lastErrorMsg . "' with query '{$result}'";
$this->debug($this->errstr, 1);
return false;
}
// Loop through the returned result set, loading it into the array to return
// to the caller.
// Return the correct type.
if ($type == "NONE") {
return true;
}
$i = 0;
if ($type == "NUM") {
while ($sqlresult[$i++] = $res->fetchArray(SQLITE3_NUM)) {
}
} elseif ($type = "ASSOC") {
while ($sqlresult[$i++] = $res->fetchArray(SQLITE3_ASSOC)) {
}
} else {
while ($sqlresult[$i++] = $res->fetchArray(SQLITE3_BOTH)) {
}
}
$res->finalize();
$this->numrows = $i;
return $sqlresult;
case "sqlite3":
// Init the sqlite3 hack variables.
global $sql3holderAssoc;
global $sql3holderNum;
global $sql3holderRowNbr;
$sql3holderAssoc = null;
$sql3holderNum = null;
$sql3holderRowNbr = 0;
// If no result is required, just run the query and return the status.
if ($type == "NONE") {
$res = sqlite3_exec($this->dbhandle, $result);
if (!$res) {
$this->errstr = sqlite3_error($this->dbhandle);
return false;
} else {
$this->errstr = null;
return $res;
}
}
// This next line uses the sqlite3_hack function, below, to load
// up the $sql3holder variables.
$res = sqlite3_exec($this->dbhandle, $result, "sqlite3_hack");
$this->numrows = $sql3holderRowNbr;
$this->debug("SQL returned {$sql3holderRowNbr} Rows", 4);
if ($sql3holderRowNbr == 0) {
return true;
}
if ($type == "NUM") {
return $sql3holderNum;
} elseif ($type == "ASSOC") {
return $sql3holderAssoc;
} else {
return $sql3holderNum + $sql3holderAssoc;
}
default:
$this->debug("SEVERE: Database type '" . $this->db . "' NOT SUPPORTED (sql)", 0);
return false;
}
}
