[libsql] Query and QueryW

[Dak <dak...@ya...>]

Hello Rene

I have suggestion, how to regularize differences
between Query and QueryW command in TLiteDB class. I
suggest use in both metods next functions
SQLite3_Prepare(16), SQLite3_Step and
SQLite3_Finalize, because this way makes it possible
returns more complex info about returned columns in
ResultSet. I will place current implementation Query
metod (with SQLite_exec function) for example in to
new metod SimpleQuery or i dont use this anyway. I
will changne this, that SimpleQuery return no
resultset and be very simple. The final solution
depends on you. In actual implementation of metods
Query and QueryW is couple of missing thigs. If i want
get content table (for exapmle SELECT * FROM Table1;)
and table is full empty (has no row), both metods dont
return name of columns. In DB browser i need show name
of columns even if table is full empty too. Metod
QueryW dont support execution of SQL multistatements.
All this missing thigs i try solve in my proposal. My
proposal looks like in this next metods, it is noly
pseudo-code, full implementation depends on you, you
know your code complex and in detail.

function TLiteDB.SimpleQuery(SQL: String): Boolean;
begin
  use classic way with SQLite_exec. Maybe is possible
completely leave out @QueryCallBack function. Then is
possible use this function for speed execution of
large, a lot of sql statements which dont need
ResultSet.  
end;

function TLiteDB.Query(SQL: String): Boolean;
var
  SQLStatement: PAnsiChar;  
  Stmt: Pointer;
  sr,qr: Integer;
  FLastError: Integer;
begin
  SQLStatement := PAnsiChar(SQL);
  
  repeat
  
    try
      FLastError := SQLite3_prepare(FBaseInfo.FHandle,
SQLStatement, -1, Stmt, SQLStatement);
    
      if FLastError = SQLite_OK then
      begin
        //This must be first, to get definition of
columns even if table is include no rows 
        //This one you execute very pretty in function
StoreFields3, but this must be first!
        if Not FieldDef then
        begin
          Columns := SQLite3_Column_count(Stmt);
          for i := 0 to Columns - 1 do
          begin
            name := Sqlite3_Column_Name(Stmt, i);
            _datatype := Sqlite3_Column_Type (Stmt,
i);
          end;
        end;       
        
        sr := SQLite3_step (Stmt);
        
        while sr=SQLITE_BUSY do
        begin
          sleep(0);
          sr := SQLite3_step (Stmt);
        end;
        
        while sr=SQLITE_ROW do
        begin
          //Geting simple fields of current row
          //This one you execute very pretty in
function StoreRow3
          Columns := SQLite3_Column_count(Stmt);
          for i := 0 to Columns - 1 do
          begin
            Value := SQLite3_Column_Text(Stmt, i);
          end;
          sr := SQLite3_step(Stmt);
          while sr=SQLITE_BUSY do
          begin
            sleep(0);
            sr := SQLite3_step (Stmt);
          end;
        end;
        
        if sr in [SQLITE_ERROR, SQLITE_MISUSE] then
          begin
            FLastErrorText :=
SQLite3_errormsg(FBaseInfo.FHandle);
            FLastError :=
SQLite3_errcode(FBaseInfo.FHandle);
          end
        else
          begin
            FLastErrorText := '';
            FLastError := 0;
          end;
      end
      else
        FLastErrorText := SQLite3_errormsg
(FBaseInfo.FHandle);
        
    finally
      //I see this finalisation in others SQLite
wrappers and works well
      SQLite3_reset(Stmt); 
      SQLite3_finalize(Stmt);
    end;
    
  until SQLStatement^ = #0; //This one solve problem
with SQL Multi-Statement
    
end;

function TLiteDB.QueryW(SQL: WideString): Boolean;
var
  SQLStatement: PWideChar;  
  Stmt: Pointer;
  sr,qr: Integer;
  FLastError: Integer;
begin
  SQLStatement := PAnsiChar(SQL);
  
  repeat
  
    try
      FLastError :=
SQLite3_prepare16(FBaseInfo.FHandle, SQLStatement, -1,
Stmt, SQLStatement);
    
      if FLastError = SQLite_OK then
      begin
        //This must be first, to get definition of
columns even if table is include no rows 
        //This one you execute very pretty in function
StoreFields3, but this must be first!
        if Not FieldDef then
        begin
          Columns := SQLite3_Column_count(Stmt);
          for i := 0 to Columns - 1 do
          begin
            name := Sqlite3_Column_Name16(Stmt, i);
            _datatype := Sqlite3_Column_Type (Stmt,
i);
          end;
        end;       
        
        sr := SQLite3_step (Stmt);
        
        while sr=SQLITE_BUSY do
        begin
          sleep(0);
          sr := SQLite3_step (Stmt);
        end;
        
        while sr=SQLITE_ROW do
        begin
          //Geting simple fields of current row
          //This one you execute very pretty in
function StoreRow3
          Columns := SQLite3_Column_count(Stmt);
          for i := 0 to Columns - 1 do
          begin
            Value := SQLite3_Column_Text16(Stmt, i);
          end;
          sr := SQLite3_step(Stmt);
          while sr=SQLITE_BUSY do
          begin
            sleep(0);
            sr := SQLite3_step (Stmt);
          end;
        end;
        
        if sr in [SQLITE_ERROR, SQLITE_MISUSE] then
          begin
            FLastErrorText :=
SQLite3_errormsg16(FBaseInfo.FHandle);
            FLastError :=
SQLite3_errcode(FBaseInfo.FHandle);
          end
        else
          begin
            FLastErrorText := '';
            FLastError := 0;
          end;
      end
      else
        FLastErrorText := SQLite3_errormsg16
(FBaseInfo.FHandle);
        
    finally
      //I see this finalisation in others SQLite
wrappers and works well
      SQLite3_reset(Stmt); 
      SQLite3_finalize(Stmt);
    end;
    
  until SQLStatement^ = #0; //This one solve problem
with SQL Multi-Statement
    
end;

Thanks my riend Flyman for his help with translation.

Dak



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com