beginner Question: about ODBCConnection
lazarus / 16-Databases

----------------------------------------------------------------------
xiyi0616 · 2024-06-26 06:55

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 · 2024-06-26 11:19

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

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


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 · 2024-06-26 11:57

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 · 2024-06-26 14:04

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

----------------------------------------------------------------------
xiyi0616 · 2024-06-26 14:19

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 · 2024-06-26 14:36

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

----------------------------------------------------------------------
Thaddy · 2024-06-26 16:12

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 · 2024-06-26 16:46

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 · 2024-06-26 17:43

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

----------------------------------------------------------------------
xiyi0616 · 2024-06-26 17:45

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

----------------------------------------------------------------------
paweld · 2024-06-26 18:12

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery2.Open;you insert:
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   try   
SQLQuery1.Open;  except    on E: Exception do    begin     
ShowMessage(CP950ToUTF8(E.Message)); // or:
showMessage(CP936ToUTF8(E.Message));      exit;    end;  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 · 2024-06-26 20:34

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 · 2024-06-26 20:51

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 · 2024-06-26 20:57

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

----------------------------------------------------------------------
paweld · 2024-06-26 21:07

Replace this procedureCode: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i procedure TForm1.Button1Click(Sender: TObject);begin 
Memo1.Lines.Clear;  //ODBCConnection1.Driver:='ODBC Driver 17 for SQL
Server';  ODBCConnection1.Params.Clear;   ODBCConnection1.Transaction
:= SQLTransaction1;   ODBCConnection1.Driver:=Edit_Driver.Text; 
ODBCConnection1.UserName:=Edit_MSSQLUser.Text; 
ODBCConnection1.Params.Add('Database=' + Edit_MSSQLDB.Text); 
ODBCConnection1.Password:=Edit_MSSQLPW.Text; 
ODBCConnection1.Params.Add('Trusted_Connection=No;'); 
ODBCConnection1.Params.Add('Server=' + Edit_MSSQLSrv.Text); 
ODBCConnection1.Params.Add('MARS_Connection=Yes;'); 
SQLQuery1.DataBase := ODBCConnection1;   Memo1.Lines.Add('Try to
Connect ...');  try     ODBCConnection1.Connected:= True;      if
ODBCConnection1.Connected then        Memo1.Lines.Add('Connected');  
except    on E: Exception do      Memo1.Lines.Add(e.Message); 
end; end;And try again

----------------------------------------------------------------------
xiyi0616 · 2024-06-26 21:12

Wo!  The problem has been resolved!!!

add this line ODBCConnection1.Params.Add('MARS_Connection=Yes;');

paweld, Thank you ver much!

It was very helpful to me!