Reusing LLBLGen Pro Project with different DB-Driver

Posts   
 
    
Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 10-Jan-2007 16:32:18   

(Using LLBLGen 2.0.0.0; VB.net 2.0; Runtime version 2.0.0.61130)

We'd like to be able to reuse an LLBLGen designer project for different types of databases, but haven't found a good way to do that yet.

Is there a way to change the databasedriver in a project?

We built a project for SQL Server 2005 and now would like to use exactly the same project for Access.

(Or maybe try the same for Firebird f.e.)

Entities are not so much a problem like f.e. customized Typed Lists.

Is there a way to change the database driver of an existing project? Or maybe extract the "non driver" parts of the project into a new project? Or copy objects like typed lists form one project to another?

It's no fun creating (or maintaining) two or more practically identical projects that ony should be different in the database driver being used. (and is also a possible source of problems)

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 10-Jan-2007 16:34:49   
Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 11-Jan-2007 11:13:06   

It seems this tool cannot really be used to convert a Project form SQL Server to Access.

The converted Project looks right at first sight, but if you try to "Refresh this catalog..." all entities and typed lists get removed from the project because "Target schema 'dbo' is missing."

The tables in the new Access project are still referenced as dbo.Tablename. This of course does not work in Access.

(The dbo prefix came automatically to the Sql Server project. There was no selection to be made.)

Wouldn't it be better to use the old/existing project as some "choices the user has made" and use these choices against and with the new target database instead of converting the project without ever even touching the new target database?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 11-Jan-2007 11:51:26   

Marti wrote:

It seems this tool cannot really be used to convert a Project form SQL Server to Access.

The converted Project looks right at first sight, but if you try to "Refresh this catalog..." all entities and typed lists get removed from the project because "Target schema 'dbo' is missing."

The tables in the new Access project are still referenced as dbo.Tablename. This of course does not work in Access.

(The dbo prefix came automatically to the Sql Server project. There was no selection to be made.)

After you've converted to access, load the project, go to catalog explorer, rightclick the dbo schema, select rename, rename it to 'Default', save, refresh simple_smile

The converter doesn't rename schemas.

Wouldn't it be better to use the old/existing project as some "choices the user has made" and use these choices against and with the new target database instead of converting the project without ever even touching the new target database?

Could you elaborate a bit? I don't really understand what you mean with your question.

Frans Bouma | Lead developer LLBLGen Pro
Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 11-Jan-2007 12:50:27   

I didn't know you could rename anything in catalog explorer. :-)

Most of my entities can be migrated this way, however many "Custom relation"s are removed and so are some of my Typed Lists. I didn't asume any relations should be missing in the Access database, but I will check that...

Otis wrote:

Marti wrote:

Wouldn't it be better to use the old/existing project as some "choices the user has made" and use these choices against and with the new target database instead of converting the project without ever even touching the new target database?

Could you elaborate a bit? I don't really understand what you mean with your question.

Those are my ideas how you could go about changing database type in a project.

Normally when creating a project the designer takes the users choices against what it knows about the existing database. Similar when refreshing -> entities get "migrated"/updated. If there is a change in the database (even a little one) that would be relevant to the project, I would refresh the catalog to get the project adapted to the new situation. In contrast the project converter does not care about the target database at all.

The most intuitive way of changing the database type in a project seems to me:

  • Selecting another driver somewhere in the project properties and creating a connection string (all just like when creation a new project)
  • then kind of refreshing the catalog with the new connection (to the new database type) (If it can be refreshed this way, you probably have something that can also be used successfully.)

That would have to take changing schema names into account and maybe there are some other non trivial problems I wouldn't know about...

Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 11-Jan-2007 13:26:29   

Constraints in the db seem to be all right.

When I use the same relevant entities in a new project I get all relations, even those the converted project has thrown out.

When I refresh the converted project a second time it seems to get the relations it has previously thrown out, but it's allready to late as the typed lists that depended on those are allready gone.

jeffreygg
User
Posts: 805
Joined: 26-Oct-2003
# Posted on: 11-Jan-2007 22:16:29   

If you take a freshly converted project and rename the schema before you do a refresh, do you have the same problem? Once the schema has been renamed to "default" (before any refreshes are done), you shouldn't have any further problems.

Jeff

Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 12-Jan-2007 10:07:11   

Not renaming "dbo" to "Default" before refresh I loose all Objects (entities and typed lists). (I didn't try anything else with the then empty project.)

Renaming "dbo" to "Default" (It seems the capital "D" is important) before refresh showed the following behaviour (to state it more explicitly than above):

  • first refresh takes away some "Custom relation"s and the depending typed lists.

  • second refresh returns the relations (FK constraints) that were thrown out in the first refresh (as it now is able to find them in the database; I don't know why it wouldn't find them in the first refresh)

It does not return the typed lists the first refresh has trown out. (They are gone and are something that was defined in the project, not in the database therefore I haven't tried yet a third refresh.)

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 12-Jan-2007 10:37:01   

Marti wrote:

Not renaming "dbo" to "Default" before refresh I loose all Objects (entities and typed lists). (I didn't try anything else with the then empty project.)

Renaming "dbo" to "Default" (It seems the capital "D" is important) before refresh showed the following behaviour (to state it more explicitly than above):

  • first refresh takes away some "Custom relation"s and the depending typed lists.

  • second refresh returns the relations (FK constraints) that were thrown out in the first refresh (as it now is able to find them in the database; I don't know why it wouldn't find them in the first refresh)

It does not return the typed lists the first refresh has trown out. (They are gone and are something that was defined in the project, not in the database therefore I haven't tried yet a third refresh.)

It might indeed be better to pre-convert the schema name to 'Default', I'll look into that. About removing the relations: it rebuilds all relations and thus it should be the case that the relations between the entities stay and the typedlists stay. What is the exact message you get in the log shown after the refresh? Why are the relations removed, that should be mentioned there.

Also, after you've converted and load the project (before the refresh), are there relations in the project?

Frans Bouma | Lead developer LLBLGen Pro
Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 12-Jan-2007 13:44:54   

I assume some relevant sections will do, let me know if you need anything else. (I would prefer not to post the whole log here.)


...
"Custom relation 'Ansprechpartner.AnredeId - Anrede.AnredeId (m:1)' removed because one or more of its elements were removed during the process or have changed (not in the PK anymore etc.)."
...
->
"Typed List 'LstFirmAsps' removed because the relation 'Ansprechpartner.AnredeId - Anrede.AnredeId (m:1)' was removed from the project.
...

I haven't had the time to research into what the common criterion of the removed relations is yet, it was not obvious to me at first sight. (Maybe I can have a closer look at that later)

Some relations are removed, some not.

If I recall that correctly, the message "Entity migrated." was usually the last entry for any specific entity when refreshing. At the second refresh when the reations come back however there are sometimes more relations added after that message:


"Entity 'Ansprechpartner'
                    New relation added: 'Ansprechpartner.AdrTitel - Titel.TitelId (m:1)'
                    Entity migrated.
                    New relation added: 'Ansprechpartner.FirmId - Firma.FirmId (m:1)'
                    New relation added: 'Ansprechpartner.AdrId - Adresse.AdrId (m:1)'
                    New relation added: 'Ansprechpartner.AnredeId - Anrede.AnredeId (m:1)'
"

Do you know why this could be?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 12-Jan-2007 15:18:07   

Looks like some PK fields are no longer PK fields after the conversion. Could you check that in the entities where the relations are removed, the fields on teh PK side of the relation (e.g. customer - order, then the PK side is customer) are still a PK?

Frans Bouma | Lead developer LLBLGen Pro
Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 12-Jan-2007 16:43:06   

All entities seem to have their PK intact. (Fieldname(PK) in Designer; the ones I looked into also had "Is PK" = True in Editor.

Also all user tables in the access database have PKs.

When it removes the relations (first refresh) the "Entity migrated." Message is always last.

Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 12-Jan-2007 17:11:33   

Otis wrote:

Also, after you've converted and load the project (before the refresh), are there relations in the project?

(I had overlooked that question before.)

Yes, there are quite a lot of relations in the project. (As mentioned above: some are removed with the first refresh, some are not.)

Chester
Support Team
Posts: 223
Joined: 15-Jul-2005
# Posted on: 14-Jan-2007 22:18:28   

Would it be possible for you to attach two files to this message:

1) .SQL script that would generate your SQL Server database 2) zipped copy of your Access database

If you'd rather not post the db's it's understandable, but it's difficult to come up with answers to unseen problems. I started down the path of trying to recreate your problem with my own test db, but it's not leading to any concrete ideas about your issues.

Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 15-Jan-2007 12:35:24   

Chester wrote:

Would it be possible for you to attach two files to this message:

1) .SQL script that would generate your SQL Server database 2) zipped copy of your Access database

If you'd rather not post the db's it's understandable, but it's difficult to come up with answers to unseen problems. I started down the path of trying to recreate your problem with my own test db, but it's not leading to any concrete ideas about your issues.

I do not wish to publish those files, but I posted them to the helpdesk forum (with the same thread title) so you can use them to track the problem.

Marti
User
Posts: 39
Joined: 29-Nov-2006
# Posted on: 24-Jan-2007 15:50:50   

With the newly available version of the project converter, the conversion from SQL Server to Access seems to be solved.

I assume you should always refresh catalogs after conversion. In my case (Access as target database) the refresh completes the conversion by changing text fields with greater size than 255 to Memo fields.

(Still it's required to change the schema name from "dbo" to "Default".)

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 24-Jan-2007 16:04:58   

Thanks for posting the resolution in the public forum.