Mapping of Server Data Type per database provider?

[steffen_w]

Hi,

maybe it's already there and I just don't see it, but I tried to sync my model (which was developed against a MySQL database) with a MSSQL server and got a lot of errors. All of them are caused by having things like 'AUTO_INCREMENT' and (apparently) 'UNSIGNED' in my Server Data Type property. I'd have thought that those are automatically 'translated' by the EntityDeveloper. Did I do something wrong or is that not implemented?

[MariiaI]

After changing your connection from MySQL to SQL Server, your model still holds storage part and model mapping for the previous database - MySQL - and because some data types in SQL Server and MySQL are different, the model is not valid.

To make the model valid, you can do the following: perform "Create database script from Model" or "Update database from Model" (if the database does not exist yet, or need to be updated) with the 'Regenerate storage' check box selected. In this case all server data types in the model will be updated to the correct types.

[steffen_w]

ah, thanks for the answer (sorry was busy with other stuff).

Well, I just checked and when I select 'Update database from model', check the checkbox for recreation, click next -> next -> next and at 'Choose change actions' deselect everything and continue, it'll update my model and not touch the database. So, my question is: Why is there no 'Make model compatible to database type' button that does something like that?

*edit

since I can't find anything about that in the revision history: There seems to be a bug when I try to change a column to AUTO_INCREMENT/IDENTITY. The column is a primary key, when I add AUTO_INCREMENT on MySQL site, it works. When I change over to MSSQL and try to update the database, it just throws an error about 'Wrong syntax near IDENTITY'. I looked at the generated script and the problem was obvious: The ED drops the constraint of the table (correct, since this is a primary key) and then tries to change the columnt to IDENTITY with an alter command which will not work. One has to remove the constraint, drop the column and add the column again (this time with the IDENTITY word).

[MariiaI]

So, my question is: Why is there no 'Make model compatible to database type' button that does something like that?

Thank you for the comment on this. We will consider the possibility of adding this option, but we cannot provide any time-frame at the moment.

As for the issue with AUTO_INCREMENT/IDENTITY columns, we couldn't reproduce it. Please specify the version of LinqConnect you are working with and the detailed steps to reproduce the issue. Also, if possible, please send us the model and the generated script so that we are able to investigate this issue in more details.

[steffen_w]

We will consider the possibility of adding this option, but we cannot provide any time-frame at the moment.

okay, now that I know how to change it it's not that important anyway.

Version is 4.2.231

Steps: Create a MSSQL database with at least one table that has a column marked as primary key (should be incremental, but don't make it incremental yet), add some random data to the table. Fire up Devart/LinqConnect designer, create model from database, try to add IDENTITY to the primary key column and sync with the database.

If that does work for you, I guess it's something wrong with my database or I'm doing something wrong (which would be most likely).

[MariiaI]

We couldn't reproduced this issue. We perform the following steps:
- create database table 'Test' (ID, val); ID is a primary key, not identity yet;
- add 2-3 records to the table;
- create LinqConnect model with this table;
- set 'IdGenerator' to 'Identity' for the 'ID' property;
- perform update of the database using the 'Update Database From Model' wizard.

All changes to this table are made correctly.
Please make amendments to the steps to reproduce and specify the following:
- the SQL script for the database table;
- the data that should be stored in this table;
- the version of SQL Server.

Also, if possible, please send us the model you are working with, so that we could investigate this issue in more details.

[MariiaI]

The "Regenerate Storage and Mapping" option for regenerating model parts, specific to the database server, is added to the diagram shortcut menu.
New build of LinqConnect 4.2.281 is available for download now! It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=27463.