mORMot Open Source

miab3

Member

From: Poland

Registered: 2014-10-01

Posts: 188

MSSQL and ZEOS

Arnaud,

Where(how) in the case of ZEOS I define (differentiate) connection by?:

- Mssql,
- FreeTDS,
- Ado-SQLNCLI.

Michal

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: MSSQL and ZEOS

In the ZDBC URI connection string, in the aServerName parameter of TSQLDBZEOSConnectionProperties.Create()?

miab3

Member

From: Poland

Registered: 2014-10-01

Posts: 188

Re: MSSQL and ZEOS

@ab,

UpperCase should remain in (new) line 558 SynDBZEOS.pas:

L558: SQLSplitTableName(UpperCase(aTablename), Schema,TableName);

(There is a problem with Oracle)

mORMot 1.18.520; ZEOS ZEOS 7.2.0-beta r3489; Delphi XE2-32

Michal

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: MSSQL and ZEOS

Michael said to me it was to be modified....

OK - just put it back.
See http://synopse.info/fossil/info/7ffd1a2b4f
Anyway, if nothing is found, our SynDB unit falls back to its own SQL request.

In all cases, there is now an overloaded URI() method for setting the expected protocol.

miab3

Member

From: Poland

Registered: 2014-10-01

Posts: 188

Re: MSSQL and ZEOS

@ab,

In all cases, there is now an overloaded URI() method for setting the expected protocol.

Thanks

By the way, it turned out that the name COMMENT used in Example 30 - MVC... is reserved in Oracle:
https://docs.oracle.com/cd/B19306_01/em … _words.htm
https://docs.oracle.com/cd/B28359_01/se … SQLRF01109

Michal

Last edited by miab3 (2014-11-15 22:58:18)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: MSSQL and ZEOS

I've added some options parameter to VirtualTableExternalRegisterAll().
See http://synopse.info/fossil/info/0fa0fb7850
The regMapAutoKeywordFields flag can be used to ensure that the mapped field names won't conflict with a SQL reserved keyword on the external database.
Note that we have already an up-to-date list of keywords in function TSQLDBConnectionProperties.IsSQLKeyword() - more accurate that the one in ZDBC and FireDAC AFAIK.

So you can write now:

 VirtualTableExternalRegisterAll(aModel,aExternalDB,[regMapAutoKeywordFields]);

and the column _COMMENT will be created on the external table.

Thanks for the feedback!

miab3

Member

From: Poland

Registered: 2014-10-01

Posts: 188

Re: MSSQL and ZEOS

@ab,

With the Oracle is not too good.
VirtualTableExternalRegisterAll(aModel,aExternalDB,[regDoNotRegisterUserGroupTables, regMapAutoKeywordFields]);
not helping base is not created and server does not start.
(I do not know whether to emphasize before the name can be used in Oracle?)

However, after replacing Comment on Comment1 database is generated but not all the tables are filled with data(ARTICLE, COMMENT1).
Could you look into Oracle? maybe I missed something.
This may come in handy:

drop table ARTICLE;
drop table ARTICLESEARCH;
drop table AUTHOR;
drop table BLOGINFO;
drop table TAG;
-- drop table COMMENT;
-- drop table COMMENT1;
-- drop table _COMMENT;

mORMot 1.18.525; ZEOS 7.2.0-beta r3489; Delphi XE2-32; W7-64; remote Oracle XE 11.2g.

Michal

Last edited by miab3 (2014-11-16 12:21:48)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: MSSQL and ZEOS

Did you try with SynDBOracle unit, instead of SynDBZeos?

Sadly, I do not have any instance of Oracle server available for testing, since I left my former company...
What are your advice for having an Oracle test server? Using Oracle Express? How do you reduce the used space and system resource? Is it better to install it in a Linux (virtual) machine? Which distro is preferred (I'm used to Ubuntu/Debian, but Oracle Express seems to be available in rpm only)? Do you think the 12c XE version will soon appear?

The doc at https://docs.oracle.com/cd/E17781_01/in … 02/toc.htm is pretty explicit.
So I guess I could use an OpenSuse 13.2 virtual machine, in server mode (without X/UI), with only one CPU core and 1 GB or RAM.
The tuto in http://www.howtoforge.com/the-perfect-s … spconfig-3 is pretty clear.

miab3

Member

From: Poland

Registered: 2014-10-01

Posts: 188

Re: MSSQL and ZEOS

@ab,

I have not tried the SynDBOracle yet, but just as ZEOS behaving AnyDAC/FireDAC (with Oracle).

Michal

Last edited by miab3 (2014-11-16 22:27:10)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: MSSQL and ZEOS

Any feedback about how to install Oracle XE?

miab3

Member

From: Poland

Registered: 2014-10-01

Posts: 188

Re: MSSQL and ZEOS

@ab

With SynDBOracle work but only in a version with Comment1 !!!.
ZEOS and AnyDAC has apparently problem with INSERT.

Any feedback about how to install Oracle XE?

In Windows, you download and install:
http://www.oracle.com/technetwork/datab … index.html
in Linux I have no experience.

Michal

Last edited by miab3 (2014-11-16 23:25:31)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: MSSQL and ZEOS

I do not want to pollute my main Windows machine with Oracle.
I do not mind having PostgreSQL running: it is light and do not impact my PC.
But Oracle is a huge and awful beast, to install (and to uninstall!).
So I was thinking about a dedicated VM - in Linux to make it smaller and more efficient.

miab3

Member

From: Poland

Registered: 2014-10-01

Posts: 188

Re: MSSQL and ZEOS

@ab,

ZEOS still has a problem with INSERT statement in Oracle.
Both the sample data and Add Comment.
SynDBOracle not have this problem.

mORMot 1.18.536; ZEOS 7.2.0-beta r3494.

Michal

miab3

Member

From: Poland

Registered: 2014-10-01

Posts: 188

Re: MSSQL and ZEOS

@ab,

After 1.18.548:

VirtualTableExternalRegisterAll(aModel,aExternalDB,[regDoNotRegisterUserGroupTables, regMapAutoKeywordFields]);
still does not work (Oracle) even for SynDBOracle.
Only manually rename Comment allows run 30 - MVC.. .

Michal