[Lazarus] SQLDB - Can't attach second SQLite database because transaction

[Michael Van Canneyt]

On Mon, 7 Jan 2013, Ludo Brands wrote:

> On 07/01/2013 17:23, Michael Van Canneyt wrote:
>> 
>> 
>> On Mon, 7 Jan 2013, Ludo Brands wrote:
>> 
>>> On 07/01/2013 16:01, Michael Van Canneyt wrote:
>>>> 
>>>> 
>>>> On Mon, 7 Jan 2013, Krzysztof wrote:
>>>> 
>>>>> 
>>>>> 
>>>>> 
>>>>> 2013/1/7 Michael Van Canneyt <michael at freepascal.org>
>>>>> 
>>>>> Maybe we should make a method for this in TSQLite3Connection ?
>>>>> 
>>>>> 
>>>>> It would be great. Because without this we can't use all opportunities 
>>>>> of SQLite, like attaching databases or also VACUUM (trim and pack 
>>>>> database)
>>>> 
>>>> I meant a method Procedure AttachDatabase(Const AFileName, AnAlias : 
>>>> String);
>>>> ?
>>>> 
>>>> Other methods can also be added, of course.
>>>> 
>>> A method to execute a statement outside a transaction would also be useful 
>>> for the following reasons:
>> 
>> That will have to be database-specific.
>> 
>> For example Firebird does not allow you to execute statements "outside a 
>> transaction".
> Firebird starts the transaction implicitly when the transaction hasn't 
> started. So a user that uses this new method has to manually commit or 
> rollback.

I don't follow ?

> Firebird has virtually no sql commands for maintenance or system 
> administration which explains why all SQL statements are running inside a 
> transaction.

Ah ? Can you name a few ?

>>> - Some statements end implicitly a transaction. Ex: 
>>> http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html or all oracle 
>>> DDL statements. sqldb is not aware that the transaction is terminated and 
>>> its state doesn't match the db state.
>>> - You can't make a method for every database command that has to run 
>>> outside a transaction.
>> 
>> Why not ?
>> 
> Of course you can when you have all the time of the world. A method that 
> bypasses sql transaction control covers all cases once it is released. Any 
> custom connector method is trunk first and then wait for next release to get 
> it "production ready".

Eh ? That is the same for the bypass ?

>
>> If they are implemented in the specific connections, I do not see what 
>> problem there could be with that. Such methods already exist. People 
>> relying on such constructs should be aware that they are database-specific.
>> 
>> Problems arise only if you try to elevate some methods to TSQLConnection 
>> for something that is not supported by all databases...
>> 
> Yep. That is exactly what is happening now with the statements that 
> implicitly terminate transactions without sqldb knowing about it.

It would be the same if you had only 1 transaction per connection ?
No single client solution can handle this, unless it knows exactly 
which statements cause this implicit transaction commit ?

Michael.