int open_connection()
{
SQLRETURN ret;
/* Allocate Environment handle and register version */
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &ODenvi);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
db_d_append_error("SQLAllocHandle()");
db_d_report_error();
return DB_FAILED;
}
ret =
SQLSetEnvAttr(ODenvi, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
db_d_append_error("SQLSetEnvAttr()");
db_d_report_error();
SQLFreeHandle(SQL_HANDLE_ENV, ODenvi);
return DB_FAILED;
}
/* Allocate connection handle */
ret = SQLAllocHandle(SQL_HANDLE_DBC, ODenvi, &ODconn);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
db_d_append_error("SQLAllocHandle()");
db_d_report_error();
SQLFreeHandle(SQL_HANDLE_ENV, ODenvi);
return DB_FAILED;
}
/* Set timeout */
SQLSetConnectAttr(ODconn, SQL_LOGIN_TIMEOUT, (SQLPOINTER *) 5, 0);
return DB_OK;
}
int db__driver_open_select_cursor(dbString * sel, dbCursor * dbc, int mode)
{
cursor *c;
dbTable *table;
char *str;
/* allocate cursor */
c = alloc_cursor();
if (c == NULL)
return DB_FAILED;
db_set_cursor_mode(dbc, mode);
db_set_cursor_type_readonly(dbc);
/* \ must be escaped, see explanation in
* db_driver_execute_immediate() */
str = G_str_replace(db_get_string(sel), "\\", "\\\\");
G_debug(3, "Escaped SQL: %s", str);
if (mysql_query(connection, str) != 0) {
db_d_append_error("%s\n%s\n%s",
_("Unable to select data:"),
db_get_string(sel),
mysql_error(connection));
if (str)
G_free(str);
db_d_report_error();
return DB_FAILED;
}
if (str)
G_free(str);
c->res = mysql_store_result(connection);
if (c->res == NULL) {
db_d_append_error("%s\n%s",
db_get_string(sel),
mysql_error(connection));
db_d_report_error();
return DB_FAILED;
}
if (describe_table(c->res, &table, c) == DB_FAILED) {
db_d_append_error(_("Unable to describe table."));
db_d_report_error();
mysql_free_result(c->res);
return DB_FAILED;
}
c->nrows = (int)mysql_num_rows(c->res);
/* record table with dbCursor */
db_set_cursor_table(dbc, table);
/* set dbCursor's token for my cursor */
db_set_cursor_token(dbc, c->token);
return DB_OK;
}
int db__driver_get_num_rows(dbCursor * cn)
{
cursor *c;
dbToken token;
int row, ret;
/* get cursor token */
token = db_get_cursor_token(cn);
/* get the cursor by its token */
if (!(c = (cursor *) db_find_token(token))) {
db_d_append_error(_("Cursor not found"));
db_d_report_error();
return DB_FAILED;
}
if (c->nrows > -1) {
return (c->nrows);
}
sqlite3_reset(c->statement);
c->nrows = 0;
while ((ret = sqlite3_step(c->statement)) == SQLITE_ROW) {
c->nrows++;
}
/* get real result code */
ret = sqlite3_reset(c->statement);
if (ret != SQLITE_OK) {
db_d_append_error("%s\n%s",
_("Unable to get number of rows:"),
(char *)sqlite3_errmsg(sqlite));
db_d_report_error();
return DB_FAILED;
}
/* Reset cursor position */
row = -1;
if (c->row > -1) {
while (sqlite3_step(c->statement) == SQLITE_ROW) {
if (row == c->row)
break;
row++;
}
}
return (c->nrows);
}
int db__driver_execute_immediate(dbString * sql)
{
char *s, msg[OD_MSG];
cursor *c;
SQLRETURN ret;
SQLINTEGER err;
s = db_get_string(sql);
/* allocate cursor */
c = alloc_cursor();
if (c == NULL)
return DB_FAILED;
ret = SQLExecDirect(c->stmt, s, SQL_NTS);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO)) {
SQLGetDiagRec(SQL_HANDLE_STMT, c->stmt, 1, NULL, &err, msg,
sizeof(msg), NULL);
db_d_append_error("SQLExecDirect():\n%s\n%s (%d)\n", s, msg,
(int)err);
db_d_report_error();
return DB_FAILED;
}
free_cursor(c);
return DB_OK;
}
int db__driver_execute_immediate(dbString * sql)
{
char *str;
/* In addition to standard escape character ' (apostrophe)
* MySQL supports also \ (backslash). Because this is not SQL
* standard, GRASS modules cannot escape all \ in the text
* because other drivers do not support this feature.
* For example, if a text contains string \' GRASS modules
* escape ' by another ' and the string passed to the driver is \''
* MySQL converts \' to ' but second ' remains not escaped and
* result is error.
* Because of this, all occurencies of \ in sql must be
* escaped by \ */
str = G_str_replace(db_get_string(sql), "\\", "\\\\");
G_debug(3, "Escaped SQL: %s", str);
if (mysql_query(connection, str) != 0) {
db_d_append_error("%s\n%s\n%s",
_("Unable to execute:"),
str,
mysql_error(connection));
db_d_report_error();
if (str)
G_free(str);
return DB_FAILED;
}
if (str)
G_free(str);
return DB_OK;
}
/*
\brief Parse connection string in form:
1) 'database_name'
2) 'host=xx,port=xx,dbname=xx'
\returns DB_OK on success
\return DB_FAILED on failure
*/
int parse_conn(const char *str, PGCONN * pgconn)
{
int i;
char **tokens, delm[2];
/* reset */
G_zero(pgconn, sizeof(PGCONN));
G_debug(3, "parse_conn: '%s'", str);
if (strchr(str, '=') == NULL) { /* db name only */
pgconn->dbname = G_store(str);
}
else {
delm[0] = ',';
delm[1] = '\0';
tokens = G_tokenize(str, delm);
i = 0;
while (tokens[i]) {
G_chop(tokens[i]);
G_debug(3, "token %d : %s", i, tokens[i]);
if (strncmp(tokens[i], "host", 4) == 0)
pgconn->host = G_store(tokens[i] + 5);
else if (strncmp(tokens[i], "port", 4) == 0)
pgconn->port = G_store(tokens[i] + 5);
else if (strncmp(tokens[i], "options", 7) == 0)
pgconn->options = G_store(tokens[i] + 8);
else if (strncmp(tokens[i], "tty", 3) == 0)
pgconn->tty = G_store(tokens[i] + 4);
else if (strncmp(tokens[i], "dbname", 6) == 0)
pgconn->dbname = G_store(tokens[i] + 7);
else if (strncmp(tokens[i], "user", 4) == 0)
G_warning(_("'user' in database definition is not supported, use db.login"));
/* pgconn->user = G_store ( tokens[i] + 5 ); */
else if (strncmp(tokens[i], "password", 8) == 0)
/* pgconn->password = G_store ( tokens[i] + 9 ); */
G_warning(_("'password' in database definition is not supported, use db.login"));
else if (strncmp(tokens[i], "schema", 6) == 0)
pgconn->schema = G_store(tokens[i] + 7);
else {
db_d_append_error("%s %s",
_("Unknown option in database definition "
"for PostgreSQL: "),
tokens[i]);
return DB_FAILED;
}
i++;
}
G_free_tokens(tokens);
}
return DB_OK;
}
int db__driver_commit_transaction(void)
{
G_debug(2, "mysql: COMMIT");
if (mysql_query(connection, "COMMIT") != 0) {
db_d_append_error("%s %s",
_("Unable to commit transaction:"),
mysql_error(connection));
db_d_report_error();
return DB_FAILED;
}
return DB_OK;
}
int db__driver_begin_transaction(void)
{
G_debug(2, "mysql: START TRANSACTION");
if (mysql_query(connection, "START TRANSACTION") != 0) {
db_d_append_error("%s %s",
_("Unable to start transaction:"),
mysql_error(connection));
db_d_report_error();
return DB_FAILED;
}
return DB_OK;
}
int db__driver_create_table(dbTable * table)
{
dbString sql;
int ret;
G_debug(3, "db__driver_create_table()");
db_init_string(&sql);
db_table_to_sql(table, &sql);
G_debug(3, " SQL: %s", db_get_string(&sql));
ret = execute(db_get_string(&sql), NULL);
if (ret == DB_FAILED) {
db_d_append_error(_("Unable to create table"));
db_d_report_error();
return DB_FAILED;
}
return DB_OK;
}
int db__driver_open_database(dbHandle * handle)
{
char buf[500];
const char *name, *schema, *user, *password;
dbConnection connection;
PGCONN pgconn;
PGresult *res;
int row;
db_get_connection(&connection);
name = db_get_handle_dbname(handle);
/* if name is empty use connection.databaseName */
if (strlen(name) == 0)
name = connection.databaseName;
G_debug(3,
"db_driver_open_database(): driver=pg database definition = '%s'",
name);
if (parse_conn(name, &pgconn) == DB_FAILED) {
db_d_report_error();
return DB_FAILED;
}
G_debug(3,
"db_driver_open_database(): host = %s, port = %s, options = %s, tty = %s, "
"dbname = %s, user = %s, password = %s, "
"schema = %s", pgconn.host, pgconn.port, pgconn.options,
pgconn.tty, pgconn.dbname, pgconn.user, pgconn.password,
pgconn.schema);
db_get_login("pg", name, &user, &password);
pg_conn = PQsetdbLogin(pgconn.host, pgconn.port, pgconn.options, pgconn.tty,
pgconn.dbname, user, password);
if (PQstatus(pg_conn) == CONNECTION_BAD) {
db_d_append_error("%s\n%s",
_("Connection failed."),
PQerrorMessage(pg_conn));
db_d_report_error();
PQfinish(pg_conn);
return DB_FAILED;
}
/* set schema */
schema = db_get_handle_dbschema(handle);
/* Cannot use default schema because link to table can point to
different database */
/*
if ( schema )
schema = connection.schemaName;
*/
if (pgconn.schema) {
schema = pgconn.schema;
}
/* set path to the schema */
if (schema && strlen(schema) > 0) {
sprintf(buf, "set search_path to %s", schema);
res = PQexec(pg_conn, buf);
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
db_d_append_error("%s %s",
_("Unable to set schema:"),
schema);
db_d_report_error();
PQclear(res);
return DB_FAILED;
}
}
/* read internal codes */
res = PQexec(pg_conn,
"select oid, typname from pg_type where typname in ( "
"'bit', 'int2', 'int4', 'int8', 'serial', 'oid', "
"'float4', 'float8', 'numeric', "
"'char', 'bpchar', 'varchar', 'text', "
"'time', 'date', 'timestamp', "
"'bool', 'geometry', 'topogeometry') order by oid");
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) {
db_d_append_error(_("Unable to select data types"));
db_d_report_error();
PQclear(res);
return DB_FAILED;
}
pg_ntypes = PQntuples(res);
pg_types = G_realloc(pg_types, 2 * pg_ntypes * sizeof(int));
for (row = 0; row < pg_ntypes; row++) {
int pgtype, type;
pgtype = atoi(PQgetvalue(res, row, 0));
pg_types[row][0] = pgtype;
G_debug(3, "row = %d value = %s", row, PQgetvalue(res, row, 1));
if (strcmp(PQgetvalue(res, row, 1), "bit") == 0)
type = PG_TYPE_BIT;
else if (strcmp(PQgetvalue(res, row, 1), "int2") == 0)
type = PG_TYPE_INT2;
else if (strcmp(PQgetvalue(res, row, 1), "int4") == 0)
type = PG_TYPE_INT4;
else if (strcmp(PQgetvalue(res, row, 1), "int8") == 0)
type = PG_TYPE_INT8;
else if (strcmp(PQgetvalue(res, row, 1), "serial") == 0)
type = PG_TYPE_SERIAL;
else if (strcmp(PQgetvalue(res, row, 1), "oid") == 0)
type = PG_TYPE_OID;
else if (strcmp(PQgetvalue(res, row, 1), "float4") == 0)
type = PG_TYPE_FLOAT4;
else if (strcmp(PQgetvalue(res, row, 1), "float8") == 0)
type = PG_TYPE_FLOAT8;
else if (strcmp(PQgetvalue(res, row, 1), "numeric") == 0)
type = PG_TYPE_NUMERIC;
else if (strcmp(PQgetvalue(res, row, 1), "char") == 0)
type = PG_TYPE_CHAR;
else if (strcmp(PQgetvalue(res, row, 1), "bpchar") == 0)
type = PG_TYPE_BPCHAR;
else if (strcmp(PQgetvalue(res, row, 1), "varchar") == 0)
type = PG_TYPE_VARCHAR;
else if (strcmp(PQgetvalue(res, row, 1), "text") == 0)
type = PG_TYPE_TEXT;
else if (strcmp(PQgetvalue(res, row, 1), "date") == 0)
type = PG_TYPE_DATE;
else if (strcmp(PQgetvalue(res, row, 1), "time") == 0)
type = PG_TYPE_TIME;
else if (strcmp(PQgetvalue(res, row, 1), "timestamp") == 0)
type = PG_TYPE_TIMESTAMP;
else if (strcmp(PQgetvalue(res, row, 1), "bool") == 0)
type = PG_TYPE_BOOL;
else if (strcmp(PQgetvalue(res, row, 1), "geometry") == 0)
type = PG_TYPE_POSTGIS_GEOM;
else if (strcmp(PQgetvalue(res, row, 1), "topogeometry") == 0)
type = PG_TYPE_POSTGIS_TOPOGEOM;
else
type = PG_TYPE_UNKNOWN;
G_debug(3, "db_driver_open_database(): pgtype = %d, name = %s -> type = %d", pgtype,
PQgetvalue(res, row, 1), type);
pg_types[row][1] = type;
}
/* print notice messages only on verbose level */
PQsetNoticeProcessor(pg_conn, notice_processor, NULL);
PQclear(res);
return DB_OK;
}
/* create or drop database */
int create_delete_db(dbHandle *handle, int create)
{
dbString stmt;
const char *template_db, *name, *user, *password;
PGCONN pgconn;
PGresult *res;
db_init_string(&stmt);
template_db = "template1";
name = db_get_handle_dbname(handle); /* database to create */
if (parse_conn(template_db, &pgconn) == DB_FAILED) {
db_d_report_error();
return DB_FAILED;
}
G_debug(3,
"db_driver_create_database(): host = %s, port = %s, options = %s, tty = %s, "
"dbname = %s, user = %s, password = %s, "
"schema = %s", pgconn.host, pgconn.port, pgconn.options,
pgconn.tty, pgconn.dbname, pgconn.user, pgconn.password,
pgconn.schema);
db_get_login("pg", template_db, &user, &password);
pg_conn = PQsetdbLogin(pgconn.host, pgconn.port, pgconn.options, pgconn.tty,
pgconn.dbname, user, password);
if (PQstatus(pg_conn) == CONNECTION_BAD) {
db_d_append_error(_("Connection failed."));
db_d_append_error("\n");
db_d_append_error(PQerrorMessage(pg_conn));
db_d_report_error();
PQfinish(pg_conn);
return DB_FAILED;
}
/* create new database */
if (create)
db_set_string(&stmt, "CREATE DATABASE ");
else
db_set_string(&stmt, "DROP DATABASE ");
db_append_string(&stmt, name);
res = PQexec(pg_conn,
db_get_string(&stmt));
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
if (create)
db_d_append_error(_("Unable to create database <%s>"), name);
else
db_d_append_error(_("Unable to drop database <%s>"), name);
db_d_append_error("\n");
db_d_append_error(PQerrorMessage(pg_conn));
db_d_report_error();
PQclear(res);
PQfinish(pg_conn);
return DB_FAILED;
}
PQclear(res);
PQfinish(pg_conn);
return DB_OK;
}
int db__driver_list_tables(dbString ** tlist, int *tcount, int system)
{
int i, j, nrows, trows, vrows, ncols, tablecol, tschemacol, viewcol,
vschemacol;
dbString *list;
PGresult *rest, *resv;
char buf[DB_SQL_MAX];
*tlist = NULL;
*tcount = 0;
/* Get table names */
sprintf(buf, "SELECT * FROM pg_tables WHERE schemaname %s "
" ('pg_catalog', 'information_schema') ORDER BY tablename", system ? "IN" : "NOT IN");
G_debug(2, "SQL: %s", buf);
rest = PQexec(pg_conn, buf);
if (!rest || PQresultStatus(rest) != PGRES_TUPLES_OK) {
db_d_append_error("%s\n%s",
_("Unable to select table names."),
PQerrorMessage(pg_conn));
db_d_report_error();
PQclear(rest);
return DB_FAILED;
}
/* Find table and schema col */
ncols = PQnfields(rest);
tschemacol = -1;
for (i = 0; i < ncols; i++) {
if (strcmp(PQfname(rest, i), "tablename") == 0)
tablecol = i;
if (strcmp(PQfname(rest, i), "schemaname") == 0)
tschemacol = i;
}
/* Get view names */
sprintf(buf, "SELECT * FROM pg_views WHERE schemaname %s "
" ('pg_catalog', 'information_schema') ORDER BY viewname", system ? "IN" : "NOT IN");
G_debug(2, "SQL: %s", buf);
resv = PQexec(pg_conn, buf);
if (!resv || PQresultStatus(resv) != PGRES_TUPLES_OK) {
db_d_append_error("%s\n%s",
_("Unable to select view names."),
PQerrorMessage(pg_conn));
db_d_report_error();
PQclear(resv);
return DB_FAILED;
}
/* Find viewname and schema col */
ncols = PQnfields(resv);
vschemacol = -1;
for (i = 0; i < ncols; i++) {
if (strcmp(PQfname(resv, i), "viewname") == 0)
viewcol = i;
if (strcmp(PQfname(resv, i), "schemaname") == 0)
vschemacol = i;
}
trows = PQntuples(rest);
vrows = PQntuples(resv);
nrows = trows + vrows;
list = db_alloc_string_array(nrows);
if (list == NULL) {
db_d_append_error(_("Out of memory"));
db_d_report_error();
return DB_FAILED;
}
for (i = 0; i < trows; i++) {
if (tschemacol >= 0) {
sprintf(buf, "%s.%s", (char *)PQgetvalue(rest, i, tschemacol),
(char *)PQgetvalue(rest, i, tablecol));
}
else {
sprintf(buf, "%s", (char *)PQgetvalue(rest, i, tablecol));
}
db_set_string(&list[i], buf);
}
PQclear(rest);
for (j = 0; j < vrows; j++) {
if (vschemacol >= 0) {
sprintf(buf, "%s.%s", (char *)PQgetvalue(resv, j, vschemacol),
(char *)PQgetvalue(resv, j, viewcol));
}
else {
sprintf(buf, "%s", (char *)PQgetvalue(resv, j, viewcol));
}
db_set_string(&list[i], buf);
i++;
}
PQclear(resv);
*tlist = list;
*tcount = nrows;
return DB_OK;
}
int db__driver_open_database(dbHandle * handle)
{
const char *name;
int len;
dbConnection connection;
char buf[1024];
DIR *dir;
struct dirent *ent;
char **tokens;
int no_tokens, n;
G_debug(2, "DBF: db__driver_open_database() name = '%s'",
db_get_handle_dbname(handle));
db.name[0] = '\0';
db.tables = NULL;
db.atables = 0;
db.ntables = 0;
db_get_connection(&connection);
name = db_get_handle_dbname(handle);
/* if name is empty use connection.databaseName */
if (strlen(name) == 0) {
name = connection.databaseName;
}
strcpy(db.name, name);
/* open database dir and read table ( *.dbf files ) names
* to structure */
/* parse variables in db.name if present */
if (db.name[0] == '$') {
tokens = G_tokenize(db.name, "/");
no_tokens = G_number_of_tokens(tokens);
db.name[0] = '\0'; /* re-init */
for (n = 0; n < no_tokens; n++) {
G_debug(3, "tokens[%d] = %s", n, tokens[n]);
if (tokens[n][0] == '$') {
G_strchg(tokens[n], '$', ' ');
G_chop(tokens[n]);
strcat(db.name, G__getenv(tokens[n]));
G_debug(3, " -> %s", G__getenv(tokens[n]));
}
else
strcat(db.name, tokens[n]);
strcat(db.name, "/");
}
G_free_tokens(tokens);
}
G_debug(2, "db.name = %s", db.name);
errno = 0;
dir = opendir(db.name);
if (dir == NULL) {
if (errno == ENOENT) {
int status;
status = G_mkdir(db.name);
if (status != 0) { /* mkdir failed */
db_d_append_error(_("Unable create DBF database: %s"), name);
db_d_report_error();
return DB_FAILED;
}
else {
/* now that the dbf/ dir is created, try again */
dir = opendir(db.name);
if (dir == NULL) {
db_d_append_error(_("Cannot open DBF database directory: %s"),
name);
db_d_report_error();
return DB_FAILED;
}
}
}
else { /* some other problem */
db_d_append_error(_("Unable to open DBF database: %s"), name);
db_d_report_error();
return DB_FAILED;
}
}
while ((ent = readdir(dir))) {
len = strlen(ent->d_name) - 4;
if ((len > 0) && (G_strcasecmp(ent->d_name + len, ".dbf") == 0)) {
strcpy(buf, ent->d_name);
buf[len] = '\0';
add_table(buf, ent->d_name);
}
}
closedir(dir);
return DB_OK;
}
int db__driver_fetch(dbCursor * cn, int position, int *more)
{
cursor *c;
dbToken token;
dbTable *table;
int i, ret;
int ns;
/* get cursor token */
token = db_get_cursor_token(cn);
/* get the cursor by its token */
if (!(c = (cursor *) db_find_token(token))) {
db_d_append_error(("Cursor not found"));
db_d_report_error();
return DB_FAILED;
}
G_debug(3, "fetch row = %d", c->row);
/* fetch on position */
switch (position) {
case DB_NEXT:
case DB_FIRST:
if (position == DB_FIRST)
c->row = -1;
ret = sqlite3_step(c->statement);
if (ret != SQLITE_ROW) {
/* get real result code */
ret = sqlite3_reset(c->statement);
if (ret != SQLITE_OK) {
db_d_append_error("%s\n%s",
_("Unable to fetch:"),
(char *)sqlite3_errmsg(sqlite));
db_d_report_error();
return DB_FAILED;
}
*more = 0;
return DB_OK;
}
c->row++;
break;
case DB_CURRENT:
break;
case DB_PREVIOUS:
db_d_append_error(_("DB_PREVIOUS is not supported"));
db_d_report_error();
return DB_FAILED;
break;
case DB_LAST:
db_d_append_error(_("DB_LAST is not supported"));
db_d_report_error();
return DB_FAILED;
break;
};
*more = 1;
/* get the data out of the descriptor into the table */
table = db_get_cursor_table(cn);
for (i = 0; i < c->nkcols; i++) {
int col, litetype, sqltype;
dbColumn *column;
dbValue *value;
const char *text;
dbDateTime *dt;
col = c->kcols[i]; /* known cols */
column = db_get_table_column(table, i);
sqltype = db_get_column_sqltype(column);
/* fails for dates:
litetype = db_get_column_host_type(column);
*/
litetype = sqlite3_column_type(c->statement, col);
text = (const char *)sqlite3_column_text(c->statement, col);
value = db_get_column_value(column);
db_zero_string(&value->s);
/* Is null? */
if (sqlite3_column_type(c->statement, col) == SQLITE_NULL) {
value->isNull = 1;
continue;
}
else {
value->isNull = 0;
}
G_debug(3, "col %d, litetype %d, sqltype %d: val = '%s'",
col, litetype, sqltype, text);
/* http://www.sqlite.org/capi3ref.html#sqlite3_column_type
SQLITE_INTEGER 1
SQLITE_FLOAT 2
SQLITE_TEXT 3
SQLITE_BLOB 4
SQLITE_NULL 5
lib/db/dbmi_base/sqltype.c defines:
DB_SQL_TYPE_*
*/
/* Note: we have set DATESTYLE TO ISO in db_driver_open_select_cursor() so datetime
* format should be ISO */
switch (sqltype) {
case DB_SQL_TYPE_INTEGER:
case DB_SQL_TYPE_SMALLINT:
case DB_SQL_TYPE_SERIAL:
value->i = sqlite3_column_int(c->statement, col);
break;
case DB_SQL_TYPE_REAL:
case DB_SQL_TYPE_DOUBLE_PRECISION:
value->d = sqlite3_column_double(c->statement, col);
break;
case DB_SQL_TYPE_DATE:
dt = &value->t;
dt->hour = 0;
dt->minute = 0;
dt->seconds = 0.0;
G_debug(3, "sqlite fetched date: <%s>", text);
ns = sscanf(text, "%4d-%2d-%2d", &dt->year, &dt->month, &dt->day);
if (ns != 3) {
db_d_append_error("%s %s",
_("Unable to scan date:"),
text);
db_d_report_error();
return DB_FAILED;
}
break;
case DB_SQL_TYPE_TIME:
dt = &value->t;
dt->year = 0;
dt->month = 0;
dt->day = 0;
G_debug(3, "sqlite fetched date: %s", text);
ns = sscanf(text, "%2d:%2d:%lf",
&dt->hour, &dt->minute, &dt->seconds);
if (ns != 3) {
db_d_append_error("%s %s",
_("Unable to scan time:"),
text);
db_d_report_error();
return DB_FAILED;
}
break;
case DB_SQL_TYPE_TIMESTAMP:
dt = &value->t;
G_debug(3, "sqlite fetched timestamp: %s", text);
ns = sscanf(text, "%4d-%2d-%2d %2d:%2d:%lf",
&dt->year, &dt->month, &dt->day,
&dt->hour, &dt->minute, &dt->seconds);
if (ns != 6) {
db_d_append_error("%s %s",
_("Unable to scan timestamp:"),
text);
db_d_report_error();
return DB_FAILED;
}
break;
case DB_SQL_TYPE_INTERVAL:
dt = &value->t;
dt->year = 0;
dt->month = 0;
dt->day = 0;
dt->hour = 0;
dt->minute = 0;
G_debug(3, "sqlite fetched interval: %s", text);
G_warning(_("SQLite driver: parsing of interval values "
"not implemented; assuming seconds"));
ns = sscanf(text, "%lf", &dt->seconds);
if (ns != 1) {
db_d_append_error("%s %s",
_("Unable to scan interval:"),
text);
db_d_report_error();
return DB_FAILED;
}
break;
case DB_SQL_TYPE_DECIMAL:
case DB_SQL_TYPE_NUMERIC:
case DB_SQL_TYPE_CHARACTER:
case DB_SQL_TYPE_TEXT:
db_set_string(&value->s, text);
break;
}
}
G_debug(3, "Row fetched");
return DB_OK;
}
int db__driver_open_select_cursor(dbString * sel, dbCursor * dbc, int mode)
{
PGresult *res;
cursor *c;
dbTable *table;
char *str;
/* Set datetime style */
res = PQexec(pg_conn, "SET DATESTYLE TO ISO");
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) {
db_d_append_error(_("Unable set DATESTYLE"));
db_d_report_error();
PQclear(res);
return DB_FAILED;
}
PQclear(res);
/* allocate cursor */
c = alloc_cursor();
if (c == NULL)
return DB_FAILED;
db_set_cursor_mode(dbc, mode);
db_set_cursor_type_readonly(dbc);
/* \ must be escaped, see explanation in db_driver_execute_immediate() */
str = G_str_replace(db_get_string(sel), "\\", "\\\\");
G_debug(3, "Escaped SQL: %s", str);
c->res = PQexec(pg_conn, str);
if (!c->res || PQresultStatus(c->res) != PGRES_TUPLES_OK) {
db_d_append_error("%s\n%s\n%s",
_("Unable to select:"),
db_get_string(sel),
PQerrorMessage(pg_conn));
db_d_report_error();
PQclear(c->res);
if (str)
G_free(str);
return DB_FAILED;
}
if (str)
G_free(str);
if (describe_table(c->res, &table, c) == DB_FAILED) {
db_d_append_error(_("Unable to describe table"));
db_d_report_error();
PQclear(res);
return DB_FAILED;
}
c->nrows = PQntuples(c->res);
c->row = -1;
/* record table with dbCursor */
db_set_cursor_table(dbc, table);
/* set dbCursor's token for my cursor */
db_set_cursor_token(dbc, c->token);
return DB_OK;
}
int db__driver_open_database(dbHandle * handle)
{
char *name;
dbConnection default_connection;
MYSQL *res;
db_get_connection(&default_connection);
name = G_store(db_get_handle_dbname(handle));
/* if name is empty use default_connection.databaseName */
if (strlen(name) == 0)
name = default_connection.databaseName;
G_debug(3, "db_driver_open_database() mysql: database definition = '%s'",
name);
/* Embedded version */
{
char *datadir, *database;
char *server_args[4];
char *buf;
if (!replace_variables(name, &datadir, &database)) {
db_d_append_error(_("Unable parse MySQL embedded database name"));
db_d_append_error(mysql_error(connection));
db_d_report_error();
return DB_FAILED;
}
server_args[0] = "mesql"; /* this string is not used */
G_asprintf(&buf, "--datadir=%s", datadir);
server_args[1] = buf;
/* With InnoDB it is very slow to close the database */
server_args[2] = "--skip-innodb"; /* OK? */
/* Without --bootstrap it complains about missing
* mysql.time_zone_leap_second table */
server_args[3] = "--bootstrap"; /* OK? */
if (mysql_server_init(4, server_args, NULL)) {
db_d_append_error(_("Cannot initialize MySQL embedded server"));
db_d_append_error(mysql_error(connection));
db_d_report_error();
free(datadir);
free(database);
return DB_FAILED;
}
connection = mysql_init(NULL);
mysql_options(connection, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
res =
mysql_real_connect(connection, NULL, NULL, NULL, database, 0,
NULL, 0);
free(datadir);
free(database);
if (res == NULL) {
db_d_append_error(_("Unable to connect to MySQL embedded server: "));
db_d_append_error(mysql_error(connection));
db_d_report_error();
return DB_FAILED;
}
}
return DB_OK;
}
int db__driver_list_tables(dbString ** tlist, int *tcount, int system)
{
int i, nrows;
dbString *list;
sqlite3_stmt *statement;
const char *rest;
int ret;
G_debug(3, "db__driver_list_tables(): system = %d", system);
ret = sqlite3_prepare(sqlite,
"select name from sqlite_master where type = 'table' or type = 'view'",
-1, &statement, &rest);
if (ret != SQLITE_OK) {
db_d_append_error("%s\n%s",
_("Unable to list tables:"),
(char *)sqlite3_errmsg(sqlite));
db_d_report_error();
sqlite3_finalize(statement);
return DB_FAILED;
}
nrows = 0;
while (sqlite3_step(statement) == SQLITE_ROW) {
nrows++;
}
/* get real result code */
ret = sqlite3_reset(statement);
if (ret != SQLITE_OK) {
db_d_append_error("%s\n%s",
_("Unable to list tables:"),
(char *)sqlite3_errmsg(sqlite));
db_d_report_error();
sqlite3_finalize(statement);
return DB_FAILED;
}
G_debug(3, "nrows = %d", nrows);
list = db_alloc_string_array(nrows);
if (list == NULL) {
db_d_append_error(_("Unable to db_alloc_string_array()"));
db_d_report_error();
sqlite3_finalize(statement);
return DB_FAILED;
}
i = 0;
while (sqlite3_step(statement) == SQLITE_ROW) {
G_debug(3, "table: %s", sqlite3_column_text(statement, 0));
db_set_string(&list[i], (char *)sqlite3_column_text(statement, 0));
i++;
}
sqlite3_finalize(statement);
*tlist = list;
*tcount = nrows;
return DB_OK;
}
int db__driver_execute_immediate(dbString * sql)
{
char *where, *table;
int res, ncols, i;
column_info *cols;
OGRLayerH hLayer;
OGRFeatureH hFeature;
OGRFeatureDefnH hFeatureDefn;
OGRFieldDefnH hFieldDefn;
G_debug(1, "db__driver_execute_immediate():");
G_debug(3, "\tSQL: '%s'", db_get_string(sql));
/* try RDBMS SQL */
OGR_DS_ExecuteSQL(hDs, db_get_string(sql), NULL, NULL);
if (CPLGetLastErrorType() == CE_None)
return DB_OK;
/* parse UPDATE statement */
res = parse_sql_update(db_get_string(sql), &table, &cols, &ncols, &where);
G_debug(3, "\tUPDATE: table=%s, where=%s, ncols=%d", table, where ? where : "", ncols);
if (res != 0)
return DB_FAILED;
/* get OGR layer */
hLayer = OGR_DS_GetLayerByName(hDs, table);
if (!hLayer) {
db_d_append_error(_("OGR layer <%s> not found"), table);
db_d_report_error();
return DB_FAILED;
}
if (where)
OGR_L_SetAttributeFilter(hLayer, where);
/* get columns info */
hFeatureDefn = OGR_L_GetLayerDefn(hLayer);
for (i = 0; i < ncols; i++) {
cols[i].index = OGR_FD_GetFieldIndex(hFeatureDefn, cols[i].name);
if (cols[i].index < 0) {
db_d_append_error(_("Column <%s> not found in table <%s>"),
cols[i].name, table);
db_d_report_error();
return DB_FAILED;
}
cols[i].qindex = OGR_FD_GetFieldIndex(hFeatureDefn, cols[i].value);
hFieldDefn = OGR_FD_GetFieldDefn(hFeatureDefn, cols[i].index);
cols[i].type = OGR_Fld_GetType(hFieldDefn);
G_debug(3, "\t\tcol=%s, val=%s, idx=%d, type=%d, qidx=%d",
cols[i].name, cols[i].value, cols[i].index, cols[i].type,
cols[i].qindex);
}
/* update features */
OGR_L_ResetReading(hLayer);
while(TRUE) {
char *value;
hFeature = OGR_L_GetNextFeature(hLayer);
if (!hFeature)
break;
G_debug(5, "\tfid=%ld", OGR_F_GetFID(hFeature));
for (i = 0; i < ncols; i++) {
if (cols[i].qindex > -1) {
value = (char *)OGR_F_GetFieldAsString(hFeature, cols[i].qindex);
}
else {
if ((cols[i].type != OFTInteger ||
cols[i].type != OFTReal) && *(cols[i].value) == '\'') {
value = G_strchg(cols[i].value, '\'', ' ');
G_strip(value);
}
else {
value = cols[i].value;
}
}
OGR_F_SetFieldString(hFeature, cols[i].index, value);
}
OGR_L_SetFeature(hLayer, hFeature);
OGR_F_Destroy(hFeature);
}
G_free(table);
G_free(where);
for (i = 0; i < ncols; i++) {
G_free(cols[i].name);
G_free(cols[i].value);
}
return DB_OK;
}