mORMot Open Source

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Integrating TDS in SynDBZeos

This forum thread is about the needed modifications to support TDS (as available with Zeos) with mORMot.

TDS has some pretty unusual syntax expectation at SQL level, e.g. for UPDATE statements, like this:

Update Foo(NCHARField) set (N?) where X

This is an email I just sent to Daniel & Michael:

About the SQLAdapt*() new methods, I would rather use some dedicated methods for INSERT / UPDATE / DELETE, just providing the raw information needed by the ORM.

In fact, there are several places where we write e.g. UPDATE statements:
- in mORMotDB.pas for simple UPDATE on one field value, in TSQLRestStorageExternal.EngineUpdateField / EngineUpdateBlob / UpdateBlobFields / Update
- in mORMot.pas for TJSONObjectDecoder.EncodeAsSQLPrepared - which is used in mORMot.pas by TSQLRestStorageExternal.JSONDecodedPrepareToSQL (used also by InternalBatchStop)
- in mORMot.pas for TSQLModelRecordPropertiesExternal.ComputeSQL - which is used to pre-compute the SQL for most common operations, stored in a TSQLModelRecordPropertiesSQL record.

The new methods should be defined at TSQLRestStorage level, similar to AdaptSQLForEngineList().
Then a method in TSQLDBConnectionProperties, similar to AdaptSQLLimitForEngineList(), should take care of the exact SQL generation, according to the underlying engine.
In a first draft, we may only put the necessary method to handle the TDS expectations about UPDATE fields (is it mandatory also for INSERT?).

I created the following feature request ticket to track the corresponding changes in :
See http://synopse.info/fossil/tktview/94ff704bb16d7

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: Integrating TDS in SynDBZeos

Arnaud,

i know we did loudly ask for this support... But i have to admit TDS is a dead and way from my point of view. Let me explain some advantage/disadvantage for your users:

Advantages:
1. one little library to access Sybase or MSSQL by using the "Tabular Data Stream" protocol: ntwdblib.dll/dblib.dll or a FreeTDS library.

Disadvantages:
1. MSSQL does not support a type difference between N(VAR)Char / (VAR)Char-Fields
2. All Strings which are retrieved need a encoding detection to indicate which encoding a column has -> what about native USASCII chars? -> Performance?
2. NTEXT fields are not selectable you need a cast to TEXT
3. NO Prepared Statement API. Zeos simply emulatates it.
4. MSSQL is doing no encoding detection: so the spezial syntax whould be required to indicate which param uses which encoding! NO WAY AROUND
5. The TDS protocol is sequential only. The more you have only one Connection handle. NOTHING else. SO if you guys are making a select, Zeos is forced cache ALL data. If this is done mORMot can get the JSON-Contents. This is done because next query kills a opened request...
6. All String columns are retrieved with ' '(Space)-filled strings. There is a mode "ANSI_PADDING=OFF/ON" which allows to enable/disable this behavior. Disabling means you will get a exception if you try to update a fixed-char field with a length shorter than Field.Length. Nice isn't it?

Should i go on? I don't think so.

Well it might be possible some more users do want to use it. So keep the ticked but note this is a very spezial case. From my point of view: No need to over hurry.

Michael

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Integrating TDS in SynDBZeos

@Michael
This does make sense to me.
I was never attracted by the TDS clients...
I suspect you could use ODBC in Linux, right? See http://msdn.microsoft.com/en-us/library/hh568451

Then from other platforms (e.g. mobile), I would not use direct connection to the DB, but a remote connection, via REST or SOA.

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: Integrating TDS in SynDBZeos

This does make sense to me.
I was never attracted by the TDS clients...

Agree. Propose you simply add a comment to your documentations and close your ticket, if nobody has objections, of course.

Eventually some additional notes:

TDS is able to be used for all old SQL servers too (6.5-2012). I don't know if the ODBC driver can be used with them..
But let's say: All below 2000 are old and Mircosoft also did stop supporting them!

Michael

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Integrating TDS in SynDBZeos

I've simply deferred the ticket.

Thanks for the input.