Hi

I use NHibernate 1.2.0.2003
SQLite ADO 1.0.39.1
MySQL ADO 1.0.3.31712

I have a table 'users', a table 'credential' and a join table 'users_has_credential'.

I have a many-to-many relationship between 'users and' 'credential'.

I made a little test program to create a user, add some credential and save it into a database.

My program writes in MySQL and after in SQLite.
The idgenerator for both user and credential are unsaved-value="any" and <generator class="assigned" />.

I have a session factory for each database, mappping files are common.

The writing in MySQL works fine but the join table in SQLite is not filled. No SQL orders is issued.
Here's the log4j log:

DEBUG NHibernate.SQL - INSERT INTO users (login, sha1_password, salt, nom, prenom, mail, idusers) VALUES (?p0, ?p1, ?p2, ?p3, ?p4, ?p5, ?p6); p0 = 'bill', p1 = '55666646', p2 = '654646646546', p3 = 'Smith', p4 = 'Bill', p5 = 'bill.smith@mydomain.com', p6 = '302'
DEBUG NHibernate.SQL - INSERT INTO credential (libelle_credential, idcredential) VALUES (?p0, ?p1); p0 = 'reader', p1 = '452'
DEBUG NHibernate.SQL - INSERT INTO users_has_credential (users_idusers, credential_idcredential) VALUES (?p0, ?p1); p0 = '302', p1 = '452'
DEBUG NHibernate.SQL - INSERT INTO users (login, sha1_password, salt, nom, prenom, mail, idusers) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6); @p0 = 'bill', @p1 = '55666646', @p2 = '654646646546', @p3 = 'Smith', @p4 = 'Bill', @p5 = 'bill.smith@mydomain.com', @p6 = '302'
DEBUG NHibernate.SQL - INSERT INTO credential (libelle_credential, idcredential) VALUES (@p0, @p1); @p0 = 'reader', @p1 = '452'

As you can see the three first lines are for MySQL and the two last are for SQLite.
No SQL orders for the 'users_has_credential' for the SQLite database.

Does anyone know if SQLite has a problem for a many-to-many relationship ?
Does anyone managed to have SQLite work with a many-to-many relationship ?

Thanks !!!

I inverted the order of the writing, SQLite and MySQL after.

This time the relationship is fine in SQLite and not in MySQL.

The problem is not database specific.

I need to read data form MySQL and write the data in SQLite.

Wich action should I do or wich pattern should I use ?

Thanks !

What is the code that you are using?

I put my sample project in http://gentyjp.free.fr/nhb/testNhibernateConsole.zip.

It's a Visual 2005 project.

In testNhibernateConsole\testNhibernateConsole\dbLite there's a testNHB.MySQL.sql script to create the MySQL database.

In fact my problem is descrideb in the post http://forum.hibernate.org/viewtopic.php?t=969969

If I read data from MySQL and try to write it in SQLite, NHibernate keeps in "memory" the relationship and does not recreate it in SQLite, it only creates the Users and Credential records.

If I build a new record, everything works fine.

I can't figure the right pattern to also create the relationship in SQLite.

Thanks !!!

My problem is solved using Replicate.

The tables ans the relationship are created.