mORMot Open Source

squirrel

Member

Registered: 2015-08-13

Posts: 146

Mysql ODBC connection on 64bit Windows

Good day

I am hoping to use the mORMot framework as a middle layer between our legacy databases and external applications (both Delphi and Web).  Could you please assist with the following questions?

1) Is it possible to use ORM and have an integer primary key that is called anything other than ID?  eg Change the primary key name to TeamID or CustomerID?  All tables already have autoincrementing integer primary keys. Edit: I assumed it would be by doing something like this:

aModel.Props[TSQLRecordTblTeams].ExternalDB.MapField('ID','TeamID');

but this results in the following error when calling CreateMissingTables:

Error SQLITE_ERROR (1) using 3.8.11.1 - ''TSQLRestStorageExternal.Create: TSQLRecordTblTeams: unable to create external missing field TblTeams.TeamID - SQL="ALTER TABLE TblTeams ADD TeamID bigint"'' extended_errcode=1

2) When building example 28 in 32 bit using a 32bit mysql dsn connection, the example works perfectly.  Building the same code in 64bit and using a 64bit dsn connection, I get the error:

No SQLite3 libray available: you shall either add SynSQLite3Static to your project uses clause or run sqlite3 := TSQLite3LibraryDynamic.Create(..).

  I do not intend using SqlLite at all, only Mysql and only in 64bit.  Is this possible?

3) When trying to use the full connection string instead of a dsn, the error

Data source name not found and no default driver specified (0) is returned.

  Is there anything specific that needs to be specified as part of the connection string?  Sample code from example 28 altered with mysql connection string:
 

aProps := TODBCConnectionProperties.Create('','DRIVER=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;DATABASE=testdb;USER=root;PASSWORD=testpasswd;Option=3;Port=3306','','');

Thanks for the excellent examples you distribute with the code.  If it wasn't for them using the framework wouldn't have been possible.

Last edited by squirrel (2015-08-13 11:34:52)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: Mysql ODBC connection on 64bit Windows

1) Please restart with a blank database.

2) You need a SQlite3 engine available, even if it is not used to stored data: SQLite3 virtual tables are used to manage the external data, in some cases.
So you need the sqlite364.dll to be available with your exe.

3) Does your connection string work outside mORMot?
See sample 15 for other samples of connection strings.

squirrel

Member

Registered: 2015-08-13

Posts: 146

Re: Mysql ODBC connection on 64bit Windows

Thanks for the quick response.

1) This is a blank database.  If I don't call the Mapfield line, the table is successfully created.  I drop table manually after every test. 

2) Does that mean that I need to distribute an unreleased and unsupported version of SqlLite with all my applications and keep it up to date for something we don't use?  I am a bit confused about this.  I've searched my hard drive and don't have the 32bit version of SqlLite anywhere on it, yet the sample works.  But rebuilding the same code in 64bit gives the error.  Is the 64bit dll you distribute altered or different from the standard dll to make it a requirement?

3) I was able to resolve this, thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: Mysql ODBC connection on 64bit Windows

1) You can customize the ID field name on the external database.
The MapField method should be set before CreateMissingTables.

2) Sqlite3 in 32 bit is statically linked within the exe, but not in 64 bit.

squirrel

Member

Registered: 2015-08-13

Posts: 146

Re: Mysql ODBC connection on 64bit Windows

1) For future readers:  It seems that the field that replaces ID is not allowed to be part of the TSQLRecord definition.  If it is, it correctly creates the table and then try to add it on again after creating the table.  After removing the TeamId field from the TSQLRecord class it works as expected.

2) Are there any options for statically linking the 64bit in the exe as well?  Do 64bit obj files need to be generated, or is this not possible / available?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: Mysql ODBC connection on 64bit Windows

1) Of course!
The TSQLRecord.ID field stays the primary key ID field.
Only on external database, the mapped field is another.
Please see the doc - http://synopse.info/files/html/Synopse% … l#TITL_120

2) There is no such option yet.