We have a working application that uses llblgen against SQLServer.

Now we have to modify this project so it can work with an Informix database instead of a SQLServer DB. The ideal solution would be to regenerate the dal for Informix, but there are no drivers for informix.

Apart from rewriting the biz and dal layers to access directly the Informix DB, Is there any other approach we could try?

Thank you in advance.

That will be a bit difficult, I'm afraid. Informix support isn't planned as well.

If you haven't used any sqlserver specific types (GUID's, bit), you could try to alter the sqlserver specific templates to work with informix' .net provider (if that exists) and also use the SqlServer DQE sourcecode (part of the runtime libraries) and rework that so it produces informix compatible code.

I'm totally unfamiliar with informix however, so I have no idea how much it looks like sqlserver (as in: does it use catalogs or does it use schema's like oracle? does it support identity columns or does it use sequences?)

In general, writing a driver for informix and a DQE (plus a couple of template modifications) is less work than re-doing an entire dal. Most of the time a database looks similar to one we already support, like oracle, or firebird, and you can base your driver on the sourcecode of that driver, the same goes for the DQE. Templates specific for a driver are in general pretty easy to port from one db to another, and if you don'tuse procs it's an hour or so.

Thank you for your help, we appreciate it.

I didn't mention it in my first post (although you may have deducted it) but we are only interested in the adapter scenario.

We haven't used any sqlserver specific types and Informix .net provider exists, therefore we are going to try your advice: Modify sqlserver specific templates to work with informix and rework SqlServer DQE sourcecode so it produces informix compatible code.

This is a new field for us, because we have never created any templates and we don't know the internal working details of llblgen.

It would be very helpful if you could suggest us any resource or documentation about how to create new drivers, templates and DQE sourcecode in llblgen.

Thank you again

Writing a driver is basicly copying the driver sourcecode of the driver which db looks closest to the db you want to write the driver for. Then it's a challenge to get all the meta-data you need out of the db. This can be easy if the .net provider offers schema methods, like the firebird provider does, but it can be a struggle if you need to parse meta-data from system tables. The sourcecode for all databasedrivers is available in the SDK in the extras section.

The DQE follows the same route, and in 1.0.2005.1 it's made easier to write a DQE as you just have to override the methods you need to customize, e.g. the methods which produce the actual queries.

If Informix supports ansi-joins (INNER JOIN/LEFT JOIN), it's easier, as you can simply use teh RelationCollection.ToQueryText() method as used in for example the sqlserver dqe to produce FROM clauses, otherwise you have to produce non-ansi joins and that's located only in the oracle odp.net DQE as thats the only place where non-ansi joins are required.

The templates are the smallest step. In the Drivers folders you find driver specific templates. They mostly differ in DQE namespace references and the stored procedure call code in teh dataaccessadapter template.

FOr a driver, FIRST create a new GUID, and place that in the dbdriver class in the property for the ID, also place it in the driver.config file for the informix driver.

The system first reads catalogs, which are displayed in the list of catalogs to select from, OR can produce schemas. To connect to a db, it uses a control which implements a special interface. This control is shown in the create project/refresh catalog dialogs. It is used to obtain connection info to produce a connection string.

I'm not sure what informix uses, schemas or catalogs or files. All kinds of systems are already available, so you can check how to produce the proper code for catalog/schema or file based databases. The Dbdriver class produces a list of catalog objects to fill, which is then filled by the catalogretriever. The catalogretriever mainly retrieves schema objects which are filled by the schemaretriever. The schema retriever reads for a given schema the various elements. Please check the sourcecode in the SDK, it's well documented and pretty straight forward.

When you're done reading meta-data, the designer will be able to work with it and you can create entities.

A driver has its own folder in the Drivers folder, so you should create an Informix folder in the Drivers folder. In there you place the dll and the driver.config file. Also the Templates folder for the specific templates for informix, as well as the templateset config file has to be placed there (just copy one from another db, and alter a couple of specific settings, it's just xml, straightforward).

To debug a driver, place the .pdb file also in the drivers folder, run the designer, create a project in the designer and you should hit the breakpoint set in the driver code.

We have created the driver for Informix. We also created templates for adapter c# visual studio 2003. But most of the changes we have made were in the DQE.

Starting from the sources for SQLServerDQE we have created InformixDQE. It doesn't have been as difficoult as we expected, altought there might be hiden bugs.

We have discovered that the version of informix that we use (wich is not the latest one) cannot make RIGHT JOINS, but can make LEFT JOINS. Is there a recommended way to convert RIGHT JOINS to LEFT JOINS in the DQE?

Thank you.

Hmm. Well, it's a bit complicated.

The absense of RIGHT joins makes it difficult because if you have this: A LEFT JOIN B C LEFT JOIN B

You can't make that query. It should become: A LEFT JOIN B RIGHT JOIN C or C LEFT JOIN B RIGHT JOIN A

So I'm a bit puzzled how informix would be able to deliver the proper results without problems AND not using a right join, but perhaps I miss something.

A LEFT JOIN B RIGHT JOIN C

But, Is not A LEFT JOIN B RIGHT JOIN C_equivalent to_C LEFT JOIN (A LEFT JOIN B) ?

C LEFT JOIN B RIGHT JOIN A

Similary, C LEFT JOIN B RIGHT JOIN A_equivalent to_A LEFT JOIN (C LEFT JOIN B) ?

Am I confused?

nachanga wrote:

A LEFT JOIN B RIGHT JOIN C

But, Is not A LEFT JOIN B RIGHT JOIN C_equivalent to_C LEFT JOIN (A LEFT JOIN B) ?

C LEFT JOIN B RIGHT JOIN A

Similary, C LEFT JOIN B RIGHT JOIN A_equivalent to_A LEFT JOIN (C LEFT JOIN B) ? Am I confused?

No you're not, I didn't think of those!

Hmm, ok how to embed this in your code. I think you have 2 options. 1) look into the ToQueryText() code in RelationCollection, that's the join code you need to change. You can grab that code and copy it to the DQE and alter it. Then, in the select query construction and other places where relationcollection.ToQueryText() is called, call your own routine in the DQE 2) create a different RelationCollection class and alter the code there. This only is useful in selfservicing.

I'd go for option one. I've done that too for the non-ansi, Oracle specific joins for 8i in the oracle dqe.

Hello,

I am researching LLBLGen as an option for an upcoming project. I too will need to support Informix. Can you tell me which driver you chose as the base for your informix driver? Any other useful information would be great as well.

Thanks!

I think it's best if the customers I'm aware of who wrote an informix driver (at least 2) should bundle their workforce and work together. We can host the code in our subversion repository if that's ok with you, nachanga.

Sorry for the delay. I should check this thread more often.

I think it's best if the customers I'm aware of who wrote an informix driver (at least 2) should bundle their workforce and work together. We can host the code in our subversion repository if that's ok with you, nachanga.

That's a good idea! . But I warn that we just made the modifications that we needed. It only works for adapter in vs2003 and C# and only with the DBTypes that we needed. The modifications that we did only work with Informix .Net Provider 2.81 and we are sure there are lots of bugs and big mistakes. But it worked for us so far.

That said, we are more than happy to share the code if it can help anybody.

Now for something completly different, we found out that the Informix .net provider returns a 'string' when the DBType is a 'Char(m)' if m>1, but it returns a 'System.Char' when DBType is 'Char(1)'. We tried to define a TypeConverter in the LLBLGen pro Designer to make 'Char(1)' behave like a 'string' but there are not defined converters for strings.

Any suggestion of how to make 'Char(1)' behave as a 'string'?

Thanks in advance and sorry for my English mistakes.

nachanga wrote:

Sorry for the delay. I should check this thread more often.

I think it's best if the customers I'm aware of who wrote an informix driver (at least 2) should bundle their workforce and work together. We can host the code in our subversion repository if that's ok with you, nachanga.

That's a good idea! . But I warn that we just made the modifications that we needed. It only works for adapter in vs2003 and C# and only with the DBTypes that we needed. The modifications that we did only work with Informix .Net Provider 2.81 and we are sure there are lots of bugs and big mistakes. But it worked for us so far.

That said, we are more than happy to share the code if it can help anybody.

Yesterday we contacted you about this, as another customer is busy with an informix driver and saw this thread and asked us if we could contacted you so you two could work together. Did you receive that email?

Now for something completly different, we found out that the Informix .net provider returns a 'string' when the DBType is a 'Char(m)' if m>1, but it returns a 'System.Char' when DBType is 'Char(1)'. We tried to define a TypeConverter in the LLBLGen pro Designer to make 'Char(1)' behave like a 'string' but there are not defined converters for strings.

Any suggestion of how to make 'Char(1)' behave as a 'string'?

Thanks in advance and sorry for my English mistakes.

You can define a type converter very easily In the SDK there's the sourcecode for the typeconverter shipped with llblgen pro. It's a matter of changing some lines of code to make it convert a different type to a different other type, like char to string. Then just compile it and place it in the typeconverters folder. It's then picked up after you restart llblgen pro and you should be able to use it in the designer.

I've needed an Informix driver for years. Of course, I could never get around to writing one, but would be intersted in testing, fixing and working with those that already have the base stuff done. I'll need mine to work with 2005 as well, so I might be able to contribute at that level.

Thanks.

Otis wrote:

nachanga wrote:

Sorry for the delay. I should check this thread more often.

I think it's best if the customers I'm aware of who wrote an informix driver (at least 2) should bundle their workforce and work together. We can host the code in our subversion repository if that's ok with you, nachanga.

That's a good idea! . But I warn that we just made the modifications that we needed. It only works for adapter in vs2003 and C# and only with the DBTypes that we needed. The modifications that we did only work with Informix .Net Provider 2.81 and we are sure there are lots of bugs and big mistakes. But it worked for us so far.

That said, we are more than happy to share the code if it can help anybody.

Yesterday we contacted you about this, as another customer is busy with an informix driver and saw this thread and asked us if we could contacted you so you two could work together. Did you receive that email?

Now for something completly different, we found out that the Informix .net provider returns a 'string' when the DBType is a 'Char(m)' if m>1, but it returns a 'System.Char' when DBType is 'Char(1)'. We tried to define a TypeConverter in the LLBLGen pro Designer to make 'Char(1)' behave like a 'string' but there are not defined converters for strings.

Any suggestion of how to make 'Char(1)' behave as a 'string'?

Thanks in advance and sorry for my English mistakes.

You can define a type converter very easily In the SDK there's the sourcecode for the typeconverter shipped with llblgen pro. It's a matter of changing some lines of code to make it convert a different type to a different other type, like char to string. Then just compile it and place it in the typeconverters folder. It's then picked up after you restart llblgen pro and you should be able to use it in the designer.

You can grab the informix drivers from:

svn://www.sd.nl/LLBLGenPro/CustomDrivers/Informix

from our subversion repository.

I'm new to LLBLGen.

I'm currently looking for solutions for porting Informix Databases to SQL Server.

I'm interested in using a Code Generation product to read the schema of an Informix database and generate Micrsoft SQL Server DDL scripts.

Am I understanding this thread correctly that LLBLGen didn't offer support for providing schema information from an Informix database? And that the drivers required to process the schema of an Informix database in LLBLGen can be found in your subversion repository?

Will I be able to use LLBLGen with your DDL templates (that I saw you released recently in another thread), against an Informix database with this Informix driver?

Is there any other way of getting a copy of these drivers other than through subversion?

micklang wrote:

I'm new to LLBLGen.

I'm currently looking for solutions for porting Informix Databases to SQL Server.

I'm interested in using a Code Generation product to read the schema of an Informix database and generate Micrsoft SQL Server DDL scripts.

Am I understanding this thread correctly that LLBLGen didn't offer support for providing schema information from an Informix database? And that the drivers required to process the schema of an Informix database in LLBLGen can be found in your subversion repository?

Yes. Though the driver/DQE aren't ported to v2.0.

Will I be able to use LLBLGen with your DDL templates (that I saw you released recently in another thread), against an Informix database with this Informix driver?

No, informix isn't supported. the driver/dqe was made by a 3rd party (a customer)

Is there any other way of getting a copy of these drivers other than through subversion?

No, we don't actively distribute these drivers/dqe, only the subversion repository is there so customers could check out the code and work on it for their own situation.

The converter and DDL scripts are provided as sourcecode as well. You could alter these to make them support informix, however we won't support informix, also because we don't have access to informix nor experience with informix.