[sqlite] Is there any book (books??) to help us creating a bidirectional Sync tool?

2007-08-31 Thread jfbaro

Hi guys,

We have been looking for books which cover the DB synchronisation subject.
We have started creating a plugin to our socket server but we quickly
realised how much of a challenge it would be (We are creating it "from
scratch"). The conflict problems, the order to tables (rows) be synchronised
(Child - Parent) and how to delete rows were some of the problems we found.
The main goal is to synchronise devices in the field (Compact framework) to
the server using less bandwich as possible.
We are trying to be "agnostic" about the DB in both Server and Client side.

We are pretty sure there are books out there which cover this subject, we
just couldn't find them! :confused:

We know this will be a challenge but we also know it will be a great feature
to add to the server.

Does anyone could point us out to any book?

Cheers

Jean Baro
Dynamic Devices Ltd
-- 
View this message in context: 
http://www.nabble.com/Is-there-any-book-%28books--%29-to-help-us-creating-a-bidirectional-Sync-tool--tf4361009.html#a12429220
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Trigger UPDATE OF problems

2007-01-08 Thread jfbaro

Hi,

We need to use a trigger to update the column sync into a table when any
column is changed (except the sync column itself), so by looking in the
SQLite website we came across that:

CREATE TRIGGER syncTransactions AFTER
UPDATE OF transactionId, transactionJourneyId, transactionDeviceId,
transactionSignificant ON transactions
BEGIN
  UPDATE transactions SET sync = 0 WHERE transactionId = old.transactionId;
END;

But even when we change sync column (by issuing a update command on SQLite
Expert tool) the trigger is executed. Shouldn't it be execute only when we
update one of the columns specified in the clause UPDATE OF??

Another question, is it possible to have in the UPDATE OF clause something
like "ALL BUT sync"?? So if we add new columns to that table we wouldn't
need to change the trigger. (I don't thing that is possible though).

Thanks
-- 
View this message in context: 
http://www.nabble.com/Trigger-UPDATE-OF-problems-tf2939798.html#a8219304
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] Reusing SQLiteCommand

2006-12-13 Thread jfbaro


Hi,

I am new to C# and SQLite and I am having problems using Paramaters.Add...

I prepare the "INSERT" statement and inside a loop I set the values for each
parameter and call ExecuteNonQuery();
Something similar to that:

checkCmd.CommandText = "SELECT userId FROM users WHERE
userId = @userId";

IDbDataParameter userIdPar = checkCmd.CreateParameter();

userIdPar.ParameterName = "@userId";

userIdPar.DbType = DbType.String;
checkCmd.Parameters.Add(userIdPar);
   
updateCmd = conn.CreateCommand();
updateCmd.CommandText = "UPDATE users SET userName =
@userName, userPin = @userPin WHERE userId = @userId";
IDbDataParameter userIdUp = updateCmd.CreateParameter();
userIdUp.ParameterName = "@userId";
userIdUp.DbType = DbType.Int32;
IDbDataParameter userNameUp = updateCmd.CreateParameter();
userNameUp.ParameterName = "@userName";
userNameUp.DbType = DbType.String;
IDbDataParameter userPinUp = updateCmd.CreateParameter();
userPinUp.ParameterName = "@userPin";
userPinUp.DbType = DbType.String;
updateCmd.Parameters.Add(userIdUp);
updateCmd.Parameters.Add(userNameUp);
updateCmd.Parameters.Add(userPinUp);


insertCmd = conn.CreateCommand();
insertCmd.CommandText = "INSERT INTO users (userId,
userName, userPin) VALUES (@userId, @userName, @userPin)";
IDbDataParameter userIdIn = insertCmd.CreateParameter();
userIdIn.ParameterName = "@userId";
userIdIn.DbType = DbType.Int32;
IDbDataParameter userNameIn = insertCmd.CreateParameter();
userNameIn.ParameterName = "@userName";
userNameIn.DbType = DbType.String;
IDbDataParameter userPinIn = insertCmd.CreateParameter();
userPinIn.ParameterName = "@userPin";
userPinIn.DbType = DbType.String;
insertCmd.Parameters.Add(userIdIn);
insertCmd.Parameters.Add(userNameIn);
insertCmd.Parameters.Add(userPinIn);


 while (objBinaryReaderRx.BaseStream.Position < PacketSize)
{

int userId = objBinaryReaderRx.ReadInt32();
string userName = objBinaryReaderRx.ReadString();
string userPIN = objBinaryReaderRx.ReadString();

userIdPar.Value = userId;   

readerCheck = checkCmd.ExecuteReader();

if (readerCheck.Read())
{
userIdUp.Value = userId;
userNameUp.Value = userName;
userPinUp.Value = userPIN;

updateCmd.ExecuteNonQuery();
}
else
{
userIdIn.Value = userId;
userNameIn.Value = userName;
userPinIn.Value = userPIN;

insertCmd.ExecuteNonQuery();   
}

readerCheck.Close();
}
   

transaction.Commit();

return true;

catch{...}
finally{...}

The first INSERT works fine, but the second time I get: "Operation is not
valid due to the current state of the object."

I am using .NET 1.1, Finisar 1.1 and SQLite 3

Thanks in advance
-- 
View this message in context: 
http://www.nabble.com/Reusing-SQLiteCommand-tf2816640.html#a7861212
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] SQLite for .NET 1.1?

2006-12-11 Thread jfbaro

Hi!

Where can we find ADO for .NET 1.1?

Thanks in advance
-- 
View this message in context: 
http://www.nabble.com/SQLite-for-.NET-1.1--tf2796567.html#a7802832
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



[sqlite] SELECT FOR UPDATE

2006-11-16 Thread jfbaro

Hi,

We are newbie to SQLite and we would like to lock a table through a select +
update.

Something like that:

BEGIN TRANSACTION

   currentValue = select currentValue from sequence_transactions;   

   update sequence_transactions set currentValue = currentValue + 1;

   COMMIT;

END TRANSACTION

When the program reachs the select, we would like other threads /
applications wait for that transaction to finish.

Is there any way of achiving that?

Cheers


-- 
View this message in context: 
http://www.nabble.com/SELECT-FOR-UPDATE-tf2645383.html#a7384913
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-