libsql-discussion — Libsql open discussion mailing list

[libsql] Advice on SQL DELETEs inside a SELECT result loop

[Andy C. <and...@bt...>]

Hi,

I've come across what I expect is operator error on my part, so I was
hoping for some quick advice as to the direction I should be going. The SQL task I'm performing I suspect can
be re-written as a compound SQL statement, but while I learn SQL fully, I'm performing it in a couple of stages in Delphi.
This is where I have a query.....

If I do a "SELECT track_id FROM main WHERE checked='N'" and store the resulting list of numbers in an array or list, then
perform the delete command "DELETE FROM main WHERE track_id=%d", using this list of numbers, this works fine with no problems.

But if I try to write it all in one loop, it always fails to perform the DELETE commands.
No errors reported and it still runs down the full list of results inside the loop. 
Both ways below fail in the same way..... (maybe the odd typo as I'm writing partially from memory)



Using one DB object, but 2 result sets
--------------------------------------

rs1 := TResultSet.Create (Ldb);
rs2 := TResultSet.Create (Ldb);
Ldb.StartTransaction;
rs1.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
while rs1.FetchRow do begin
   rs2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
   rs2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
end;
Ldb.Commit;
rs2.free;
rs1.free;




Using two DB objects 
--------------------
Ldb2.StartTransaction;
handle:=Ldb.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
while Ldb.FetchRow(handle,row) do begin
   temp:=row['track_id'];
   Ldb2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
   Ldb2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
end;
Ldb.FreeResult(handle);
Ldb2.Commit;




Who'd like be first in line to call me a lemon ;-)
Thanks all.

Re: [libsql] sqlite 80x faster on transactions

[<re...@du...>]

Hi,

I should have read sqlite's documentation better. Setting pragma=20
synchronous to OFF does about the same (and thus speeding up=20
transactions dramastically). Sorry for any confusion.

Rene



Ren=E9 Tegel wrote:

> Hi,
>
> Inspired by cross-platform usage of sqlite vs transaction and=20
> threading issues, i've been looking why sqlite is so slow with=20
> transactions on my system (XPsp2, amd2500+, 250GB HD, NTFS). A=20
> transaction takes about 80ms on my system, with lot of disk head=20
> movements involved.
> Since i need sqlite especially to be fast for one of my projects=20
> (speed is more important than possible data loss in case of an=20
> operating system crash) i've been investigating how to speed up=20
> sqlite. To accomplish that goal, i've recompiled sqlite3.dll and added=20
> an extra pragma to sqlite that disables disk flushing on each=20
> transaction.
>
> If you want to test (please do not use for production at the moment!),=20
> you can download a modified sqlite dll from sourceforge:
> http://sourceforge.net/project/showfiles.php?group_id=3D103463&package_=
id=3D131328=20
>
>
> to disable disk flush, set the pragma win32_flush_buffers to off (it=20
> defaults to on):
> PRAGMA win32_flush_buffers=3Doff
>
> below the readme of the zip, including a simple benchmark.
>
> regards,
>
> rene
>
> -------------------------------------
>
> Important information on this library
> This is a custom build of SQLite 3 dynamic link library (dll) for=20
> windows.
>
> WARNING: THIS BUILD CANNOT GUARANTEE SQLite ACID behaviour in case of=20
> OS crash!
>
> Version: Sqlite 3.27 (modified)
> Compiler: dev-c++ 4.9.9.2 (minGW)
> Modifications: Added pragma for disabling file buffer flush on win32=20
> (Rene Tegel)
>
> Usage:
> Set the sqlite pragma win32_flush_buffers to on or off (defaults to on=20
> (safe)):
> PRAGMA win32_flush_buffers=3Doff
>
> Motivation:
> SQLite is very slow and disk-intensive when completing a transaction.=20
> This is mainly causes by calling the windows api FlushFileBuffers=20
> which does, probably, a lot more than intended (flushing file buffer=20
> to disk).
> In case of an application crash, there should be no difference in=20
> ACID. In case of an operating system crash or power failure, the=20
> effects may be noticable.
> However, by disabling buffer flush a transaction is handled about 80=20
> times faster on my system! Benchmarks below.
>
> Recommended use:
> NTFS file systems
> Data logging applications
> Other applications that involve numerous transactions but need=20
> perfarmance.
> Make a backup of your database if you can't risk total data loss but=20
> still need the performance
>
> Important notice:
> I am not resposible for data loss. What is more: Richard Hipp (SQLite=20
> main developer) is not responsible for data loss or any modifications=20
> in this library. Do NOT contach him and/or the sqlite mailing list for=20
> issues caused by flushbuffers switched to off. The rest of the library=20
> should be identical.
>
> Future compilations:
> I will try to patch, compile and release newer versions. After=20
> testing, i may contact the sqlite mailing list and ask Richard Hipp to=20
> include the patch.
>
>
>
> Benchmarks:
> AS tested with sqlite 3.2.7 compiled with dev-c++ on win32
> platform: win XP AMD 2500+
>
>
>
> sqlite327 standard, non-optimized:
>
> inserting 1 records
> Seperate transactions 79 ms
> Single transaction 78 ms
>
> inserting 10 records
> Seperate transactions 672 ms
> Single transaction 79 ms
>
> inserting 100 records
> Seperate transactions 6719 ms
> Single transaction 78 ms
>
> inserting 1000 records
> Seperate transactions 77359 ms
> Single transaction 156 ms
>
> inserting 10000 records
> Seperate transactions 789453 ms
> Single transaction 781 ms
>
>
>
> sqlite3.27 optimized, disabled buffer flush:
>
> inserting 1 records
> Seperate transactions 0 ms
> Single transaction 0 ms
>
> inserting 10 records
> Seperate transactions 16 ms
> Single transaction 0 ms
>
> inserting 100 records
> Seperate transactions 156 ms
> Single transaction 0 ms
>
> inserting 1000 records
> Seperate transactions 969 ms
> Single transaction 78 ms
>
> inserting 10000 records
> Seperate transactions 9828 ms
> Single transaction 594 ms
>
>
>
>
>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussion=
s,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> libsql-discussion mailing list
> lib...@li...
> https://lists.sourceforge.net/lists/listinfo/libsql-discussion

[libsql] New developer

[<re...@du...>]

Hi,

Please welcome Dak as new developer!

Dak has already submitted quite some patches and bugfixes, so he has 
already earned his medals contributing to the project ;)

As usual, he's granted admin rights so he is allowed to commit to cvs, 
create releases, moderate the tracker and forum etc. His sourceforge 
account is dak_alfa.

Rene

Re: [libsql] Query and QueryW

[<re...@du...>]

hi,

like to ask you again.. please download tortoisecvs (or some other cvs 
client), and tell me the name of your sourceforge account. cvs is so 
convenient for this, and i may make less errors merging..

Dak wrote:

>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 
>
>
>-------------------------------------------------------
>This SF.Net email is sponsored by the JBoss Inc.
>Get Certified Today * Register for a JBoss Training Course
>Free Certification Exam for All Training Attendees Through End of 2005
>Visit http://www.jboss.com/services/certification for more information
>_______________________________________________
>libsql-discussion mailing list
>lib...@li...
>https://lists.sourceforge.net/lists/listinfo/libsql-discussion
>  
>

[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 

[libsql] sqlite 80x faster on transactions

[<re...@du...>]

Hi,

Inspired by cross-platform usage of sqlite vs transaction and threading 
issues, i've been looking why sqlite is so slow with transactions on my 
system (XPsp2, amd2500+, 250GB HD, NTFS). A transaction takes about 80ms 
on my system, with lot of disk head movements involved.
Since i need sqlite especially to be fast for one of my projects (speed 
is more important than possible data loss in case of an operating system 
crash) i've been investigating how to speed up sqlite. To accomplish 
that goal, i've recompiled sqlite3.dll and added an extra pragma to 
sqlite that disables disk flushing on each transaction.

If you want to test (please do not use for production at the moment!), 
you can download a modified sqlite dll from sourceforge:
http://sourceforge.net/project/showfiles.php?group_id=103463&package_id=131328

to disable disk flush, set the pragma win32_flush_buffers to off (it 
defaults to on):
PRAGMA win32_flush_buffers=off

below the readme of the zip, including a simple benchmark.

regards,

rene

-------------------------------------

Important information on this library
This is a custom build of SQLite 3 dynamic link library (dll) for windows.

WARNING: THIS BUILD CANNOT GUARANTEE SQLite ACID behaviour in case of OS 
crash!

Version: Sqlite 3.27 (modified)
Compiler: dev-c++ 4.9.9.2 (minGW)
Modifications: Added pragma for disabling file buffer flush on win32 
(Rene Tegel)

Usage:
Set the sqlite pragma win32_flush_buffers to on or off (defaults to on 
(safe)):
PRAGMA win32_flush_buffers=off

Motivation:
SQLite is very slow and disk-intensive when completing a transaction. 
This is mainly causes by calling the windows api FlushFileBuffers which 
does, probably, a lot more than intended (flushing file buffer to disk).
In case of an application crash, there should be no difference in ACID. 
In case of an operating system crash or power failure, the effects may 
be noticable.
However, by disabling buffer flush a transaction is handled about 80 
times faster on my system! Benchmarks below.

Recommended use:
NTFS file systems
Data logging applications
Other applications that involve numerous transactions but need perfarmance.
Make a backup of your database if you can't risk total data loss but 
still need the performance

Important notice:
I am not resposible for data loss. What is more: Richard Hipp (SQLite 
main developer) is not responsible for data loss or any modifications in 
this library. Do NOT contach him and/or the sqlite mailing list for 
issues caused by flushbuffers switched to off. The rest of the library 
should be identical.

Future compilations:
I will try to patch, compile and release newer versions. After testing, 
i may contact the sqlite mailing list and ask Richard Hipp to include 
the patch.



Benchmarks:
AS tested with sqlite 3.2.7 compiled with dev-c++ on win32
platform: win XP AMD 2500+



sqlite327 standard, non-optimized:

inserting 1 records
Seperate transactions 79 ms
Single transaction 78 ms

inserting 10 records
Seperate transactions 672 ms
Single transaction 79 ms

inserting 100 records
Seperate transactions 6719 ms
Single transaction 78 ms

inserting 1000 records
Seperate transactions 77359 ms
Single transaction 156 ms

inserting 10000 records
Seperate transactions 789453 ms
Single transaction 781 ms



sqlite3.27 optimized, disabled buffer flush:

inserting 1 records
Seperate transactions 0 ms
Single transaction 0 ms

inserting 10 records
Seperate transactions 16 ms
Single transaction 0 ms

inserting 100 records
Seperate transactions 156 ms
Single transaction 0 ms

inserting 1000 records
Seperate transactions 969 ms
Single transaction 78 ms

inserting 10000 records
Seperate transactions 9828 ms
Single transaction 594 ms

[libsql] libsql 0.70 testing

[<re...@du...>]

Hi,

On sourceforge i put a new package for testing. It has two features added:

* Support for janSQL - a native object-pascal flat-file database engine. 
janSQL is suitable for small databases only, lacks indexes and data 
types, but has well-featured sql dialect including joins etc. May be 
good choice if you dont want to be dependant on external libraries.

* Experimental class TMLiteDB (M for Multi-threaded, name suggestions 
welcome). A helper thread performs all interaction with the database, 
while various threads may access the same database object, provided they 
do adequate locking. This may help to easify compiling multi-threaded 
applications on linux that use sqlite.

Warning: Both additions are experimental code that need some polishing. 
The rest of the library has not changed since 0.63. Download only if you 
want to experiment with one of the new features.

Download available at:
http://sourceforge.net/project/showfiles.php?group_id=103463&package_id=122533

regards,

Rene

[libsql] Test

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

I test when i am subscrible in this mailing list,
sorry :). 




		
__________________________________ 
Yahoo! Music Unlimited 
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

Re: [libsql] libsql 0.63 released

[<re...@du...>]

Andy Chandler wrote:

>BTW... If you hit reply on one of the the list emails,
>I don't think it's been sending back to the list, but
>the sender personally? It's unusual compared with
>other lists I'm subscribed to.
>  
>
Thanks for notifying that.. I'll see if i can change configuration.

I also noticed the lists accepts mail from unsubsrcibed users, as i 
accidentaly sent a message using wrong sender...

regards,

rene

Re: [libsql] libsql 0.63 released

[Andy C. <and...@bt...>]

Thanks Rene for your hard work - much appreciated.

Rgs,
Andy



BTW... If you hit reply on one of the the list emails,
I don't think it's been sending back to the list, but
the sender personally? It's unusual compared with
other lists I'm subscribed to.



--- René Tegel <ren...@wu...> wrote:

> Hi,
> 
> I've released libsql 0.63. Been working on memory
> leaking issues and 
> resultset freeing.
> 
> regards,
> 
> rene
> 
> =======================================
> 
> changes in 0.63
> * Hunting memory leaks. I believe they are all
> solved now
> * Made TResultSet usage (especially freeing) more
> proof
> 
> Here is code of allowed methods of querying and
> result set usage:
>     //test main constructor / destructor:
>     db := TLiteDB.Create(nil, ':memory:');
>     db.Free;
> 
>     //basic query on DB
>     db := TLiteDB.Create(nil, ':memory:');
>     db.FormatQuery('select %d+%d', [1,1]);
>     db.Free;
> 
>     //basic formatquery on DB
>     db := TLiteDB.Create(nil, ':memory:');
>     db.FormatQuery('select %d+%d', [1,1]);
>     writeln (db.Results[0][0]);
>     db.Free;
> 
>     //Per row fetching using execute method using DB
>     db := TLiteDB.Create(nil, ':memory:');
>     h := db.Execute ('select 1+1');
>     while db.FetchRow(h, row) do
>       writeln (row[0]);
>     db.FreeResult(h);
>     db.Free;
> 
>     //Per row fetching using formatexecute method
> using DB
>     db := TLiteDB.Create(nil, ':memory:');
>     h := db.FormatExecute ('select %d + %d', [1,
> 1]);
>     while db.FetchRow(h, row) do
>       writeln (row[0]);
>     db.FreeResult(h);
>     db.Free;
> 
>     //Result set testing:
>     db := TLiteDB.Create(nil, ':memory:');
>     rs := db.UseResultSet('testing');
>     rs.Query('select 1+1');
>     writeln (rs.Row[0][0]);
>     db.Free; //db should free the result set
> 
>     db := TLiteDB.Create(nil, ':memory:');
>     rs := db.UseResultSet('testing');
>     rs.Query('select 1+1');
>     writeln (rs.Row[0][0]);
>     rs.Free; //db should not complain about
> resultset being freed
>     db.Free;
> 
>     db := TLiteDB.Create(nil, ':memory:');
>     rs := TResultSet.Create(db);
>     rs.Query('select 1+1');
>     writeln (rs.Row[0][0]);
>     db.Free; //db does _not_ free the result set
> since it was manually 
> created
>     rs.Free;
> 
>     db := TLiteDB.Create(nil, ':memory:');
>     rs := TResultSet.Create(db);
>     rs.FormatQuery('select %d + %d', [1,1]);
>     writeln (rs.Row[0][0]);
>     db.Free;
> 
>     //double usage of resultset
>     db := TLiteDB.Create(nil, ':memory:');
>     db.UseResultSet(rs);
>     rs.FormatQuery('select %d + %d', [1,1]);
>     writeln (rs.Row[0][0]);
>     db.Free;
>     rs.Free;
> 
>     //row fetching using a TResultSet
>     db := TLiteDB.Create(nil, ':memory:');
>     rs := db.UseResultSet('rowfetch');
>     if rs.Execute ('select 1+1') then
>       while rs.FetchRow do
>         writeln (rs.Fetched[0]);
>     rs.FreeResult;
>     db.Free;
> 
> 
> 
> changes in 0.62
> * sqlite was performing an 'unlock' in
> Rollback[transaction] (Dak?)
> * base class (mysql/odbc) still had locks on
> database transactions. 
> Removed. Mysql/odbc users beware,
> starting/comitting/rollback a 
> transaction no longer locks the thread. You will
> have to lock/unlock 
> threads using respective methods yourself.
> * added constant LibSQLVersion (String type, now:
> '0.62') in base class. 
> (Dak)
> * DateTime support in TResultCell
> * Added prorperty ValidValue to a resultcell to
> differate between db 
> results and 'out-of-range' or nen-existent result.
> * Added method 'FieldExists' to TResultRow to
> determinate a particulair 
> field name exists.
> * OnClose handler for sqlite
> * Fixed reference counting bug in sqlite (multiple
> instances accessing 
> the same database)
> * Added method 'RefreshDBInfo' that calls FillDBInfo
> * Added private 'AutoFree' varibale to TResultSet.
> Freeing a DB will not 
> automatically free all associated resultsets that
> were created 
> 'stand-alone', (calling "DB := TnnnDB.Create(); rs
> := 
> TResultSet.Create(DB); DB.Free; rs.free", in this
> order, could result in 
> a AV error due to double freeing.).
> * Added package file for convenience.
> * Renamed all occurences of 'Owner' in TResultSet to
> 'SQLDB' to make 
> things same more clear.
> 
> 
> 
> 
> changes in 0.61
> *Implemented virtual implementations in base class
> of some newly added 
> methods for sqlite and/or mysql. ODBC class did not
> make any 
> implementation of this virtual (and abstract) class.
> Possibly having 
> unimplemented abstract classes leads to conflicts on
> some compilers 
> (kylix, freepascal?).
> *No other changes since 0.60
> 
> changes in 0.60
> 
> This happen to be "stable" version. On my system. No
> guarantees.
> * The SQLITE transaction mechanism has changed a
> little. A transaction 
> no longer involves an obliged thread lock. Threads
> can share the same 
> file handle safely on windows systems.
> * Removed a confusing property from the TResultSet
> class.
> * Merged more support functions by PChev: flush,
> truncate, lock, vacuum. Tx!
> * SQLite best tested currently. MySQL support
> without real problems. 
> Most recent libmysql.dll ( version 5.0.n) supported.
> * The components may use some attention. So does the
> documentation. 
> Volunteer welcome!
> * It is not multi-platform tested. Notify me of new
> issues please. Not 
> much changes since last testing.
> * Next release will contain additional sqlite class
> that provides a 
> threading mechanism to allow multiple threads on
> some unix systems 
> redirected to a single thread for the file system to
> work correctly.
> * Some minor bug fixes.
> 
> changes in 0.57
> * formatquery adjustments - floating point issues
> * result set freeing (fixes shutdown error)
> 
> changes in 0.56
> * result set usage, issues
> 
> Changes in 0.55
> * added delphi4 compatability to sqlite units
> * improved result set usage
> * speeded up resultset lookup some
> * some bugfixes and issues
> * fixed execute/fetchrow functionality for sqlite 3
> * added execute/fetchrow functionality to TResultSet
> (!)
> 
=== message truncated ===

[libsql] libsql 0.63 released

[<re...@du...>]

Hi,

I've released libsql 0.63. Been working on memory leaking issues and 
resultset freeing.

regards,

rene

=======================================

changes in 0.63
* Hunting memory leaks. I believe they are all solved now
* Made TResultSet usage (especially freeing) more proof

Here is code of allowed methods of querying and result set usage:
   //test main constructor / destructor:
   db := TLiteDB.Create(nil, ':memory:');
   db.Free;

   //basic query on DB
   db := TLiteDB.Create(nil, ':memory:');
   db.FormatQuery('select %d+%d', [1,1]);
   db.Free;

   //basic formatquery on DB
   db := TLiteDB.Create(nil, ':memory:');
   db.FormatQuery('select %d+%d', [1,1]);
   writeln (db.Results[0][0]);
   db.Free;

   //Per row fetching using execute method using DB
   db := TLiteDB.Create(nil, ':memory:');
   h := db.Execute ('select 1+1');
   while db.FetchRow(h, row) do
     writeln (row[0]);
   db.FreeResult(h);
   db.Free;

   //Per row fetching using formatexecute method using DB
   db := TLiteDB.Create(nil, ':memory:');
   h := db.FormatExecute ('select %d + %d', [1, 1]);
   while db.FetchRow(h, row) do
     writeln (row[0]);
   db.FreeResult(h);
   db.Free;

   //Result set testing:
   db := TLiteDB.Create(nil, ':memory:');
   rs := db.UseResultSet('testing');
   rs.Query('select 1+1');
   writeln (rs.Row[0][0]);
   db.Free; //db should free the result set

   db := TLiteDB.Create(nil, ':memory:');
   rs := db.UseResultSet('testing');
   rs.Query('select 1+1');
   writeln (rs.Row[0][0]);
   rs.Free; //db should not complain about resultset being freed
   db.Free;

   db := TLiteDB.Create(nil, ':memory:');
   rs := TResultSet.Create(db);
   rs.Query('select 1+1');
   writeln (rs.Row[0][0]);
   db.Free; //db does _not_ free the result set since it was manually 
