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

[Ludo Brands]

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.
Firebird has virtually no sql commands for maintenance or system 
administration which explains why all SQL statements are running inside 
a transaction.

>
>> - 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".

> 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. 
Firebird behavior has been elevated to TSQLConnection and it is not 
working correctly for all databases.

Ludo