[yunqa.de] Re: "Create table with default" problem
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Mon, 15 Aug 2011 17:43:05 +0200
Please send your project sources as TProvider and TClientDataSet for
Delphi 5 have a few bugs which make such problems very intricate and
hard to analyze without compiling the sources.
Ralf
On 15.08.2011 17:27, edoardo falzetti wrote:
> Given a Table created with following string:
>
> SQL_CREATETABLE_T1 =
> 'CREATE TABLE IF NOT EXISTS T1 (' + CRLF +
> '"F1" TEXT PRIMARY KEY,' + CRLF +
> '"F2" TEXT,' + CRLF +
> '"F3" INT DEFAULT 0' + CRLF +
> ')';
>
>
> Working with TDISQLite3UniDirQuery + ... + TClientDataset
>
>
> If i do the following:
>
> Case 1
> ======
>
> cds.open
> cds.insert values : 'K1', 'a'
> ...
> cds.post
> cds.ApplyUpdates
> cds.close
>
> In the table i find: 'K1', 'a', 0 OK !
>
>
> cds.open
> cds.edit 'K1', 'b'
> ...
> cds.post
> cds.ApplyUpdates
> cds.close
>
> In the table i find: 'K1', 'b', 0 OK !
>
>
> Everithing worked fine !
>
> =========
>
> But if i do:
>
> Case 2
> ======
>
> cds.open
> cds.insert values : 'K1', 'a'
> ...
> cds.post
> cds.ApplyUpdates
>
>
> In the table i find: 'K1', 'a', 0 OK !
>
>
>
> cds.edit 'K1', 'b'
> ...
> cds.post
> cds.ApplyUpdates
>
> ERRORS: 'Record changed by another user'
> 'Unable to find record. No key specified'
>
> cds.close
>
> =============
>
> Looking at the code i found out that in the following function:
>
> function TDISQLite3UniDirQuery.PSExecuteStatement(
> const ASql: {$IFDEF COMPILER_10_UP}UnicodeString{$ELSE}AnsiString{$ENDIF};
> AParams: TParams;
> AResultSet: Pointer = nil): Integer;
> var
> Stmt: TDISQLite3Statement;
> Query: TDISQLite3UniDirQuery;
> begin
> if Assigned(AResultSet) then
> begin
> .......
> end
> else
> begin
> Stmt := TDISQLite3Statement.Create;
> try
> Stmt.Database := Database;
> Stmt.SQL16 := ASql; <<<<<<< not well formed
> Stmt.Active := True;
> BindParams(Stmt.Handle, AParams);
> Stmt.Step;
> Result := Database.Changes;
> finally
> Stmt.Free;
> end;
> end;
> end;
>
>
> the SQL instruction in case 2 is not well formed .
>
>
> =======
> Case 1:
> =======
>
> SQL : insert into T1 (F1, F2) values (?, ?)
>
> Params: 'K1', 'a'
>
> Obtained DB row: 'K1', 'a', 0
>
> ---
>
> SQL : update T1 set F2 = ? where F1 = ? and F2 = ? and F3 = ?
>
> Params: 'b' 'K1', 'a', 0
>
>
>
> Obtained DB row: 'K1', 'b', 0
>
> Correct !
>
>
> =======
> Case 2:
> =======
>
>
> SQL : insert into T1 (F1, F2) values (?, ?)
>
> Params: 'K1', 'a'
>
> Obtained DB row: 'K1', 'a', 0
>
> ---
>
> SQL : update T1 set F2 = ? where F1 = ? and F2 = ? and F3 is null
>
> Params: 'b' 'K1', 'a'
>
>
>
> WRONG ! F3 is not null but 0 !!!
>
> =========
>
> I've got an analogous problem if just after the
> "insert"-"post"-"ApplyUpdates"
> i do a
> "delete"-"ApplyUpdates"
> on the same record without closing the cds within the two.
>
>
>
>
> I hope to have been clear enough.
>
> Thanks and greetings
> edoardo.
>
> PS:
> - Delphi version D5
> - Disqlite version ?. Sorry i'm not at work now and cannot check. Anyway it's
> not the last but almost.
> - It's not a big problem to bypass; if it's difficult or long to correct i'll
> eliminate the defaults from the database.
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts: