Standard Portable Library: src/data/SqlLiteCommand.cpp Source File

00001 /*
00002  *   This file is part of the Standard Portable Library (SPL).
00003  *
00004  *   SPL is free software: you can redistribute it and/or modify
00005  *   it under the terms of the GNU General Public License as published by
00006  *   the Free Software Foundation, either version 3 of the License, or
00007  *   (at your option) any later version.
00008  *
00009  *   SPL is distributed in the hope that it will be useful,
00010  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
00011  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
00012  *   GNU General Public License for more details.
00013  *
00014  *   You should have received a copy of the GNU General Public License
00015  *   along with SPL.  If not, see <http://www.gnu.org/licenses/>.
00016  */
00017 #include <spl/data/SqlLiteCommand.h>
00018 
00019 #include "src/sqllite/sqlite3.h"
00020 
00021 SqlLiteCommand::SqlLiteCommand()
00022 : m_db(NULL), m_stmt(NULL)
00023 {
00024 }
00025 
00026 SqlLiteCommand::SqlLiteCommand(const SqlLiteCommand& cmd)
00027 : Command(cmd), m_db(cmd.m_db), m_stmt(NULL)
00028 {
00029 }
00030 
00031 SqlLiteCommand::SqlLiteCommand(void *db, const String& cmdtxt)
00032 : Command(cmdtxt), m_db(db), m_stmt(NULL)
00033 {
00034 }
00035 
00036 SqlLiteCommand::~SqlLiteCommand()
00037 {
00038         if (NULL != m_stmt)
00039         {
00040                 sqlite3_finalize((sqlite3_stmt *)m_stmt);
00041                 m_stmt = NULL;
00042         }
00043 }
00044 
00045 SqlLiteCommand& SqlLiteCommand::operator =(const SqlLiteCommand& cmd)
00046 {
00047         Command::operator =(cmd);
00048         m_db = cmd.m_db;
00049         Clear();
00050         return *this;
00051 }
00052 
00053 void SqlLiteCommand::Prepare()
00054 {
00055         if (NULL == m_stmt)
00056         {
00057                 int rc = sqlite3_prepare_v2((sqlite3 *)m_db, m_cmdtxt.GetChars(), m_cmdtxt.Length(), (sqlite3_stmt **)&m_stmt, NULL);
00058                 if (SQLITE_OK != rc)
00059                 {
00060                         throw new SqlException(sqlite3_errmsg((sqlite3 *)m_db));
00061                 }
00062         }
00063 }
00064 
00065 void SqlLiteCommand::Clear()
00066 {
00067         Command::Clear();
00068         if (NULL != m_stmt)
00069         {
00070                 sqlite3_finalize((sqlite3_stmt *)m_stmt);
00071                 m_stmt = NULL;
00072         }
00073 }
00074 
00075 void SqlLiteCommand::BindParameters()
00076 {
00077         Prepare();
00078 
00079         int rc = sqlite3_clear_bindings((sqlite3_stmt*)m_stmt);
00080         if(SQLITE_OK != rc)
00081         {
00082                 throw new SqlException(sqlite3_errmsg((sqlite3 *)m_db));
00083         }
00084 
00085         rc = sqlite3_reset((sqlite3_stmt *)m_stmt);
00086         if(SQLITE_OK != rc)
00087         {
00088                 throw new SqlException(sqlite3_errmsg((sqlite3 *)m_db));
00089         }
00090 
00091         for (int x = 0; x < ParameterCount(); x++)
00092         {
00093                 CommandParameterPtr prm = GetParameter(x);
00094 
00095                 switch(prm->Type())
00096                 {
00097                 case DbSqlType::SQL_TYPE_UNASSIGNED:
00098                         rc = sqlite3_bind_null((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()));
00099                         break;
00100                 case DbSqlType::SQL_TYPE_INT8:
00101                         rc = sqlite3_bind_int((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetInt32());
00102                         break;
00103                 case DbSqlType::SQL_TYPE_INT16:
00104                         rc = sqlite3_bind_int((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetInt32());
00105                         break;
00106                 case DbSqlType::SQL_TYPE_INT32:
00107                         rc = sqlite3_bind_int((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetInt32());
00108                         break;
00109                 case DbSqlType::SQL_TYPE_INT64:
00110                         rc = sqlite3_bind_int64((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetInt64());
00111                         break;
00112                 case DbSqlType::SQL_TYPE_DECIMAL:
00113                         rc = sqlite3_bind_double((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetFloat64());
00114                         break;
00115                 case DbSqlType::SQL_TYPE_FLOAT32:
00116                         rc = sqlite3_bind_double((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), (double)prm->GetFloat32());
00117                         break;
00118                 case DbSqlType::SQL_TYPE_FLOAT64:
00119                         rc = sqlite3_bind_double((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetFloat64());
00120                         break;
00121                 case DbSqlType::SQL_TYPE_FLAG:
00122                         rc = sqlite3_bind_int((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetInt32());
00123                         break;
00124                 case DbSqlType::SQL_TYPE_TIMESTAMP:
00125                         rc = sqlite3_bind_text((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetDateTime().ToString()->GetChars(), -1, NULL);
00126                         break;
00127                 case DbSqlType::SQL_TYPE_DATE:
00128                         rc = sqlite3_bind_int((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetDate().ToRevInt());
00129                         break;
00130                 case DbSqlType::SQL_TYPE_DATETIME:
00131                         rc = sqlite3_bind_text((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetDateTime().ToString()->GetChars(), -1, NULL);
00132                         break;
00133                 case DbSqlType::SQL_TYPE_CHAR:
00134                         rc = sqlite3_bind_text((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetVarchar()->GetChars(), -1, NULL);
00135                         break;
00136                 case DbSqlType::SQL_TYPE_VARCHAR:
00137                         rc = sqlite3_bind_text((sqlite3_stmt *)m_stmt, sqlite3_bind_parameter_index((sqlite3_stmt *)m_stmt, prm->Name().GetChars()), prm->GetVarchar()->GetChars(), -1, NULL);
00138                         break;
00139                 case DbSqlType::SQL_TYPE_BLOB:
00140                         throw new NotImplementedException("BLOB not yet supported");
00141                         //break;
00142                 }
00143 
00144                 if(SQLITE_OK != rc)
00145                 {
00146                         throw new SqlException(sqlite3_errmsg((sqlite3 *)m_db));
00147                 }
00148         }
00149 }
00150 
00151 int SqlLiteCommand::ExecuteNonQuery()
00152 {
00153         BindParameters();
00154         int rc;
00155 
00156         if (SQLITE_DONE != (rc = sqlite3_step((sqlite3_stmt *)m_stmt)))
00157         {
00158                 SqlException *ex = new SqlException(sqlite3_errmsg((sqlite3 *)m_db));
00159                 sqlite3_finalize((sqlite3_stmt *)m_stmt);
00160                 throw ex;
00161         }
00162         
00163         return 1;
00164 }
00165 
00166 RecordSetPtr SqlLiteCommand::ExecuteQuery()
00167 {
00168         BindParameters();
00169 
00170         RecordSetPtr rs(new RecordSet());
00171 
00172         int numColumns = sqlite3_column_count((sqlite3_stmt *)m_stmt);
00173 
00174         int rc;
00175         while (SQLITE_ROW == (rc = sqlite3_step((sqlite3_stmt *)m_stmt)))
00176         {
00177                 if (rs->ColumnCount() == 0)
00178                 {
00179                         for ( int x = 0; x < numColumns; x++ )
00180                         {
00181                                 int ctyp = sqlite3_column_type((sqlite3_stmt *)m_stmt, x);
00182                                 switch (ctyp)
00183                                 {
00184                                 case SQLITE_INTEGER:
00185                                         rs->DefineColumn(sqlite3_column_name((sqlite3_stmt *)m_stmt, x), DbSqlType::SQL_TYPE_INT64, 8);
00186                                         break;
00187                                 case SQLITE_FLOAT:
00188                                         rs->DefineColumn(sqlite3_column_name((sqlite3_stmt *)m_stmt, x), DbSqlType::SQL_TYPE_FLOAT64, 8);
00189                                         break;
00190                                 case SQLITE_BLOB:
00191                                         throw new SqlException("BLOB not supported yet");
00192                                         //rs->DefineColumn(sqlite3_column_name(stmt, x), DbSqlType::SQL_TYPE_BLOB, 0);
00193                                         //break;
00194                                 case SQLITE_NULL:
00195                                         rs->DefineColumn(sqlite3_column_name((sqlite3_stmt *)m_stmt, x), DbSqlType::SQL_TYPE_VARCHAR, sqlite3_column_bytes((sqlite3_stmt *)m_stmt, x));
00196                                         break;
00197                                 case SQLITE_TEXT:
00198                                         rs->DefineColumn(sqlite3_column_name((sqlite3_stmt *)m_stmt, x), DbSqlType::SQL_TYPE_VARCHAR, sqlite3_column_bytes((sqlite3_stmt *)m_stmt, x));
00199                                         break;
00200                                 default:
00201                                         throw new SqlException("Unknown column type of " + Int32::ToString(ctyp));
00202                                 }
00203                         }
00204                 }
00205 
00206                 for ( int x = 0; x < numColumns; x++ )
00207                 {
00208                         int ctyp = sqlite3_column_type((sqlite3_stmt *)m_stmt, x);
00209                         switch (ctyp)
00210                         {
00211                         case SQLITE_INTEGER:
00212                                 rs->GetColumn(x)->Append(sqlite3_column_int64((sqlite3_stmt *)m_stmt, x));
00213                                 break;
00214                         case SQLITE_FLOAT:
00215                                 rs->GetColumn(x)->Append(sqlite3_column_double((sqlite3_stmt *)m_stmt, x));
00216                                 break;
00217                         case SQLITE_BLOB:
00218                                 break;
00219                         case SQLITE_NULL:
00220                                 rs->GetColumn(x)->AppendNull();
00221                                 break;
00222                         case SQLITE_TEXT:
00223                                 rs->GetColumn(x)->Append(String((const char *)sqlite3_column_text((sqlite3_stmt *)m_stmt, x)));
00224                                 break;
00225                         }
00226                 }               
00227         }
00228 
00229         if (SQLITE_DONE != rc)
00230         {
00231                 throw new SqlException(sqlite3_errmsg((sqlite3 *)m_db));
00232         }
00233 
00234         return rs;
00235 }