[Solved]CREATE FULLTEXT CATALOG statement cannot be used inside user transaction

[ALAU2007]

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]

Don't start a Transaction?

[ALAU2007]

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

It still has such error.  Please advise.  Thanks.

[Zvoni]

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]

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]

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]

@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

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.

But wouldn't explain, why it complains about a User-Transaction

Just saw it:

Code: Pascal  [Select][+][-]

1. begin
2.     Connection := TMSSQLConnection.Create(nil);
3.     try
4.       Connection.HostName := 'hostname';
5.       Connection.DatabaseName := 'test1';
6.       Connection.UserName := 'sa';
7.       Connection.Password := 'password';
8.       //Connection.Open; //missing?
9.       Connection.ExecuteDirect('CREATE FULLTEXT CATALOG MyFullTextCatalog;', nil);
10.     finally

Huh? 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][+][-]

1. begin
2.     Connection := TMSSQLConnection.Create(nil);
3.     try
4.       Connection.HostName := 'hostname';
5.       Connection.DatabaseName := 'test1';
6.       Connection.UserName := 'sa';
7.       Connection.Password := 'password';      
8.       Connection.ExecuteDirectSQL('CREATE FULLTEXT CATALOG MyFullTextCatalog;'); //ExecuteDirectSQL calls "open" before calling "Execute"
9.     finally

or

Code: Pascal  [Select][+][-]

1. begin
2.     Connection := TMSSQLConnection.Create(nil);
3.     try
4.       Connection.HostName := 'hostname';
5.       Connection.DatabaseName := 'test1';
6.       Connection.UserName := 'sa';
7.       Connection.Password := 'password';
8.       Connection.Open;
9.       Connection.Execute('CREATE FULLTEXT CATALOG MyFullTextCatalog;');
10.     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  [Select][+][-]

1.     begin
2.         Connection := TMSSQLConnection.Create(nil);
3.         try
4.           Connection.HostName := 'hostname';
5.           Connection.DatabaseName := 'test1';
6.           Connection.UserName := 'sa';
7.           Connection.Password := 'password';
8.           Connection.Open;
9.           Connection.Execute('CREATE FULLTEXT CATALOG MyFullTextCatalog;');
10.         finally

and report back

[ALAU2007]

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]

@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]

@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