But virtual table translation is a bit slower - so you may want to create a service on the server side, which will call directly the external table.
In all cases, you are by-passing the ORM so writing such direct SQL statements on the client side is just a wrong idea.
Please read the "Daily ORM" section of the SAD 1.18 pdf.
I strongly advice you switch to true n-Tier / SOA architecture, and let the SQL on the server side only.
And internally, the ID field is hard coded to be the ORM primary key.
]]>aProps := TSQLDBZeosConnectionProperties.Create(TSQLDBZeosConnectionProperties.URI(dMySQL, myServer, 'libmysql.dll', False),
myDB, myUser, myPwd);
VirtualTableExternalRegister(globalModel, TSQLmyTable, aProps, 'TableExternal');
globalModel.Props[TSQLmyTable].ExternalDB.MapField('myID', 'ID');
All this works fine and I retreive the varchar250 key, but I got error when I try to update one record on the external table via varchar250 key.
globalClient.EngineExecute('UPDATE myTABLE SET FIELD1 = VAL1 WHERE ID='+ Varchar250key);
fProperties := TSQLDBSQLite3ConnectionProperties.Create(
SQLITE_MEMORY_DATABASE_NAME,'','','');
VirtualTableExternalRegister(fExternalModel,
TSQLRecordPeopleExt,fProperties,'PeopleExternal');
fExternalModel.Props[TSQLRecordPeopleExt].ExternalDB.
MapField('ID','Key').
MapField('YearOfDeath','YOD');
globalModel.Props[TSQLmyTable].ExternalDB.MapField('mySQLID', 'ID');
But I get the 'no such column:ID' error when I try this update :
globalClient.EngineExecute('UPDATE myTABLE SET FIELD1 = VAL1 WHERE ID='+ Varchar250key);
globalClient.EngineExecute('UPDATE TABLE SET FIELD1 = VAL1 WHERE ID='+ Varchar250key)
thnx,
]]>I think that the easy way is to map the Varchar250 key into a RawUTF8 field (<> SQLite ID) and then specify it for the update/delete operations.
I see two problems for it:
1. SQLite3 primary key is by definition an integer, and especially for a virtual table, as we use with mORMot's external tables - see http://blog.synopse.info/post/2011/05/1 … -framework
2. We defined the ID as integer, or as a TSQLRecord class property for one-to-many or one-to-one
Sadly, the newly introduced WITHOUT ROWID option - see http://sqlite.org/withoutrowid.html - by which you can use e.g. a TEXT field instead of the INTEGER PRIMARY KEY, just does not work with virtual tables.
This is why I did not list your proposal in my previous answer, since when I considered it, it was simply not feasible.
I'll test the mapping functionnality later.
I think that the easy way is to map the Varchar250 key into a RawUTF8 field (<> SQLite ID) and then specify it for the update/delete operations.
Merci beaucoup
]]>aModel.Props[aExternalClass].ExternalDB.MapField(..)
including regression tests.
See http://synopse.info/fossil/info/cae7936c88
So you can write e.g.
fProperties := TSQLDBSQLite3ConnectionProperties.Create(SQLITE_MEMORY_DATABASE_NAME,'','','');
VirtualTableExternalRegister(fExternalModel,TSQLRecordPeopleExt,fProperties,'PeopleExternal');
fExternalModel.Props[TSQLRecordPeopleExt].ExternalDB.
MapField('ID','Key').
MapField('YearOfDeath','YOD');
Then you use your TSQLRecordPeopleExt table as usual from Delphi code, with ID and YearOfDeath fields:
- The "internal" TSQLRecord.ID field will be an external "Key: INTEGER" column.
- The "internal" TSQLRecord.YearOfDeath field will be an external "YOD: BIGINT" column.
What you need is a way to map any varchar(250) value from/to an integer value...
Not easy.
I see at least several patterns:
- My current best idea is that we may have to generate ALTER TABLE ADD COLUMN for a new INTEGER NON NULL UNIQUE column to the table, so that we will be able to use it as the primary key on an existing table.
- Another option may be to maintain a separate IntegerID/Varchar250Key mapping (e.g. in one or several dedicated SQLite3 tables). But this may be slow and confusing, especially when the DB is accessed directly from other non-mORMot applications;
- Last, we could use a callback to compute the ID to/from varchar(250), but we may have collision, so it could be also very difficult to work with.
What do you think?
Thnx,
]]>I have the same problem with a mySql table that have 'ID' like this `id` varchar(250) NOT NULL.
when I get the records, the ID only takes integers from the start until it finds an alpha char of the mySQL ID field.
Example :
mySQL_ID = 1213E30377
SQLite_ID = 1213
How I can retrieve the entire primary key in order to change the record?
Thnx,
]]>