Hi,

I want attach second databse to sqlite connection by command:

DBConnection.ExecuteDirect('attach database
'+QuotedStr(GetConfigDir+'second.db')+' as mydb2');

But I get error that cannot attach database when on transaction. Even If I commit transaction first, then ExecuteDirect start new one. How to get around this?

BTW: Why SQLDB has logic "always in transaction"? Is it really perform in large and complex ERP systems? What I was always taught that the transaction should be used only in specific situtations (importing, converting documents, FIFO, stock status etc) but whole time? Is not dangerous? One programmer's mistake (e.g forget commit somewhere, or transaction conflict etc.) and client can loose bunch of data.

Regards
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130107/72e47e2b/attachment.html>

There is currently no way.

BTW: Why SQLDB has logic "always in transaction"? Is it really perform in large and complex ERP systems??What I was always taught that the transaction should be used only in specific situtations (importing, converting documents, FIFO, stock status etc) but whole time??Is not dangerous??One programmer's mistake?(e.g forget commit somewhere, or transaction conflict etc.) and client can loose bunch of data.

This is the world upside down :)

A transaction is a basic cornerstone of any RDBMS. You must always take care of them.

Many RDBMS toolkits try to 'simplify' your life by adding some automated transaction handling which hides the details from you.

(whether this is good or not is another debate)

SQLDB currently does not do this. You must take full responsability for the transactions.

Michael.

Ok I found a hack. I created own TMyConnection = class(TSQLite3Connection) so I have access to protected execsql which don't check transaction:

type
  { TMyConnection }
  TMyConnection = class(TSQLite3Connection)
  public
    procedure ExecSQLNoTrans(const ASql: String);
  end;
implementation
procedure TMyConnection.ExecSQLNoTrans(const ASql: String);
begin
  execsql(ASql);
end;

And now I can attach my second database by calling:

    DBConnection.ExecSQLNoTrans('attach database
'+QuotedStr(GetConfigDir+'second.db')+' as mydb2');

Each system can be broken ;)

Regards
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130107/01c03a5b/attachment.html>

Maybe we should make a method for this in TSQLite3Connection ?

Michael.

2013/1/7 Michael Van Canneyt

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) -------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130107/12ceb3af/attachment.html>

I meant a method
Procedure AttachDatabase(Const AFileName, AnAlias : String); ?

Other methods can also be added, of course.

Michael.

2013/1/7 Michael Van Canneyt

It's even better. I see that some wrappers already exists (GetFieldNames, GetInsertID). This is a good idea to go this way
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130107/e47648ce/attachment.html>

A method to execute a statement outside a transaction would also be useful for the following reasons:
- 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. Just take a look at the long list of sqlite PRAGMA commands that, for some, can't be executed inside a transaction. On top of that the list changes considerably between the different databases and the confusion between the different db method variations can become very big. Fe in SQLServer you can also attach a database with a SQL statement but the parameters are quite different : http://msdn.microsoft.com/en-us/library/ms176061.aspx.
- A lot of these commands are non SQL standard and have been introduced over time which raises also the question of version compatibility when implementing sqldb methods to support them.

Ludo

That will have to be database-specific.

For example Firebird does not allow you to execute statements "outside a transaction".

Why not ?

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

Michael.

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.

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

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

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 ?

Eh ? That is the same for the bypass ?

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.

As far as I understand it: the bypass could "immediately" be used for any affacted SQL command once trunk is released (or the fixes version with that bypass applied). If however in the other approach some commands are forgotten the user has to wait again until the next version is released.

Regards,
Sven

Ah yes.

Good point, had not though of that. Well, let's add the bypass then. Just need to figure out what to do with Firebird, since it needs transactions. Probably temporarily creating one and committing it at the end will do.

Michael.

Yes it does, using execute immediate or execute block or something, iirc. Though yes, the execute block does need to run in some sort of transaction, but the action inside the block is committed regardless of any transaction rollbacks.
Useful for logging...
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20130108/d2796cfc/attachment.html>

Personally I disagree, that behavior is exactly the opposite of what the method name imply and I would simply raise an exception instead with a message of : firebird does not support out of transaction commands, or something along those lines also I would like to propose an other alternative, Instead of adding one more method I would extend the existing one with an extra parameter Transaction:TSQLTransaction if the parameter is nil then it will run outside of a transaction if supported by the database or raise an exception.

That's my 2 cents.

Regards
Jo.

MSEgui transaction component has an option "tao_fake" which can be used in situations where statements should run in an implicit transaction.

Martin

Memory was not completely correct again: the term is autonomous transactions:
http://www.firebirdsql.org/refdocs/langrefupd25-psql-autonomous-trans.html

Reinier