beginner Question: about ODBCConnection

[xiyi0616]

I build some TSQLQuery component useing ODBCConnection database.
ODBCConnection driver: ODBC Driver 17 for SQL Server

I have no problem using multiple SQLQuery to execute select statements and return datas in a button event.

For example:
  sn := 35555;
  SQLQuery1.Close;
  SQLQuery1.SQL.Clear;
  SQLQuery1.Params.Clear;
  SQLQuery1.SQL.Add('select * from tb_UploadPackage where x_SerialNo=:p0');
  SQLQuery1.Params[0].AsInteger := sn;
  SQLQuery1.Open;

  SQLQuery2.Close;
  SQLQuery2.SQL.Clear;
  SQLQuery2.Params.Clear;
  SQLQuery2.SQL.Add('select * from tb_UploadPackage');
  SQLQuery2.Open;


  SQLQuery3.Close;
  SQLQuery3.SQL.Clear;
  SQLQuery3.Params.Clear;
  SQLQuery3.SQL.Add('select * from tb_UploadErrInfo where x_SerialNo=:p0');
  SQLQuery3.Params[0].AsInteger := sn;
  SQLQuery3.Open;


But when I click on this button and then click on the second button to perform a similar SQLQuery operation, I inevitably get an error: (See Attach pic), even if I cut a portion of the code from the first button event to the second one.

This is very strange. Does anyone know the reason? How should it be resolved?

(I try to check the sqlerror code, There is no specific indication of the problem.  I suspect it has something to do with transactions, but I'm not very familiar with the current data component mechanisms in Lazarus.)

[Seenkao]

Товарищи разработчики, вы ведь подрабатываете телепатами?

Мне просто интересно, как можно подсказать что-то, что не связано с картинкой, по картинке? Может всё-таки код приложите для того чтоб вам можно было помочь?


Google translate:
Comrade developers, you work part-time as telepaths, right?

I'm just wondering how you can suggest something that is not related to a picture from a picture? Maybe you can still attach the code so that we can help you?

[xiyi0616]

Thank you for your time!

As a novice, I might assume this is a common issue.

But you are right, i attach some files.

When I click the Open button first and then the Test button, I get an error.

[Zvoni]

You might be correct with your suspicion about Transactions.
Have you tried to give each SQLQuery its own Transaction?

[xiyi0616]

This is a good iea. Tks.
Perhaps I have a misconception that all components using a same library should be able to use a same transaction component. I have just started using the database components in Lazarus.

[xiyi0616]

I have tried using another transaction component with SQLQuery2 in the Test button event, but I still encounter the same error.

[Thaddy]

Note there is only one(1) 64 bit driver for ODBC and it is never been very well tested.
(not fpc fault, but ms), so if you try to compile for 64 bit your code may fail.
Except for MS SQL, all other ODBC drivers are 32 bit code.

[xiyi0616]

This is a bad message. ODBC 17 or 18 is the only driver that I know of for connecting to MS SqlServer on Linux.

[af0815]

you can connect to ms-sql with freetds drivers on linux, win, raspbian.

[xiyi0616]

Thank you for the suggestion. I will give it a try.

[paweld]

Without the content of the error, it is hard to determine the cause.
You need to change the code page of the message to UTF-8. To do this you first need to find out what collation is set for the MSSQL server, then instead:

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

1.   SQLQuery2.Open;

you insert:

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

1.   try
2.     SQLQuery1.Open;
3.   except
4.     on E: Exception do
5.     begin
6.       ShowMessage(CP950ToUTF8(E.Message)); // or: showMessage(CP936ToUTF8(E.Message));
7.       exit;
8.     end;
9.   end;

and add LConvEncoding unit to uses section
Then you should see the error content on the message instead of these stamps.

I checked your project under Windows 10 (MSSQL also installed on Windows) and I don't get any errors in both 32 and 64 bit versions of the application.

[xiyi0616]

The MS SQLServer which i used is not utf-8 code page, local language.  It's early version. Server 2008.


I think you're right. Let's take a look at the complete error message, and I'll try your code.

[xiyi0616]

I use CP936ToUTF8 function instead CP950ToUTF8.
Then i get useful message. But it is looks like limit to length, the message is not complete. Just head
Which means: Busy connection to ...

[xiyi0616]

Am I getting close to the root of the problem?
Is my version of MSSQL Server too low?

[paweld]

Replace this procedure

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

1. procedure TForm1.Button1Click(Sender: TObject);
2. begin
3.   Memo1.Lines.Clear;
4.   //ODBCConnection1.Driver:='ODBC Driver 17 for SQL Server';
5.   ODBCConnection1.Params.Clear;
6.  
7.   ODBCConnection1.Transaction := SQLTransaction1;
8.  
9.   ODBCConnection1.Driver:=Edit_Driver.Text;
10.   ODBCConnection1.UserName:=Edit_MSSQLUser.Text;
11.   ODBCConnection1.Params.Add('Database=' + Edit_MSSQLDB.Text);
12.   ODBCConnection1.Password:=Edit_MSSQLPW.Text;
13.   ODBCConnection1.Params.Add('Trusted_Connection=No;');
14.   ODBCConnection1.Params.Add('Server=' + Edit_MSSQLSrv.Text);
15.   ODBCConnection1.Params.Add('MARS_Connection=Yes;');
16.   SQLQuery1.DataBase := ODBCConnection1;
17.  
18.   Memo1.Lines.Add('Try to Connect ...');
19.   try
20.      ODBCConnection1.Connected:= True;
21.  
22.      if ODBCConnection1.Connected then
23.         Memo1.Lines.Add('Connected');
24.  
25.   except
26.     on E: Exception do
27.       Memo1.Lines.Add(e.Message);
28.   end;
29.  
30. end;

And try again