created
   rs.Free;

   db := TLiteDB.Create(nil, ':memory:');
   rs := TResultSet.Create(db);
   rs.FormatQuery('select %d + %d', [1,1]);
   writeln (rs.Row[0][0]);
   db.Free;

   //double usage of resultset
   db := TLiteDB.Create(nil, ':memory:');
   db.UseResultSet(rs);
   rs.FormatQuery('select %d + %d', [1,1]);
   writeln (rs.Row[0][0]);
   db.Free;
   rs.Free;

   //row fetching using a TResultSet
   db := TLiteDB.Create(nil, ':memory:');
   rs := db.UseResultSet('rowfetch');
   if rs.Execute ('select 1+1') then
     while rs.FetchRow do
       writeln (rs.Fetched[0]);
   rs.FreeResult;
   db.Free;



changes in 0.62
* sqlite was performing an 'unlock' in Rollback[transaction] (Dak?)
* base class (mysql/odbc) still had locks on database transactions. 
Removed. Mysql/odbc users beware, starting/comitting/rollback a 
transaction no longer locks the thread. You will have to lock/unlock 
threads using respective methods yourself.
* added constant LibSQLVersion (String type, now: '0.62') in base class. 
(Dak)
* DateTime support in TResultCell
* Added prorperty ValidValue to a resultcell to differate between db 
results and 'out-of-range' or nen-existent result.
* Added method 'FieldExists' to TResultRow to determinate a particulair 
field name exists.
* OnClose handler for sqlite
* Fixed reference counting bug in sqlite (multiple instances accessing 
the same database)
* Added method 'RefreshDBInfo' that calls FillDBInfo
* Added private 'AutoFree' varibale to TResultSet. Freeing a DB will not 
automatically free all associated resultsets that were created 
'stand-alone', (calling "DB := TnnnDB.Create(); rs := 
TResultSet.Create(DB); DB.Free; rs.free", in this order, could result in 
a AV error due to double freeing.).
* Added package file for convenience.
* Renamed all occurences of 'Owner' in TResultSet to 'SQLDB' to make 
things same more clear.




