[Solved]CREATE FULLTEXT CATALOG statement cannot be used inside user
transaction
lazarus / 16-Databases

----------------------------------------------------------------------
ALAU2007 · 2024-08-09 09:50

Hi,

I want to CREATE FULLTEXT CATALOG within the program.  It shows

TMSSQL Connection: Error 574:
CREATE FULLTEXT CATALOG statement cannot be used inside a user
transaction. 

uses
  SQLDB, MSSQLConn, .....

procedure TForm1.Button1Click(Sender: TObject);    
var
  Connection: TMSSQLConnection;
  Transaction: TSQLTransaction;
  Query: TSQLQuery;
begin
    Connection := TMSSQLConnection.Create(nil);
    Transaction := TSQLTransaction.Create(nil);
    Query := TSQLQuery.Create(nil);
    try
      Connection.HostName := 'hostname';
      Connection.DatabaseName := 'test1';
      Connection.UserName := 'sa';
      Connection.Password := 'password';
      Connection.Transaction := Transaction;
      Connection.Params.Add( 'AutoCommit=on');
      Transaction.DataBase := Connection;
      Query.DataBase := Connection;
      Connection.Open;
      Transaction.Commit;
      Transaction.StartTransaction;
      Query.SQL.Text := 'CREATE FULLTEXT CATALOG MyFullTextCatalog;';
      Query.ExecSQL;
    finally
        Query.Free;
        Transaction.Free;
        Connection.Free;
    end;
end;

Please help.

----------------------------------------------------------------------
Zvoni · 2024-08-09 10:03

Don't start a Transaction?

----------------------------------------------------------------------
ALAU2007 · 2024-08-12 02:26

Thanks Zvoni.  I have remarked the statement to test
   
    // Transaction.StartTransaction;

It still has such error.  Please advise.  Thanks.

----------------------------------------------------------------------
Zvoni · 2024-08-12 06:04

As a test: Only create the connection (No Transaction and no
TSQLQuery), and then use ExecuteDirect to check if the error still
occurs.
https://www.freepascal.org/docs-
html/fcl/sqldb/tsqlconnection.executedirect.html

----------------------------------------------------------------------
ALAU2007 · 2024-08-12 06:38

Hi Zvoni,

I have modified my testing as:

procedure TForm1.Button2Click(Sender: TObject);
var
  Connection: TMSSQLConnection;
begin
    Connection := TMSSQLConnection.Create(nil);
    try
      Connection.HostName := 'hostname';
      Connection.DatabaseName := 'test1';
      Connection.UserName := 'sa';
      Connection.Password := 'password';
      Connection.ExecuteDirect('CREATE FULLTEXT CATALOG
MyFullTextCatalog;', nil);
    finally
      Connection.Free;
    end;
    ShowMessage( 'OK');
end;                    

It shows error as:

       Transaction net set.

Thanks for your comment.

----------------------------------------------------------------------
Zvoni · 2024-08-12 06:42

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 · 2024-08-13 06:53

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 · 2024-08-13 07:08

@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 · 2024-08-13 07:29

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)
Hmmm....you might be right: https://learn.microsoft.com/en-
us/sql/t-sql/statements/create-fulltext-catalog-transact-sql?view=sql-
server-ver16
QuotePermissions

User must have CREATE FULLTEXT CATALOG permission on the database, or
be a member of the db_owner, or db_ddladmin fixed database roles.But
wouldn't explain, why it complains about a User-Transaction

Just saw it:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2024-08-13 10:47

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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     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

----------------------------------------------------------------------
ALAU2007 · 2024-08-14 02:55

Thanks for all.

I follow Zvoni's instruction.  I copy the mssqlconn.pp to project
folder as mssqlconn_v2.pp
   and Move the Private Procedure "procedure Execute(const cmd:
string); overload;"

Update the testing program as:

uses ...., mssqlconn_v2, ...

procedure TForm1.Button2Click(Sender: TObject);
var
  Connection: TMSSQLConnection;
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
      Connection.Free;
    end;
    ShowMessage( 'OK');
end;

It WORKS.  The catalog is created.  Thanks again.

----------------------------------------------------------------------
TRon · 2024-08-14 03:34

@ALAU2007
Thank you for testing and reporting back the results.

@Zvoni
That is a bit of an oversight 

Good investigatory process, thank you very much for that (it did not
even crossed my mind that this would be the culprit).

----------------------------------------------------------------------
Zvoni · 2024-08-14 04:59

Quote from: TRon on August 14, 2024, 05:34:32 am@ALAU2007
Thank you for testing and reporting back the results.

@Zvoni
That is a bit of an oversight 

Good investigatory process, thank you very much for that (it did not
even crossed my mind that this would be the culprit).
Tron, that’s the thing: i think it was the same with SQLite and a
PRAGMA-call. I had to expose a way to access the low level API