Migrating from Firebird to MySql

zatiri · Post by **zatiri** » 23.11.2007, 20:32

Hi folks. I like too much to work with ZeosLib. I am using the Delphi 2006 and I´m migrating from FB to MySql. Until now, I´m having success in this job. As I like to use the the Windows´ Register to put and get a lot of informations from my applications, now I have an application that can be used in any of the two DBs, because I can select the parameters of they in the configuration form. But I´m having a problem and I´m not understanding: In a select, I ask the following:
'Select Descr as Item, Count(*) as Quant from Table Where Comand = :Seek Group by Descr'
Well, after this the system returns the following error:
ZReadOnlyQuery1: Type mismatch for field 'QUANT', expecting: Integer actual: LargeInt
As you can see, this field isn´t in the BD: I´m creating in the query. It appears that has a configuration here for the 'field' QUANT to get an Integer, and he´s receiving a LargeInt type. How can I change it? Where do I go to do this? Thanks sincerely to any help.
A nice week end to all.

Jay · Post by **Jay** » 25.11.2007, 01:16

I suppose that you have defined persistent fields for this query and that is the problem. Depending on the database and the server version, a COUNT() may return different integer types. For example, FB may return a INTEGER type (ftInteger) and MySQL may return a BIGINT Type (ftLargeInt).

If you created the persistent fields against the FB database, it will create an ftInteger type field and when you open the query on a MySQL database which returns a ftLargeint type, you get the error.

Solutions:
1) don´t use persistent fields.
2) Type Cast the value in the query to obtain the same field type. for example;
CAST(Count(*) as INTEGER) as Quant

I Hope this helps,

Regards
Jay

zatiri · Post by **zatiri** » 25.11.2007, 15:44

Thanks Buddy. I´ll try this. I´ll post the result here.

zatiri · Post by **zatiri** » 26.11.2007, 10:21

Ok. Success. I created the persistent fields with FB. I did this again with the MySql and the problem was finished. Thanks a lot for your attention. Have a nice week.

Post by **mdaems** » 26.11.2007, 11:07

Maybe you better create it at runtime as it depends on the selected database...
Does your solution still work with FB? Is the reason why it still works logical?

Mark

zatiri · Post by **zatiri** » 26.11.2007, 11:22

Well, it really isn´t working with FB now. I´m seeing that it´s coming a distance between them; I really don´t know about some guy that had success with the 2 DBs without great problems. What do you mean with create in runtime? I mean, what may I create in runtime? Escuse-me. I didn´t understand. thanks for your attention.

Post by **mdaems** » 26.11.2007, 11:57

Do you have a (forcing) reason to use persistent fields? As without persistent fields your fields definitions are made when you open the query. Normally that means the exact right field type will be chosen at runtime, dependent on the server.

When you really needs to use persistent field definitions you could try to add the persistent field definition for this column at runtime the way it's done by the IDE when you're designing. Unfortunately, I don't know how that can be done... Possibly it's just about calling some TDataset methods, but I hope somebody else can tell you that.
It may also be possible to add a field definition from scratch like TPersistentField.create(....);......... and making the right settings depending on the selected database, but that's probably even more difficult.

Mark

zatiri · Post by **zatiri** » 26.11.2007, 13:10

I think I´m understanding... I really don´t remember exactly why these fields are made so. This project has more than 2 years. On that occasion I was using the (arg!) IB Objects, and I was starting in use DBs. Now this project is running in a client and I need to improve it. I was affraid to change things that could be dangerous there. This means that some principles maybe aren´t needed now and I possibly can change it. This project has passed for a lot of changes until now. I´ll study it, and I´ll tell you what I´ll do. thanks again.