First of all, is this an appropriate place to get answers related to SQL CE? If not, do you have any recomended forums elsewhere?
I'm trying to get a list of various related tables using ResultSets on SQLCE 3. The query is something like this:
, F.Descricao AS FamiliasDescricao
, M.Descricao AS MarcasDescricao
, I.Descricao AS IVADescricao
FROM Artigos AS A
LEFT OUTER JOIN Familias AS F ON A.FamiliasUID = F.UID
LEFT OUTER JOIN Marcas AS M ON A.MarcasUID = M.UID
LEFT OUTER JOIN IVA AS I ON A.IVAUID = I.UID
INNER JOIN ArtigosTipos AS AT ON A.ArtigosTiposUID = AT.UID;
The column ArtigosTiposUID cannot be NULL, so an INNER JOIN is used, but the other UID columns can have a NULL value, and I need all the rows on Artigos to show up even if these other UIDs are NULL. The query runs fine like this in VS2005, returning NULL values for the columns if there are no rows on the other tables, both on the SQL Server 2005 database and the .sdf database used on the Windows Mobile device. But on Windows Mobile SQL CE gives me an "Unspecified error ", Native error 25607, an the stack trace ends with:
em System.Data.SqlServerCe.SqlCeCommand.ProcessResult s()
em System.Data.SqlServerCe.SqlCeCommand.CompileQueryP lan()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteComman d()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteResult Set()
If I replace all the LEFT OUTER JOINs with INNER JOINs only the rows where all UIDs have a value show up, but as I said, I want all rows on tabela Artigos. Even if I remove all JOINs except the last one and replace it with a LEFT OUTER JOIN I get the same erro, all rows having the column ArtigosTiposUID defined... it seems as if the simple presence of LEFT OUTER JOIN makes SQL CE return an error.
Is there a way to run the queries on VS using the SQL CE engine so that one can check whether the query will run successfuly on Windows Mobile?
I'm new to Microsoft technologies, so I'm not familiarized with the terminology. What do you mean with "define a cursor"?
I want a full list of rows as a ResultSet, that will be iterated with ResultSet.Read() and displayed on a Resco AdvancedList. I suppose the cursor points to a specific row while iterating through the ResultSet, right?
The SELECT statement that you've posted ought to run just fine "as is", but LEFT JOIN operations prevent a cursor from being updatable. The error number that you posted refers to a CURSOR, so I'd bet that the tools that you are using (probably the Resco AdvancedList constrol) use a cursor to populate the control.
The following is based on my understanding and assumptions about your environment. You need to keep in mind that I know nearly nothing, and have assumed a great deal, so you need to read this very critically.
IF my assumptions are correct, then there is probably a "read only" attribute (property) for the control. If you set that attribute so that data can only be retrieved, and not sent back to the database (usually via an UPDATE method), I would expect things to work better.
Thanks for your reply. It gave me some hope when I first read it, as I saw a possible solution in your assumption.
The data does not get to the control, as the error is returned directly by SQL CE when executing the query, so it must have to do with the ResultSet definition.
I'm using a custom ResultSet for the query, that includes all the main table colums plus the ones I need from each of the JOINed tables. Each ResultSet column has a ReadOnly property that I left as "False", so I thought that changing it to "True" on at least the external table columns would solve the problem. I've tried that but nothing changed. Then tried setting it to "True" on all columns, since I only use the ResultSet for listing anyway, but no luck... can think of no other way.
Microsoft recommends limiting the usage of outer joins on http://www.microsoft.com/technet/pro.../ssceqpop.mspx (Limit the Usage of Outer Joins) by using a value for "undefined" relations instead of leaving a foreign key as NULL. I think it is a good approach in my case.
I have some custom code that overrides each ResultSet class, setting the result options and declaring a new Open method that accepts an SQL query as a string. I was forcing the Updatable option on all ResultSets. After commenting the updatable option it works fine.