I'm porting a Delphi program which used Ado components to drop/restore/rename a database. I'm busy moving this over to Lazarus, where I'm using ODBC to connect to a MSSQL localdb instance. I am trying to restore a database from file, followed by an alter statement to rename the database. The alter statement raises an error:
Database X cannot be opened. It is in the middle of a restore;My suspicion is that the restore operation runs asynchronous, so that the server is still busy with the restore operation when the next statement is executed. Not sure how to wait until the restore is completed before executing the next statement - this was not an issue when I used Delphi's Ado components. Any ideas?
For reference the bit of code causing the error:
SqlTransaction.EndTransaction;
ODBCConnection.Close();
ODBCConnection.Params.Values['AUTOCOMMIT'] := '1';
ODBCConnection.Connected := true;
SqlTransaction.StartTransaction;
ODBCConnection.ExecuteDirect('restore database '+InternalUnifacName+
' from disk = ''' + UnifacDBFile +
''' with replace , move '''+InternalUnifacName+
''' to ''' + APEDpath + InternalUnifacName+'.mdf'' , move '''+
InternalUnifacName+'_log'' to ''' +
APEDpath + InternalUnifacName + '_log.ldf''');
SqlTransaction.Commit;
Memo1.Lines.Add('Restore completed.');
ODBCConnection.EndTransaction;
ODBCConnection.Close();
ODBCConnection.Connected := true;
ODBCConnection.StartTransaction;
ODBCConnection.ExecuteDirect('alter database '+InternalUnifacName+' modify name = '+ UnifacTargetName);/code]