Recent

Author Topic: [solved] help with ODBC visual components  (Read 1586 times)

CapelliC

  • Jr. Member
  • **
  • Posts: 58
[solved] help with ODBC visual components
« on: July 21, 2020, 04:07:22 pm »
I was trying the simplest possible setup for an ODBC editing GUI: in my form


Code: Pascal  [Select][+][-]
  1.   TForm1 = class(TForm)
  2.     DataSource1: TDataSource;
  3.     DBGrid1: TDBGrid;
  4.     ODBCConnection1: TODBCConnection;
  5.     SQLQuery1: TSQLQuery;
  6.     SQLTransaction1: TSQLTransaction;
  7.  

In SQLQuery1 I've set the updateSQL statement, and the grid happily let me browse the dataset and edit the column I need to change, but when I call

SQLQuery1.ApplyChanges

I get an exception because the connection is busy with the results actually needed to populate the grid.

Have tried to circumvent the problem in several ways, but without luck so far.
Maybe I'm missing the key problem...
« Last Edit: July 22, 2020, 02:55:43 pm by CapelliC »

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: help with ODBC visual components
« Reply #1 on: July 21, 2020, 04:12:30 pm »
I get an exception because the connection is busy with the results actually needed to populate the grid.
What exception exactly?
You can always click the message and press CTRL+C to copy the message to the clipboard and paste it here.

How does your SQL look like. Normally the INSERT, UPDATE and DELETE SQLs are created automatically.
What is your TSQLQuery.UpdateMode set to?

CapelliC

  • Jr. Member
  • **
  • Posts: 58
Re: help with ODBC visual components
« Reply #2 on: July 21, 2020, 04:34:07 pm »
Thanks for the quick reply. The exception message

Code: [Select]
[Debugger Exception Notification]

Project test_update_odbc raised exception class 'EUpdateError' with message:
An error occurred while applying the updates in a record: Could not execute statement. ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: HY000; NativeError: 0; Message: [Microsoft][ODBC Driver 17 for SQL Server]La connessione � occupata dai risultati di un altro comando;

 At address 10020B434


[Ignore this exception type]

[Break] [Continue]

On SQLQuery1 I've set the properties SQL:
Code: MySQL  [Select][+][-]
  1.  D.nome_tipoazione AS Azione,
  2.  A.tipo AS IdTipo,
  3.  D.esecutore AS Esecutore,
  4.  A.N AS Tot_Azioni
  5. FROM my_table D,
  6.  (SELECT count(*) AS N, tipo FROM my_table_2 GROUP BY tipo) A
  7. WHERE D.numero_tipoazione=A.tipo
  8. ORDER BY A.tipo
  9.  
and updateSQL:
Code: MySQL  [Select][+][-]
  1. UPDATE my_table
  2.  SET esecutore=:Esecutore
  3.  WHERE numero_tipoazione=:IdTipo
  4.  

As I said, I've tried various visual properties as well as some programmatic ones, acting on SQLTransaction1. In the end, the only relevant snippet seems to be the

SQLQuery1.ApplyUpdates;

that I call - for instance - from a button click handler, but generates the exception.

The updateMode seems irrelevant, I've tried all 3 allowed values without luck.

Also, the database is SqlServer, not MySql. I've used that tag only to highlight the SQL code.
« Last Edit: July 21, 2020, 04:44:29 pm by CapelliC »

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: help with ODBC visual components
« Reply #3 on: July 21, 2020, 04:42:07 pm »
I'm not sure about the exception message.

But did you set the IdTipo parameter?

Anyway... it might be best to set the UPDATESQL to something more generic. Like

Code: SQL  [Select][+][-]
  1. UPDATE my_table
  2.  SET esecutore=:Esecutore
  3.  WHERE numero_tipoazione=:old_numero_tipoazione
The :OLD_ fieldnames are automatically generated from the record.

https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.updatesql.html

If that doesn't work you could try
Code: SQL  [Select][+][-]
  1. UPDATE my_table
  2.  SET esecutore=:Esecutore
  3.  WHERE numero_tipoazione=:old_IdTipo
« Last Edit: July 21, 2020, 04:45:24 pm by rvk »

CapelliC

  • Jr. Member
  • **
  • Posts: 58
Re: help with ODBC visual components
« Reply #4 on: July 21, 2020, 05:20:07 pm »
Have tried to simplify, just started another project and connected only the small table, so no joins or columns aliases, but the result doesn't change. I always get the exception because the connection is busy...

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: help with ODBC visual components
« Reply #5 on: July 21, 2020, 05:37:12 pm »
Have tried to simplify, just started another project and connected only the small table, so no joins or columns aliases, but the result doesn't change. I always get the exception because the connection is busy...
Does it work with a simple query and without setting UpdateSql?
(And setting UpdateMode to upWhereAll)