changes in 0.61
*Implemented virtual implementations in base class of some newly added 
methods for sqlite and/or mysql. ODBC class did not make any 
implementation of this virtual (and abstract) class. Possibly having 
unimplemented abstract classes leads to conflicts on some compilers 
(kylix, freepascal?).
*No other changes since 0.60

changes in 0.60

This happen to be "stable" version. On my system. No guarantees.
* The SQLITE transaction mechanism has changed a little. A transaction 
no longer involves an obliged thread lock. Threads can share the same 
file handle safely on windows systems.
* Removed a confusing property from the TResultSet class.
* Merged more support functions by PChev: flush, truncate, lock, vacuum. 
Tx!
* SQLite best tested currently. MySQL support without real problems. 
Most recent libmysql.dll ( version 5.0.n) supported.
* The components may use some attention. So does the documentation. 
Volunteer welcome!
* It is not multi-platform tested. Notify me of new issues please. Not 
much changes since last testing.
* Next release will contain additional sqlite class that provides a 
threading mechanism to allow multiple threads on some unix systems 
redirected to a single thread for the file system to work correctly.
* Some minor bug fixes.

changes in 0.57
* formatquery adjustments - floating point issues
* result set freeing (fixes shutdown error)

changes in 0.56
* result set usage, issues

Changes in 0.55
* added delphi4 compatability to sqlite units
* improved result set usage
* speeded up resultset lookup some
* some bugfixes and issues
* fixed execute/fetchrow functionality for sqlite 3
* added execute/fetchrow functionality to TResultSet (!)
* TResultset has extra property: Fetched

0.55 is release candidate for 0.60. This time i really do not plan 
additions to functionality.
0.55 seems relative stable to me, all functionality is ok. what bothers 
me a tiny bit is that resulset handling has got a little more complex.
The db class keeps track of used result sets. Things may both speed up 
and get little more complex if we skip that functionality.
TResultSet has now almost all methods that originally were found in db. 
While both ways will work, there are now many ways how to perform a query.
however, i think i favourize giving TResultSet a central (obliged?) 
place.But doing so, would remove 'ease-of-use' of the db component itself.
Anyhow, stuff is working ok, it is nicely put together, i don't see any 
major mistakes but as said, complexity of code may make things less 
maintainable.

[libsql] libsql 0.63 released

[<ren...@wu...>]

Hi,

I've released libsql 0.63. Been working on memory leaking issues and 
resultset freeing.

regards,

rene

=======================================

changes in 0.63
* Hunting memory leaks. I believe they are all solved now
* Made TResultSet usage (especially freeing) more proof

