mORMot Open Source

cregonat

Member

Registered: 2020-04-27

Posts: 7

Error with MSSQL, zeos connection

Hi,

I'm working on a backend for a mobile app, which must extend an old production system. Connected the mssql using Zeos connection and SQLNCLI native client. I get a strange error when I run this query:

 SELECT art.IdArticulo, 
     art.Descripcion, art.UniMedida, art.LlevaStock, 
    ISNULL((SELECT SUM(ingreso-egreso) FROM vtainventario inv 
    WHERE inv.idarticulo=art.idarticulo 
    AND IdDeposito = ?), 0) AS Stock, 
    sect.Descripcion as Sector, 
    ISNULL(sect.CodSector, 0) as CodSector 
    FROM COMVTAARTICULO art 
    LEFT JOIN vtasectorarticulo sect 
    ON (art.codsectorvta = sect.CodSector) 
    WHERE art.IdArticulo = ?

Error: oledb error sqlstate: 42000 native error: error message: syntax error, permission violation, or other nonspecific error..

I then changed the connection to TOleDBMSSQL2012ConnectionProperties and it worked without problems.

the line of zeos that throws the error is: line 1447, unit ZDbcOleDbStatement

      if fDEFERPREPARE and (fCommand.QueryInterface(IID_ICommandPrepare, CommandPrepare) = S_OK) then begin

I didn't know if report it on zeos forum or here. sorry if this is not the correct place to report.

Thanks!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: Error with MSSQL, zeos connection

I guess that if it works with our direct OleDB connection, it should work with ZdbcOleDbStatement...
So please try to report it on the Zeos forum.

I would use TOleDBMSSQL2012ConnectionProperties anyway, because it is more direct.
Do you have any reason to explicitly use Zeos?

cregonat

Member

Registered: 2020-04-27

Posts: 7

Re: Error with MSSQL, zeos connection

Thanks for your reply, I'll use TOleDBMSSQL2012ConnectionProperties as you suggested.
I wanted to use Zeos because there's a migration to linux planned, so I tought that would be easier the transition when that happens.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,539

Website

Re: Error with MSSQL, zeos connection

Under Linux you can use TODBCConnectionProperties from SynDBODBC + ms odbc driver or FreeTDS. I test both. On production I use msodbcsql17

Last edited by mpv (2020-06-26 16:40:01)

cregonat

Member

Registered: 2020-04-27

Posts: 7

Re: Error with MSSQL, zeos connection

Under Linux you can use TODBCConnectionProperties from SynDBODBC + ms odbc driver or FreeTDS. I test both. On production I use msodbcsql17

Thanks for the tip. I'll give a try.

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: Error with MSSQL, zeos connection

Hi cregonat,

Zeos is using a different binding technique in comparison to Syn[OleDB/ODBC]. We're using direct bindings -> if possible (alloc a buffer once and don't use any latebindings). That's why the Syn-access layers are slower than using ZeosLib for ODBC/OleDB. The more using the SynDB logic, the optimized plan for the request is determined per execution (use the MS-SQL profiler to see what happens), whereas the plan is pretty fix using real prepareds. For me it's a know'n issue you've been running into. ZeosLib is trying to get parameter-descriptions from the SQL-Server by using an immediately prepare + obtaining the descriptions. If done we're allocating the required parameter buffer once, and do all neccessary conversions to prevent reallocations where ever we can. Sadly the expensive MS products are failing sometimes(with obscure error codes), especially using parameters in sub-selects as you did. Also are selects with a "group by" clause dangerous to use while in explizit transaction. There are several MS-QC reports available but they are more than 10 years old inbetween, not one resolved. So know the product you're using.
To cirucmvent you issue you just need to add the paramter of ZDbcProperties.pas:

Properties.Values[DSProps_DeferPrepare]='True'

to your Statement-Properties. Zeos will fallback to the slower syndb compareable performance.
JFYI, Michael

Last edited by EgonHugeist (2020-07-24 07:44:40)

cregonat

Member

Registered: 2020-04-27

Posts: 7

Re: Error with MSSQL, zeos connection

Michael,

thanks for your complete explanation and the connection param. Right now the small backend entered production using SynOledb connection and it's serving without any problem. There's a plan to move mssql to linux, so probably I'll revisit it later and try again using zeos.

Claudio.