I get an exception because the connection is busy with the results actually needed to populate the grid.What exception exactly?
[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]
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?
Does it work with a simple query and without setting UpdateSql?No, always the same exception about the connection being busy...
(And setting UpdateMode to upWhereAll)
"This error occurs when there are pending results on a statement handlehttps://it.comp.java.narkive.com/mcG1LkR8/odbc-sql-server-driver-la-connessione-e-occupata-dai-risultati-di-un-altro-hstmt
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. "
The root problem is - imho - that TSQLQuery *requires* a TSQLTransaction to fetch data.Does your code not have a transaction?
Does your code not have a transaction?
Even SQL server itself only works with transactions. You can't do anything without it.
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?
BTW, have you tried setting the TSQLQuery.PacketRecords := -1; ?
(as according to the readme)
Sorry for not being able to find the readme you're referring. The documentation is a bit sparse :)I don't blame you :D
Known problems:
===============
- CHAR/VARCHAR data truncated to column length when encoding to UTF-8 (use NCHAR/NVARCHAR instead or CAST char/varchar to nchar/nvarchar)
- Multiple result sets (for example when SP returns more than 1 result set only 1st is processed)
- Output parameters of stored procedure are not returned. See FreeTDS FAQ: "I'm not getting my output parameters returned, but I seem to be doing everything right!"
- DB-Library error 10038 "Results Pending" - set TSQLQuery.PacketRecords=-1 to fetch all pendings rows
- BLOB data (IMAGE/TEXT columns) larger than 16MB are truncated to 16MB - (set TMSSQLConnection.Params: 'TEXTSIZE=2147483647' or execute 'SET TEXTSIZE 2147483647')
(create temporary stored procedures for prepared statements)
JFYI: Zeos 7.3 (upcomming 8.0) https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/branches/testing-7.3/ (https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/branches/testing-7.3/)
has a ODBC_W/ODBC_A/OleDB(Windows only) protocol, deeply tested and optimized for SQL-Server.
right now I can complete with my simple minded ADODB (ComObj based) shallow interface.That's why i'm writing, 7.2 already has a ADO protocol for FPC. So don't invest to much time. There are loads of incompatibilities with FPC and the OleVariants, ADO is using for data-transport(all of them are hooked by Zeos to get it running). Such as decimal values etc. Delphi has no such problems. The more the ADO bridge seem's less maintained by MS and field types like datetime2/time2 are returned as strings(OleVariant missue), whereas ODBC and OleDB can handle them gracefully. The more we've Pointed out using ADO is such an incredible performance loss in comparision to OleDB (4x faster).. JFYI, Michael
There are loads of incompatibilities with FPC and the OleVariants, ADO is using for data-transport(all of them are hooked by Zeos to get it running). Such as decimal values etc. Delphi has no such problems.
7.2 already has a ADO protocol for FPC. So don't invest to much time.Yes, ADO support was the main reason I installed and attempted to use, but what protocol (from the listbox of TZConnection) I should use ?
Well, since most of my and others helpfull reports are rejected as won't fix and it's forbitten to ask questions, i gave up doing that by now. The more we need backward compatibility for older FPC's so it's worth it cirumvent such problems by our self in some cases. Of course it would be nice to omit "old" code in several years, honestly it would, so i agree with your POV.There are loads of incompatibilities with FPC and the OleVariants, ADO is using for data-transport(all of them are hooked by Zeos to get it running). Such as decimal values etc. Delphi has no such problems.
If there are incompatibilities in FPC that don't exist in Delphi, then please report them, so that they can be fixed.
Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=zeoslib;Data Source=K95VM-Egon\SQLEXPRESS2012;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=EGONDEVLAPTOPW7;Use Encryption for Data=False;Tag with column collation when possible=FalseOleDB:
Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=zeoslib;Data Source=(localdb)\ProjectsV13;MarsConn=Yes;Trusted_Connection=YesODBC:
DRIVER={SQL Server Native Client 11.0};Server=(localdb)\ProjectsV13;DataBase=zeoslib;Trusted_Connection=Yes;MARS_Connection=yes
Well, since most of my and others helpfull reports are rejected as won't fix and it's forbitten to ask questions, i gave up doing that by now. The more we need backward compatibility for older FPC's so it's worth it cirumvent such problems by our self in some cases. Of course it would be nice to omit "old" code in several years, honestly it would, so i agree with your POV.There are loads of incompatibilities with FPC and the OleVariants, ADO is using for data-transport(all of them are hooked by Zeos to get it running). Such as decimal values etc. Delphi has no such problems.
If there are incompatibilities in FPC that don't exist in Delphi, then please report them, so that they can be fixed.