I have searched the forum looking for a better answer and wasn’t able to find it. I’m having trouble with numeric types between MySQL and Servoy. I have found that it is better to create tables using Servoy than creating directly in MySQL, because the dbi file doesn’t seem to update appropriately - BTW this issue only surfaces when synching to SVN. However, when I create a number field in Servoy, it ends up as a float in MySQL. I have tried to enter the number 2, since I read somewhere that would tell Servoy to make it a decimal 10,2. But regardless of what I put in the length field in Servoy, I get a float in MySQL. What am I doing wrong?
And what is the best practice? Do you create tables in Servoy, or in the db directly?
If something didn’t broke recently if you create a number field in servoy and specify “2” as the field length you should obtain a DECIMAL(10,2), if you create a number field with no length specified you obtain a FLOAT field, make sure you are using the appropriate JDBC driver for you MySQL version and doublecheck.
Anyway keep in mind that for serious projects it’s a good thing to perform the datamodeling directly using your DB management utilty of choice (so you can create the perfect field types that match your needs, all the needed indexes and you can also specify the db engine to use for the table == go for InnoDB!!!), Servoy can create fields and tables as well but it’s just for your convenience, I wouldn’t use it to create a large datamodel but just to add that additional field that you may need now and then.
Thanks for the response. I did as you suggested, and it doesn’t seem to work. Not sure if there’s a problem (I’m using 4.1) or if I’m doing something else wrong. I too agree that working in the db is the right place to create/define the db models, however I was “talked into” using Servoy due to synch issues we were having. Oh well, I’ll take you’re advice and go back to working directly in MySQL.
ngervasi:
That’s strange, you could create a case in the support system so the Dev Team can check and (if needed) fix.
Nicola
I’m too now crossing over to MySQL from MSSQL, and where I’ve used ID columns through both MSSQL and MySQL then I dont have a Servoy Sequence, which I assume then limits the table to the proprietary database? Is that the case in your experience?
Servoy sequences seem the sensible route since it goes one step closer to making the solution database agnostic?
Not sure if I understand correctly what you are saying but using servoy sequences on primary keys (or other columns as well) doesn’t limit your database indipendance and the same applies to using DB generated keys so the question is: what did you mean?
ngervasi:
Not sure if I understand correctly what you are saying but using servoy sequences on primary keys (or other columns as well) doesn’t limit your database indipendance and the same applies to using DB generated keys so the question is: what did you mean?
I meant if I use Db generated ID keys rather than Servoy sequences, will that limit the Servoy Db agnostisim (if there is such a word ).
I have now got a mixture of MSSQL ‘Identity’ columns and Servoy Sequence columns. I’m concerned I may need to reconcile these before I face some challenge further down the line!
No, if you use DB generated keys you are not limiting the database indipendance, Servoy just needs unique keys, it doesn’t matter how the underlying database is handling the generation. So the answer is: feel free to use DB managed PKs.
ngervasi:
No, if you use DB generated keys you are not limiting the database indipendance, Servoy just needs unique keys, it doesn’t matter how the underlying database is handling the generation. So the answer is: feel free to use DB managed PKs.
Thanks Nicola - and Servoy will simply create Db specific ID keys if the solution is exported with Db ‘x’ and imported to Db ‘y’ (even though that could be MySQL to Oracle for example)?
I think you can use this ‘rule’.
When the database is accessed (and used for data entry) by other tools than Servoy you should use DB managed PK’s.
If Servoy is the only one (and only Servoy server) that inserts data in that database then you can use Servoy Sequences.
Yes, Robert is perfectly right and regarding to this:
and Servoy will simply create Db specific ID keys if the solution is exported with Db ‘x’ and imported to Db ‘y’ (even though that could be MySQL to Oracle for example)?
If you have set the PK to be managed by the DB and you import in a different Db you have to take care that the new DB is actually handling the PK generation, so, to recap: if you tell Servoy that the PK is managed by the DB Servoy will simply do nothing on that column when you create new records but it will expect that the DB generates a unique PK when the record is saved.
ngervasi:
Yes, Robert is perfectly right and regarding to this:
If you have set the PK to be managed by the DB and you import in a different Db you have to take care that the new DB is actually handling the PK generation, so, to recap: if you tell Servoy that the PK is managed by the DB Servoy will simply do nothing on that column when you create new records but it will expect that the DB generates a unique PK when the record is saved.
That’s what I was getting at Nicola - so where the Db Server may change then it’s essential to have Servoy handle the sequence in order for it to be truly Db agnostic!
This is probably not an issue for 95% of deployments I would imagine, but I know of at least two of my clients who will want an on premises deployment, one has MSSQL and the other Oracle. My own development server will be MySQL from now on. In this case I need to ensure at least the identity columns are created in Servoy.
Thanks for the clarification Nicola - bang on as usual!
Kahuna:
This is probably not an issue for 95% of deployments I would imagine, but I know of at least two of my clients who will want an on premises deployment, one has MSSQL and the other Oracle. My own development server will be MySQL from now on. In this case I need to ensure at least the identity columns are created in Servoy.
Kahuna,
When deploying a solution to another database, Servoy will create tables with the same sequence type as in the source solution when available.
If the requested type is not available (for instance identity columns in Oracle) Servoy will fall back to servoy sequences.
Kahuna:
This is probably not an issue for 95% of deployments I would imagine, but I know of at least two of my clients who will want an on premises deployment, one has MSSQL and the other Oracle. My own development server will be MySQL from now on. In this case I need to ensure at least the identity columns are created in Servoy.
Kahuna,
When deploying a solution to another database, Servoy will create tables with the same sequence type as in the source solution when available.
If the requested type is not available (for instance identity columns in Oracle) Servoy will fall back to servoy sequences.
ngervasi:
If something didn’t broke recently if you create a number field in servoy and specify “2” as the field length you should obtain a DECIMAL(10,2), if you create a number field with no length specified you obtain a FLOAT field, make sure you are using the appropriate JDBC driver for you MySQL version and doublecheck.
I think something broke when installing version 3.5.8
I noticed the same problem using SQL Server. So this is not a database specific problem
It looks like Servoy is not working anymore like before.
When I add a new column to an existing table using Servoy dataprovider and when I select type NUMER, then always a float type is created, even when I specify a length.
This worked correctly in 3.5.7 and is a new bug in 3.5.8
Did someone register this already in the support system ?
There was indeed functionality in Servoy 3.5.7 when a number was entered in the length field.
However, the length was not actually used in the code to create the column in the database, when a non-zero length was specified a number(19, 2) column was created, regardless of the length entered.
We decided to remove the functionality because it was incomplete and confusing.
Note that when specific data types are to be created, other than the Servoy-standard set table have to be created /columns have to be created outside Servoy.