Here is code of allowed methods of querying and result set usage:
    //test main constructor / destructor:
    db := TLiteDB.Create(nil, ':memory:');
    db.Free;

    //basic query on DB
    db := TLiteDB.Create(nil, ':memory:');
    db.FormatQuery('select %d+%d', [1,1]);
    db.Free;

    //basic formatquery on DB
    db := TLiteDB.Create(nil, ':memory:');
    db.FormatQuery('select %d+%d', [1,1]);
    writeln (db.Results[0][0]);
    db.Free;

    //Per row fetching using execute method using DB
    db := TLiteDB.Create(nil, ':memory:');
    h := db.Execute ('select 1+1');
    while db.FetchRow(h, row) do
      writeln (row[0]);
    db.FreeResult(h);
    db.Free;

    //Per row fetching using formatexecute method using DB
    db := TLiteDB.Create(nil, ':memory:');
    h := db.FormatExecute ('select %d + %d', [1, 1]);
    while db.FetchRow(h, row) do
      writeln (row[0]);
    db.FreeResult(h);
    db.Free;

    //Result set testing:
    db := TLiteDB.Create(nil, ':memory:');
    rs := db.UseResultSet('testing');
    rs.Query('select 1+1');
    writeln (rs.Row[0][0]);
    db.Free; //db should free the result set

    db := TLiteDB.Create(nil, ':memory:');
    rs := db.UseResultSet('testing');
    rs.Query('select 1+1');
    writeln (rs.Row[0][0]);
    rs.Free; //db should not complain about resultset being freed
    db.Free;

    db := TLiteDB.Create(nil, ':memory:');
    rs := TResultSet.Create(db);
    rs.Query('select 1+1');
    writeln (rs.Row[0][0]);
    db.Free; //db does _not_ free the result set since it was manually 
created
    rs.Free;

    db := TLiteDB.Create(nil, ':memory:');
    rs := TResultSet.Create(db);
    rs.FormatQuery('select %d + %d', [1,1]);
    writeln (rs.Row[0][0]);
    db.Free;

    //double usage of resultset
    db := TLiteDB.Create(nil, ':memory:');
    db.UseResultSet(rs);
    rs.FormatQuery('select %d + %d', [1,1]);
    writeln (rs.Row[0][0]);
    db.Free;
    rs.Free;

    //row fetching using a TResultSet
    db := TLiteDB.Create(nil, ':memory:');
    rs := db.UseResultSet('rowfetch');
    if rs.Execute ('select 1+1') then
      while rs.FetchRow do
        writeln (rs.Fetched[0]);
    rs.FreeResult;
    db.Free;



changes in 0.62
* sqlite was performing an 'unlock' in Rollback[transaction] (Dak?)
* base class (mysql/odbc) still had locks on database transactions. 
Removed. Mysql/odbc users beware, starting/comitting/rollback a 
transaction no longer locks the thread. You will have to lock/unlock 
threads using respective methods yourself.
* added constant LibSQLVersion (String type, now: '0.62') in base class. 
(Dak)
* DateTime support in TResultCell
* Added prorperty ValidValue to a resultcell to differate between db 
results and 'out-of-range' or nen-existent result.
* Added method 'FieldExists' to TResultRow to determinate a particulair 
field name exists.
* OnClose handler for sqlite
* Fixed reference counting bug in sqlite (multiple instances accessing 
the same database)
* Added method 'RefreshDBInfo' that calls FillDBInfo
* Added private 'AutoFree' varibale to TResultSet. Freeing a DB will not 
automatically free all associated resultsets that were created 
'stand-alone', (calling "DB := TnnnDB.Create(); rs := 
TResultSet.Create(DB); DB.Free; rs.free", in this order, could result in 
a AV error due to double freeing.).
* Added package file for convenience.
* Renamed all occurences of 'Owner' in TResultSet to 'SQLDB' to make 
things same more clear.




changes in 0.61
*Implemented virtual implementations in base class of some newly added 
methods for sqlite and/or mysql. ODBC class did not make any 
implementation of this virtual (and abstract) class. Possibly having 
unimplemented abstract classes leads to conflicts on some compilers 
(kylix, freepascal?).
*No other changes since 0.60

changes in 0.60

This happen to be "stable" version. On my system. No guarantees.
* The SQLITE transaction mechanism has changed a little. A transaction 
no longer involves an obliged thread lock. Threads can share the same 
file handle safely on windows systems.
* Removed a confusing property from the TResultSet class.
* Merged more support functions by PChev: flush, truncate, lock, vacuum. Tx!
* SQLite best tested currently. MySQL support without real problems. 
Most recent libmysql.dll ( version 5.0.n) supported.
* The components may use some attention. So does the documentation. 
Volunteer welcome!
* It is not multi-platform tested. Notify me of new issues please. Not 
much changes since last testing.
* Next release will contain additional sqlite class that provides a 
threading mechanism to allow multiple threads on some unix systems 
redirected to a single thread for the file system to work correctly.
* Some minor bug fixes.

changes in 0.57
* formatquery adjustments - floating point issues
* result set freeing (fixes shutdown error)

