00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { SQLite Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Seroukhov }
00007 { }
00008 {*********************************************************}
00009
00010 {@********************************************************}
00011 { Copyright (c) 1999-2006 Zeos Development Group }
00012 { }
00013 { License Agreement: }
00014 { }
00015 { This library is distributed in the hope that it will be }
00016 { useful, but WITHOUT ANY WARRANTY; without even the }
00017 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00018 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00019 { Public License for more details. }
00020 { }
00021 { The source code of the ZEOS Libraries and packages are }
00022 { distributed under the Library GNU General Public }
00023 { License (see the file COPYING / COPYING.ZEOS) }
00024 { with the following modification: }
00025 { As a special exception, the copyright holders of this }
00026 { library give you permission to link this library with }
00027 { independent modules to produce an executable, }
00028 { regardless of the license terms of these independent }
00029 { modules, and to copy and distribute the resulting }
00030 { executable under terms of your choice, provided that }
00031 { you also meet, for each linked independent module, }
00032 { the terms and conditions of the license of that module. }
00033 { An independent module is a module which is not derived }
00034 { from or based on this library. If you modify this }
00035 { library, you may extend this exception to your version }
00036 { of the library, but you are not obligated to do so. }
00037 { If you do not wish to do so, delete this exception }
00038 { statement from your version. }
00039 { }
00040 { }
00041 { The project web site is located on: }
00042 { http:
00043 { http:
00044 { svn:
00045 { }
00046 { http:
00047 { http:
00048 { }
00049 { }
00050 { }
00051 { Zeos Development Group. }
00052 {********************************************************@}
00053
00054 unit ZDbcSqLiteStatement;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 Classes, SysUtils, ZDbcIntfs, ZDbcStatement, ZPlainSqLiteDriver,
00062 ZCompatibility, ZDbcLogging, ZVariant;
00063
00064 type
00065
00066 {** Implements Generic SQLite Statement. }
00067 TZSQLiteStatement = class(TZAbstractStatement)
00068 private
00069 FHandle: Psqlite;
00070 FPlainDriver: IZSQLitePlainDriver;
00071
00072 function CreateResultSet(const SQL: string; StmtHandle: Psqlite_vm;
00073 ColumnCount: Integer; ColumnNames: PPChar;
00074 ColumnValues: PPChar): IZResultSet;
00075 public
00076 constructor Create(PlainDriver: IZSQLitePlainDriver;
00077 Connection: IZConnection; Info: TStrings; Handle: Psqlite);
00078
00079 function ExecuteQuery(const SQL: string): IZResultSet; override;
00080 function ExecuteUpdate(const SQL: string): Integer; override;
00081 function Execute(const SQL: string): Boolean; override;
00082 end;
00083
00084 {** Implements Prepared SQL Statement. }
00085 TZSQLitePreparedStatement = class(TZEmulatedPreparedStatement)
00086 private
00087 FHandle: Psqlite;
00088 FPlainDriver: IZSQLitePlainDriver;
00089 protected
00090 function CreateExecStatement: IZStatement; override;
00091 function GetEscapeString(const Value: string): string;
00092 function PrepareSQLParam(ParamIndex: Integer): string; override;
00093 public
00094 constructor Create(PlainDriver: IZSQLitePlainDriver;
00095 Connection: IZConnection; const SQL: string; Info: TStrings;
00096 Handle: Psqlite);
00097 end;
00098
00099 implementation
00100
00101 uses
00102 ZDbcSqLiteUtils, ZDbcSqLiteResultSet, ZSysUtils,
00103 ZMessages, ZDbcCachedResultSet;
00104
00105 { TZSQLiteStatement }
00106
00107 {**
00108 Constructs this object and assignes the main properties.
00109 @param PlainDriver a native SQLite plain driver.
00110 @param Connection a database connection object.
00111 @param Handle a connection handle pointer.
00112 @param Info a statement parameters.
00113 }
00114 constructor TZSQLiteStatement.Create(PlainDriver: IZSQLitePlainDriver;
00115 Connection: IZConnection; Info: TStrings; Handle: Psqlite);
00116 begin
00117 inherited Create(Connection, Info);
00118 FHandle := Handle;
00119 FPlainDriver := PlainDriver;
00120 ResultSetType := rtScrollInsensitive;
00121 end;
00122
00123 {**
00124 Creates a result set based on the current settings.
00125 @return a created result set object.
00126 }
00127 function TZSQLiteStatement.CreateResultSet(const SQL: string; StmtHandle: Psqlite_vm;
00128 ColumnCount: Integer; ColumnNames: PPChar; ColumnValues: PPChar): IZResultSet;
00129 var
00130 CachedResolver: TZSQLiteCachedResolver;
00131 NativeResultSet: TZSQLiteResultSet;
00132 CachedResultSet: TZCachedResultSet;
00133 begin
00134 { Creates a native result set. }
00135 NativeResultSet := TZSQLiteResultSet.Create(FPlainDriver, Self, SQL, FHandle,
00136 StmtHandle, ColumnCount, ColumnNames, ColumnValues);
00137 NativeResultSet.SetConcurrency(rcReadOnly);
00138
00139 { Creates a cached result set. }
00140 CachedResolver := TZSQLiteCachedResolver.Create(FPlainDriver, FHandle, Self,
00141 NativeResultSet.GetMetaData);
00142 CachedResultSet := TZCachedResultSet.Create(NativeResultSet, SQL,
00143 CachedResolver);
00144
00145 { Fetches all rows to prevent blocking. }
00146 CachedResultSet.SetType(rtScrollInsensitive);
00147 CachedResultSet.Last;
00148 CachedResultSet.BeforeFirst;
00149 CachedResultSet.SetConcurrency(GetResultSetConcurrency);
00150
00151 Result := CachedResultSet;
00152 end;
00153
00154 {**
00155 Executes an SQL statement that returns a single <code>ResultSet</code> object.
00156 @param sql typically this is a static SQL <code>SELECT</code> statement
00157 @return a <code>ResultSet</code> object that contains the data produced by the
00158 given query; never <code>null</code>
00159 }
00160 function TZSQLiteStatement.ExecuteQuery(const SQL: string): IZResultSet;
00161 var
00162 ErrorCode: Integer;
00163 ErrorMessage: PChar;
00164 SQLTail: PChar;
00165 StmtHandle: Psqlite_vm;
00166 ColumnCount: Integer;
00167 ColumnValues: PPChar;
00168 ColumnNames: PPChar;
00169 begin
00170 ErrorMessage := '';
00171 SQLTail := '';
00172 ColumnCount := 0;
00173 ErrorCode := FPlainDriver.Compile(FHandle, PChar(SQL), Length(SQL), SQLTail,
00174 StmtHandle, ErrorMessage);
00175 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
00176 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00177
00178 try
00179 ErrorCode := FPlainDriver.Step(StmtHandle, ColumnCount,
00180 ColumnValues, ColumnNames);
00181 CheckSQLiteError(FPlainDriver, ErrorCode, nil, lcOther, 'FETCH');
00182 except
00183 FPlainDriver.Finalize(StmtHandle, ErrorMessage);
00184 raise;
00185 end;
00186
00187 Result := CreateResultSet(SQL, StmtHandle, ColumnCount, ColumnNames,
00188 ColumnValues);
00189 end;
00190
00191 {**
00192 Executes an SQL <code>INSERT</code>, <code>UPDATE</code> or
00193 <code>DELETE</code> statement. In addition,
00194 SQL statements that return nothing, such as SQL DDL statements,
00195 can be executed.
00196
00197 @param sql an SQL <code>INSERT</code>, <code>UPDATE</code> or
00198 <code>DELETE</code> statement or an SQL statement that returns nothing
00199 @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
00200 or <code>DELETE</code> statements, or 0 for SQL statements that return nothing
00201 }
00202 function TZSQLiteStatement.ExecuteUpdate(const SQL: string): Integer;
00203 var
00204 ErrorCode: Integer;
00205 ErrorMessage: PChar;
00206 begin
00207 ErrorMessage := '';
00208 ErrorCode := FPlainDriver.Execute(FHandle, PChar(SQL), nil, nil,
00209 ErrorMessage);
00210 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
00211 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00212 Result := FPlainDriver.Changes(FHandle);
00213 LastUpdateCount := Result;
00214 end;
00215
00216 {**
00217 Executes an SQL statement that may return multiple results.
00218 Under some (uncommon) situations a single SQL statement may return
00219 multiple result sets and/or update counts. Normally you can ignore
00220 this unless you are (1) executing a stored procedure that you know may
00221 return multiple results or (2) you are dynamically executing an
00222 unknown SQL string. The methods <code>execute</code>,
00223 <code>getMoreResults</code>, <code>getResultSet</code>,
00224 and <code>getUpdateCount</code> let you navigate through multiple results.
00225
00226 The <code>execute</code> method executes an SQL statement and indicates the
00227 form of the first result. You can then use the methods
00228 <code>getResultSet</code> or <code>getUpdateCount</code>
00229 to retrieve the result, and <code>getMoreResults</code> to
00230 move to any subsequent result(s).
00231
00232 @param sql any SQL statement
00233 @return <code>true</code> if the next result is a <code>ResultSet</code> object;
00234 <code>false</code> if it is an update count or there are no more results
00235 }
00236 function TZSQLiteStatement.Execute(const SQL: string): Boolean;
00237 var
00238 ErrorCode: Integer;
00239 ErrorMessage: PChar;
00240 SQLTail: PChar;
00241 StmtHandle: Psqlite_vm;
00242 ColumnCount: Integer;
00243 ColumnValues: PPChar;
00244 ColumnNames: PPChar;
00245 begin
00246 ErrorMessage := '';
00247 SQLTail := '';
00248 ColumnCount := 0;
00249 ErrorCode := FPlainDriver.Compile(FHandle, PChar(SQL), Length(SQL), SQLTail,
00250 StmtHandle, ErrorMessage);
00251 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
00252 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00253
00254 try
00255 ErrorCode := FPlainDriver.Step(StmtHandle, ColumnCount,
00256 ColumnValues, ColumnNames);
00257 CheckSQLiteError(FPlainDriver, ErrorCode, nil, lcOther, 'FETCH');
00258 except
00259 FPlainDriver.Finalize(StmtHandle, ErrorMessage);
00260 raise;
00261 end;
00262
00263 { Process queries with result sets }
00264 if ColumnCount <> 0 then
00265 begin
00266 Result := True;
00267 LastResultSet := CreateResultSet(SQL, StmtHandle, ColumnCount, ColumnNames,
00268 ColumnValues);
00269 end
00270 { Processes regular query. }
00271 else
00272 begin
00273 Result := False;
00274 LastUpdateCount := FPlainDriver.Changes(FHandle);
00275 ErrorCode := FPlainDriver.Finalize(StmtHandle, ErrorMessage);
00276 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcOther,
00277 'Finalize SQLite VM');
00278 end;
00279 end;
00280
00281 { TZSQLitePreparedStatement }
00282
00283 {**
00284 Constructs this object and assignes the main properties.
00285 @param PlainDriver a native SQLite Plain driver.
00286 @param Connection a database connection object.
00287 @param Info a statement parameters.
00288 @param Handle a connection handle pointer.
00289 }
00290 constructor TZSQLitePreparedStatement.Create(PlainDriver: IZSQLitePlainDriver;
00291 Connection: IZConnection; const SQL: string; Info: TStrings; Handle: Psqlite);
00292 begin
00293 inherited Create(Connection, SQL, Info);
00294 FHandle := Handle;
00295 FPlainDriver := PlainDriver;
00296 ResultSetType := rtForwardOnly;
00297 end;
00298
00299 {**
00300 Creates a temporary statement which executes queries.
00301 @param Info a statement parameters.
00302 @return a created statement object.
00303 }
00304 function TZSQLitePreparedStatement.CreateExecStatement: IZStatement;
00305 begin
00306 Result := TZSQLiteStatement.Create(FPlainDriver, Connection, Info,FHandle);
00307 end;
00308
00309 {**
00310 Converts an string into escape SQLite format.
00311 @param Value a regular string.
00312 @return a string in SQLite escape format.
00313 }
00314 function TZSQLitePreparedStatement.GetEscapeString(const Value: string): string;
00315 begin
00316 Result := AnsiQuotedStr(Value, '''');
00317 end;
00318
00319 {**
00320 Prepares an SQL parameter for the query.
00321 @param ParameterIndex the first parameter is 1, the second is 2, ...
00322 @return a string representation of the parameter.
00323 }
00324 function TZSQLitePreparedStatement.PrepareSQLParam(ParamIndex: Integer): string;
00325 var
00326 Value: TZVariant;
00327 TempBytes: TByteDynArray;
00328 TempBlob: IZBlob;
00329 begin
00330 TempBytes := nil;
00331 if InParamCount <= ParamIndex then
00332 raise EZSQLException.Create(SInvalidInputParameterCount);
00333
00334 Value := InParamValues[ParamIndex];
00335 if DefVarManager.IsNull(Value) then
00336 Result := 'NULL'
00337 else begin
00338 case InParamTypes[ParamIndex] of
00339 stBoolean:
00340 if SoftVarManager.GetAsBoolean(Value) then Result := '''Y'''
00341 else Result := '''N''';
00342 stByte, stShort, stInteger, stLong, stBigDecimal, stFloat, stDouble:
00343 Result := SoftVarManager.GetAsString(Value);
00344 stString, stBytes:
00345 Result := GetEscapeString(SoftVarManager.GetAsString(Value));
00346 stDate:
00347 Result := '''' + FormatDateTime('yyyy-mm-dd',
00348 SoftVarManager.GetAsDateTime(Value)) + '''';
00349 stTime:
00350 Result := '''' + FormatDateTime('hh:mm:ss',
00351 SoftVarManager.GetAsDateTime(Value)) + '''';
00352 stTimestamp:
00353 Result := '''' + FormatDateTime('yyyy-mm-dd hh:mm:ss',
00354 SoftVarManager.GetAsDateTime(Value)) + '''';
00355 stAsciiStream, stUnicodeStream, stBinaryStream:
00356 begin
00357 TempBlob := DefVarManager.GetAsInterface(Value) as IZBlob;
00358 if not TempBlob.IsEmpty then
00359 begin
00360 if InParamTypes[ParamIndex] = stBinaryStream then
00361 Result := EncodeString(TempBlob.GetString)
00362 else Result := GetEscapeString(TempBlob.GetString);
00363 end else
00364 Result := 'NULL';
00365 end;
00366 end;
00367 end;
00368 end;
00369
00370 end.