Print Page | Close Window

Querying on array fields

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2523
Printed Date: 28-Mar-2024 at 6:55am


Topic: Querying on array fields
Posted By: stevenr
Subject: Querying on array fields
Date Posted: 21-Feb-2011 at 7:56am

My application is a distributed system where data entered by multiple users gets saved to a central database and then each user must be able to synchronize with the central database to receive the latest changes.  The system  uses timestamp columns to track whether records in certain tables have changed. In the synchronization method, I would like to formulate a query to download only those records that have changed on the server, rather than downloading the tables in their entirety. In other words, on a given client, I would like to get the highest timestamp value locally and then query the server for higher timestamp values to receive only the records that have changed since then. The tables do have datetime auditing fields, but I'd rather not rely on querying these because if the clock is not set properly on the client or the server, this would cause problems.
The timestamp columns are added to my model as fields of type byte[]. How do I write a query that uses this byte[] field in the where clause? I've tried the obvious:

// maxLocalRowVersion contains the maximum local row version
qry.AddClause(MyType.RowVersionEntityColumn, EntityColumnOp.GT, maxLocalRowVersion);

This statement fails with the error: The 'In' and 'Between' operators can only be used with Collection values (and vice versa)
 
I've also tried to set the field type to Int64 in the model, but then I get an exception that the byte[] type cannot be converted to Int64. How do I handle this?




Replies:
Posted By: kimj
Date Posted: 21-Feb-2011 at 1:08pm
I don't think you can query on the Timestamp column using an RdbQuery, but you can do this with a PassthruRdbQuery.  Something like the following:
 
RdbParameter parm = new RdbParameter("ts"DbType.Binary);
parm.Value = maxLocalRowVersion;
ParameterizedSql sql = new ParameterizedSql("select * from mytable where lastupdate > ?", parm);
PassthruRdbQuery passThruQuery = new PassthruRdbQuery(typeof(MyType), sql);
You can use the AdoHelper if you need to use provider-specific parameter and placeholder names.



Print Page | Close Window