UpdateSql will be generated automatically for simple queries.

CapelliC

  • Jr. Member
  • **
  • Posts: 58
Re: help with ODBC visual components
« Reply #6 on: July 21, 2020, 05:48:44 pm »
Does it work with a simple query and without setting UpdateSql?
(And setting UpdateMode to upWhereAll)
No, always the same exception about the connection being busy...

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: help with ODBC visual components
« Reply #7 on: July 21, 2020, 05:52:23 pm »
Can you post a reproducible example.
Did you handle/open transaction correctly?
Is insert the first statement after open?

Quote
"This error occurs when there are pending results on a statement handle
that is then used to execute another query. This causes a problem when
the ODBC data source is a SQL Server (Microsoft or Sybase) because,
owing to the architectural design, there can be only one active
statement per connection on an SQL Server."

Therefore the SQL Server ODBC driver (SQLSRVR.DLL) cannot allow multiple
active HSTMTs on a single connection handle or HDBC. An active statement
is defined as a statement that has pending results; that is, the whole
result set has not been read from the server. "
https://it.comp.java.narkive.com/mcG1LkR8/odbc-sql-server-driver-la-connessione-e-occupata-dai-risultati-di-un-altro-hstmt

CapelliC

  • Jr. Member
  • **
  • Posts: 58
Re: help with ODBC visual components
« Reply #8 on: July 21, 2020, 06:23:54 pm »
A reproducible example requires of course SqlServer...
don't know if there is some public testbed instance out there.
Of course, our servers work with ODBC as well as other connections, as usual.
The root problem is - imho - that TSQLQuery *requires* a TSQLTransaction to fetch data.
Thanks for your help.

« Last Edit: July 21, 2020, 06:25:30 pm by CapelliC »

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: help with ODBC visual components
« Reply #9 on: July 21, 2020, 06:54:45 pm »
Yes, but someone here on the forum might have such server to test.
Or we might spot an error in your code.

Does your code work with a sqlite database?

The root problem is - imho - that TSQLQuery *requires* a TSQLTransaction to fetch data.
Does your code not have a transaction?

Even SQL server itself only works with transactions. You can't do anything without it.
« Last Edit: July 21, 2020, 06:56:26 pm by rvk »

CapelliC

  • Jr. Member
  • **
  • Posts: 58
Re: help with ODBC visual components
« Reply #10 on: July 22, 2020, 08:59:52 am »
Does your code not have a transaction?
Even SQL server itself only works with transactions. You can't do anything without it.

Yes, it has a TSQLTransaction connected to a TODBCConnection, as I have shown in the first post,
and I've also tried various hacks to get around the problem, but failed...
Without it, there is no chance to get data from a TSQLQuery.

About SqlLite, I don't know if it will work... Does ODBC would be involved ? Otherwise I don't see the point...

So far, I've experienced bad feelings wrt SqlServer support, the only 'reliable' way I've found so far has been to *rewrite*
the thin client layer throu ADO (ComObj etc...). Now debugging memory leakage etc...

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: help with ODBC visual components
« Reply #11 on: July 22, 2020, 09:29:01 am »
So far, I've experienced bad feelings wrt SqlServer support, the only 'reliable' way I've found so far has been to *rewrite* the thin client layer throu ADO (ComObj etc...).
Why can't you use TMSSQLConnection in combination with the SQL server client?

CapelliC

  • Jr. Member
  • **
  • Posts: 58
Re: help with ODBC visual components
« Reply #12 on: July 22, 2020, 09:54:45 am »
FreeTDS (that I used years ago on Unix to connect to SqlServer) requires a toolchain I cannot install on my servers. Moreover, dblib is not more available... and on production servers to download and install unknown components is something I don't can - and will - to do.

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: help with ODBC visual components
« Reply #13 on: July 22, 2020, 10:07:06 am »
Then the only thing is for someone to figure out why you are getting that error message with a simple SQL on ODBC with your code.

rvk

  • Hero Member
  • *****
  • Posts: 4407
Re: help with ODBC visual components
« Reply #14 on: July 22, 2020, 01:46:35 pm »
BTW, have you tried setting the TSQLQuery.PacketRecords := -1; ?
(as according to the readme)

 

TinyPortal © 2005-2018