[solved] help with ODBC visual components

[CapelliC]

BTW, have you tried setting the TSQLQuery.PacketRecords := -1; ?
(as according to the readme)

Wow, that worked. Thanks so much.

Sorry for not being able to find the readme you're referring. The documentation is a bit sparse
There are a lot of objects/interfaces to examine, meanwhile I had to solve other problems in my code not related to the usage of RAD components, I was a bit lost.

Generally Lazarus/FPC are working well, but when something start not working and I cannot overcome the problem... well, here I come, after a bit of sweat and pain...

Thank you again.

[rvk]

Sorry for not being able to find the readme you're referring. The documentation is a bit sparse

I don't blame you 
It's an obscure little line in the fpc\packages\fcl-db\src\sqldb\mssql\readme.txt

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)

It's also on the page for TMSSQLConnection.

Although you didn't use TMSSQLConnection directly, the error message "Results Pending" did seem a bit like the message you got "The connection is occupied by the results of another command" and the quote I gave earlier about "pending results on a statement handle". So it's seems a SQLServer thing that pending results need to be handled first.

Normally the number of records returned by the server is set by the PacketRecords property. The default value is 10; if you make it -1 then all records will be loaded at once. And in that case there are no pending results on the handle anymore.

Glad it's solved.

[EgonHugeist]

JFYI: Zeos 7.3 (upcomming 8.0) 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.

[CapelliC]

JFYI: Zeos 7.3 (upcomming 8.0) 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.

Thanks, will try ASAP.
I've installed ZEOS a little ago (ver. 7.2.6.1), but stumbled into the same problems (FreeTDS or dblib required for connection) as with TMSSQLConnection.
Maybe will try ver. 8 as it come out, right now I can complete with my simple minded ADODB (ComObj based) shallow interface.

[EgonHugeist]

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

[PascalDragon]

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.

[CapelliC]

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 ?

mssql does require either FreeTDS or ntwdblib at transport layer, so I'm unable to connect.
ado seems to require ODBC - it's rather confusing, what to put into catalog, database, hostname, assuming the following information from a DSN file, that allows connection when put into FileDSN of a TODBCConnection

Code: Text  [Select][+][-]

1. [ODBC]
2. DRIVER=ODBC Driver 17 for SQL Server
3. DATABASE=XXX_DB
4. WSID=XXX_WS
5. Trusted_Connection=Yes
6. SERVER=XXX.YYY.ZZZ.UU
7. Description=XXX_DESC
8.  

[EgonHugeist]

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.

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.

@CapelliC
i'm using such a DataBase-String fo my tests:
ADO:

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=False

OleDB:

Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=zeoslib;Data Source=(localdb)\ProjectsV13;MarsConn=Yes;Trusted_Connection=Yes

ODBC:

DRIVER={SQL Server Native Client 11.0};Server=(localdb)\ProjectsV13;DataBase=zeoslib;Trusted_Connection=Yes;MARS_Connection=yes

Take attantion to the "MarsConn=Yes"(ODBC) parameter and "MARS_Connection=yes"(OleDB) if you ever test 7.3

[PascalDragon]

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.

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.

Do you have examples for such rejected reports?

Also discussion on the bugtracker is not desired. For discussion the mailing lists or the forum should be used.