int SqliteClient::executeSqlQuery(const string& sql, DataTable& table)
{
Locker locker(&_sqliteDbMutex);
#if DEBUG
Stopwatch sw("executeSqlQuery", 100);
#endif
sqlite3_stmt *stmt;
int result = sqlite3_prepare_v2(_sqliteDb, sql.c_str(), sql.length(), &stmt, 0);
if(result != SQLITE_OK)
{
printErrorInfo("sqlite3_prepare_v2", sql);
return result;
}
//const char* name = sqlite3_column_table_name(stmt, 0);
//table.setName(name != NULL ? name : "temp");
// todo: linke error for sqlite3_column_table_name.
table.setName("temp");
#if DEBUG
sw.setInfo(Convert::convertStr("executeSqlQuery, the table name is '%s'", table.getName().c_str()));
#endif
int columnCount = sqlite3_column_count(stmt);
for (int i = 0; i < columnCount; i++)
{
char* nameStr = (char*)sqlite3_column_name(stmt, i);
string name;
if(nameStr != NULL)
{
name = nameStr;
}
else
{
char temp[32];
sprintf(temp, "tempCol%d", i);
name = temp;
}
const char* typeStr = sqlite3_column_decltype(stmt, i);
string type = typeStr != NULL ? typeStr : "int";
DataColumn* column = new DataColumn(name, type);
table.addColumn(column);
}
while(sqlite3_step(stmt) == SQLITE_ROW)
{
DataRow* row = new DataRow();
for (int i = 0; i < columnCount; i++)
{
DataColumn* column = table.getColumns()->at(i);
DataCell* cell = NULL;
Value value;
memset(&value, 0, sizeof(value));
ValueTypes type = column->getType();
switch (type)
{
case Null:
break;
case Integer:
value.nValue = sqlite3_column_int(stmt, i);
break;
case String:
case DateTime:
{
char* str = (char*)sqlite3_column_text(stmt, i);
DataCell::setStringValue(value, str);
}
break;
case Float:
value.dValue = sqlite3_column_double(stmt, i);
break;
default:
assert(false);
break;
}
cell = new DataCell(column, value);
row->addCell(cell);
}
table.addRow(row);
}
result = sqlite3_finalize(stmt);
if(result != SQLITE_OK)
{
printErrorInfo("sqlite3_finalize", sql);
return result;
}
return SQLITE_OK;
}
int SqliteClient::executeSqlInsert(const DataTable& table)
{
Locker locker(&_sqliteDbMutex);
if(table.getName().empty())
return -1;
if(table.columnCount() == 0)
return -2;
if(table.rowCount() == 0)
return -3;
// such like '"INSERT INTO example VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)";'
string valuesStr = "";
int columnCount = table.columnCount();
for (int i = 0; i < columnCount; i++)
{
if(i != 0)
{
valuesStr += ", ";
}
valuesStr += Convert::convertStr("?%d", i + 1);
}
string sql = Convert::convertStr("INSERT INTO [%s] VALUES (%s)", table.getName().c_str(), valuesStr.c_str());
sqlite3_stmt *stmt;
int result = sqlite3_prepare_v2(_sqliteDb, sql.c_str(), sql.length(), &stmt, 0);
if(result != SQLITE_OK)
{
printErrorInfo("sqlite3_prepare_v2", sql);
return result;
}
result = execute("BEGIN TRANSACTION");
if(result != SQLITE_OK)
{
return result;
}
int rowCount = table.rowCount();
for (int i = 0; i < rowCount; i++)
{
const DataRow* row = table.getRows()->at(i);
for (int j = 0; j < columnCount; j++)
{
const DataCell* cell = row->getCells()->at(j);
if(cell != NULL)
{
ValueTypes type = cell->getType();
const Value value = cell->getValue();
switch (type)
{
case Null:
sqlite3_bind_null(stmt, j + 1);
break;
case Integer:
sqlite3_bind_int(stmt, j + 1, value.nValue);
break;
case DateTime:
case String:
result = sqlite3_bind_text(stmt, j + 1, value.strValue, strlen(value.strValue), SQLITE_TRANSIENT);
break;
case Float:
sqlite3_bind_double(stmt, j + 1, value.dValue);
break;
default:
assert(false);
break;
}
}
}
result = sqlite3_step(stmt);
if (result != SQLITE_DONE)
{
printErrorInfo("sqlite3_step", sql);
}
result = sqlite3_reset(stmt);
if(result != SQLITE_OK)
{
printErrorInfo("sqlite3_reset", sql);
}
}
result = execute("COMMIT TRANSACTION");
if(result != SQLITE_OK)
{
return result;
}
sqlite3_finalize(stmt);
if(result != SQLITE_OK)
{
printErrorInfo("sqlite3_finalize", sql);
return result;
}
return SQLITE_OK;
}