changes in 0.56
* result set usage, issues

Changes in 0.55
* added delphi4 compatability to sqlite units
* improved result set usage
* speeded up resultset lookup some
* some bugfixes and issues
* fixed execute/fetchrow functionality for sqlite 3
* added execute/fetchrow functionality to TResultSet (!)
* TResultset has extra property: Fetched

0.55 is release candidate for 0.60. This time i really do not plan 
additions to functionality.
0.55 seems relative stable to me, all functionality is ok. what bothers 
me a tiny bit is that resulset handling has got a little more complex.
The db class keeps track of used result sets. Things may both speed up 
and get little more complex if we skip that functionality.
TResultSet has now almost all methods that originally were found in db. 
While both ways will work, there are now many ways how to perform a query.
however, i think i favourize giving TResultSet a central (obliged?) 
place.But doing so, would remove 'ease-of-use' of the db component itself.
Anyhow, stuff is working ok, it is nicely put together, i don't see any 
major mistakes but as said, complexity of code may make things less 
maintainable.

[libsql] UPDATE - ok

[<c.s...@gm...>]

Hi,

found a solution for my UPDATE-question:

procedure ChangeData;
var
  db: TLiteDB;
  name: string;
begin
  try
    db := TLiteDB.Create(Form1);
    db.Use('database.sdb');

    entry    := Form1.RichEditInformation.Text;

    querystring:= 'UPDATE tablename SET Name = ' + #39 + entry + #39 + ' 

    db.Query(querystring);
  finally
    db.Free;
  end;
end;

Thanks,
Christoph

-- 
Highspeed-Freiheit. Bei GMX supergünstig, z.B. GMX DSL_Cityflat,
DSL-Flatrate für nur 4,99 Euro/Monat*  http://www.gmx.net/de/go/dsl

[libsql] UPDATE

[Christoph S. <c.s...@gm...>]

Hi,

a question about UPDATE.
What is the syntax for updating the database?

db.Query('UPDATE tablename SET colum_x = new_entry where column_name = xyz')

does not work. Or is it db.FormatQuery?

Thanks,
Christoph


mailto:c.s...@gm...

[libsql] Freeing Items

[Andy C. <and...@bt...>]

Hi Rene,

  Now I've written the SQL heavy sections of my project, I noticed
that my exe was leaking memory slowy.  I think I got all my mistakes,
but there were still bytes leaking.

Upon investigation using Memproof and Windows TaskManager, there is
some leaks on the most basic SQL statements, which is likely to be me.

This piece of code is as small of a section I could create on it's own
that generates around 24 bytes each time it's called.....



procedure TForm1.Button1Click(Sender: TObject);
var
  row    : TResultRow;
  handle : integer;
begin
  handle:=db.FormatExecute('SELECT name FROM temp WHERE name=%s',['test']);
  db.FreeResult(handle);
end;


Is there anything else that needs to be free after a Query that I've
missed? A standard db.query doesn't generate the increase, but FormatExecute does.
After doing 10s of 0000's of queries, the RAM required by the
application gets significant.

Cheers.

[libsql] libsql 0.62

[<re...@du...>]

is now available at sourceforge
http://sf.net/projects/libsql/files

changes in 0.62
* sqlite was performing an 'unlock' in Rollback[transaction] (Dak?)
* base class (mysql/odbc) still had locks on database transactions. 
Removed. Mysql/odbc users beware, starting/comitting/rollback a 
transaction no longer locks the thread. You will have to lock/unlock 
threads using respective methods yourself.
* added constant LibSQLVersion (String type, now: '0.62') in base class. 
(Dak)
* DateTime support in TResultCell
* Added prorperty ValidValue to a resultcell to differate between db 
results and 'out-of-range' or nen-existent result.
* Added method 'FieldExists' to TResultRow to determinate a particulair 
field name exists.
* OnClose handler for sqlite
* Fixed reference counting bug in sqlite (multiple instances accessing 
the same database)
* Added method 'RefreshDBInfo' that calls FillDBInfo
* Added private 'AutoFree' varibale to TResultSet. Freeing a DB will not 
automatically free all associated resultsets that were created 
'stand-alone', (calling "DB := TnnnDB.Create(); rs := 
TResultSet.Create(DB); DB.Free; rs.free", in this order, could result in 
a AV error due to double freeing.).
* Added package file for convenience.
* Renamed all occurences of 'Owner' in TResultSet to 'SQLDB' to make 
things same more clear.


regards,

rene