Forum > Databases
[Solved]CREATE FULLTEXT CATALOG statement cannot be used inside user transaction
Zvoni:
Create a Transaction and assign it to the Connection, but don't start it
You will have to go step by step to find the culprit
ALAU2007:
Hi Zvoni,
I have initial the transaction and test
procedure TForm1.Button2Click(Sender: TObject);
var
Connection: TMSSQLConnection;
Transaction: TSQLTransaction;
begin
Connection := TMSSQLConnection.Create(nil);
Transaction := TSQLTransaction.Create(nil);
try
Connection.HostName := 'hostname';
Connection.DatabaseName := 'test1';
Connection.UserName := 'sa';
Connection.Password := 'password';
Connection.Transaction := Transaction;
Connection.ExecuteDirect('CREATE FULLTEXT CATALOG MyFullTextCatalog;');
finally
Connection.Free;
end;
ShowMessage( 'OK');
end;
It shows: CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
TRon:
@zvoni:
I know you have a better understanding about databases than I have but could it be a permission issue that the server isn't configured to grant the (this) user to perform full-text catalogs (In which case I would have expected a denied error or similar)
Zvoni:
--- Quote from: TRon on August 13, 2024, 09:08:21 am ---@zvoni:
I know you have a better understanding about databases than I have but could it be a permission issue that the server isn't configured to grant the (this) user to perform full-text catalogs (In which case I would have expected a denied error or similar)
--- End quote ---
Hmmm....you might be right: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-catalog-transact-sql?view=sql-server-ver16
--- Quote ---Permissions
User must have CREATE FULLTEXT CATALOG permission on the database, or be a member of the db_owner, or db_ddladmin fixed database roles.
--- End quote ---
But wouldn't explain, why it complains about a User-Transaction
Just saw it:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---begin Connection := TMSSQLConnection.Create(nil); try Connection.HostName := 'hostname'; Connection.DatabaseName := 'test1'; Connection.UserName := 'sa'; Connection.Password := 'password'; //Connection.Open; //missing? Connection.ExecuteDirect('CREATE FULLTEXT CATALOG MyFullTextCatalog;', nil); finallyHuh? Where do you open the Connection?
EDIT: Just looked at the Source of TMSSQLConnection:
There is a private Method "ExecuteDirectSQL" resp. (overloaded) "Execute" (This one needs an explicit "Open")
Use that one without an assigned Transaction.
Permissions (see above) must be correct
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---begin Connection := TMSSQLConnection.Create(nil); try Connection.HostName := 'hostname'; Connection.DatabaseName := 'test1'; Connection.UserName := 'sa'; Connection.Password := 'password'; Connection.ExecuteDirectSQL('CREATE FULLTEXT CATALOG MyFullTextCatalog;'); //ExecuteDirectSQL calls "open" before calling "Execute" finallyor
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---begin Connection := TMSSQLConnection.Create(nil); try Connection.HostName := 'hostname'; Connection.DatabaseName := 'test1'; Connection.UserName := 'sa'; Connection.Password := 'password'; Connection.Open; Connection.Execute('CREATE FULLTEXT CATALOG MyFullTextCatalog;'); finally
EDIT: Doesn't work since private *growl*
Change scope of Private to Public???
EDIT: Right.
Currently, the only way forward i see (won't mind alternative ideas) is to write a class helper, where basically you copy/paste the source code from "Execute".
Further analysis of the source-code not withstanding (calling any other private/protected methods --> "Execute" and "CheckDisConnected" e.g.)
EDIT: I think i remember having similiar issues with SQLite giving me grief when trying to execute a PRAGMA after opening the Database.
I think it was even the same Error-Message.
The Solution, too, was a Class-Helper where within i used the lowlevel calls
EDIT: Looked at it further: The lowlevel-calls require FDBProc, which is a private field, which gets assigned a Pointer-Value during Login/Connect.
Again, can only be retrieved with protected "GetHandle" *sigh*.
Damn it!!
Zvoni:
Right, after more thinking about....
Just as a TEST:
Make a backup-copy of mssqlconn.pp in
C:\lazarus\fpc\{Version}\source\packages\fcl-db\src\sqldb\mssql (Or whatever your installation-path is)
Change/Move the Private Procedure "procedure Execute(const cmd: string); overload;" (in my installation in Line 66)
to the Public-Section.
Save it as "mssqlconn_v2.pp" (or whatever filename you want to use.
Add it to your project.
add it in Uses-Clause "mssqlconn_v2"
test with (Permissions must be granted to your DB-user - see above)
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- begin Connection := TMSSQLConnection.Create(nil); try Connection.HostName := 'hostname'; Connection.DatabaseName := 'test1'; Connection.UserName := 'sa'; Connection.Password := 'password'; Connection.Open; Connection.Execute('CREATE FULLTEXT CATALOG MyFullTextCatalog;'); finally
and report back
Navigation
[0] Message Index
[#] Next page
[*] Previous page