[yunqa.de] "Create table with default" problem

  • From: edoardo falzetti <edoardo.falzetti@xxxxxxxxxxxxxx>
  • To: yunqa@xxxxxxxxxxxxx
  • Date: Mon, 15 Aug 2011 17:27:04 +0200


Hallo!

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: