Greetings. My apologies for the lengthiness...
We are running an utility with about 5 clients using two DBs:
1. PrimaryPC
2. Shared folder
The PrimaryPC contains the original data and changes, while the SharedDB in
the Shared Folder is the one that provides original indexing of those
records. There are no duplicate indexes. When a new records is open, the
SharedDB provides the index and so, no two users will ever have the same
record and will never update the same record.
The SharedDB is the one that provides information about other users' records
to the other folks and so, an UpdateDB function was created to push updates
to the SharedDB from the PrimaryPC.
I will throw one more wrench in the motor... We have SharedPCs that when
users login will bring the data of that users from the SharedDB to this
SharedPC and thus the user can see his/her records and work without any
problem. However, the UpdateDB function does not work from a SharedPC
because it is not the PrimaryPC of the user.
The Update was working fine (and works fine this way) because I would attach
to the SharedDB and push all the records of the user to the SharedDB. But,
as the number of records grew, the it would take 20-30 seconds and sometimes
we find ourselves locking the SharedDB longer than needed.
I added a new column, XtraB, (Don-t ask), which changes every time a record
is edited and saved with the value 'yyyy-mm-dd hh:mm:ss'. I am trying to
change the UpdateDB function to only push the records that have been saved
in the PrimaryPC. Here is my programming steps that are not working:
dba = new SqliteDatabase(sqldba); //connect to the SharedDB. Works. :-)
q = "ATTACH '" ~ sqldb ~ "' AS client; "; // sqldb is the path to the local
PC. Works.
q = "BEGIN;";
q ~= "REPLACE INTO LSOpenProjects
SELECT * FROM client.LSOpenProjects
WHERE login)='" ~ pm["login"] ~ "' AND XtraB < client.XtraB; ";
q ~= "COMMIT;";
The above is not working... Yes, I am a newbie. Well, kinda, nonetheless,
yes.
What I would like to do is to push all the records that have a higher date
('yyyy-mm-dd hh:mm:ss') in the local DB than the SharedDB. They should
really be equal, if no changes have been made.
And then, the wrench in the motor is to bring from the sharedDB to the
SharedPC the records that are also with newer dates then the previously
brought from the SharedDB. I have something like above,
SqliteDatabase dbu = new SqliteDatabase(sqldb);
q = "ATTACH '" ~ sqldba ~ "' AS c; "; // works
q = "BEGIN;";
q ~= "INSERT OR REPLACE INTO LSOpenProjects
SELECT * FROM c.LSOpenProjects
WHERE login='" ~ pm["login"] ~ "' AND c.XtraB > XtraB; ";
q ~= "COMMIT;";
Not working either. Any help would be greatly appreciated.
thanks,
josé
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
