Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Unanswered: OUTER LEFT JOINs on SQL CE

    Hi,

    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:

    SELECT A.*
    , 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 [7]", 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?

    Any other sugestions?

    Thank you,
    Hugo Lopes

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you by any chance trying to use this SELECT to define a cursor?

    -PatP

  3. #3
    Join Date
    Feb 2008
    Posts
    4
    Hi,

    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?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

  5. #5
    Join Date
    Feb 2008
    Posts
    4
    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.

    Hugo Lopes

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I know that you're just trying to solve your problem, but that answer just stinks.

    That boils down to: "We've got bugs in our software. Why don't you deliberately code some bugs into yours to compensate for our bugs?"

    Arrrgh! That is just SO wrong for so many reasons.

    -PatP

  7. #7
    Join Date
    Feb 2008
    Posts
    4
    Ok, finally found what the problem was .

    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.

    //resultSetOptions = (resultSetOptions |
    // System.Data.SqlServerCe.ResultSetOptions.Updatable );

    Thanks for your help Pat .

    Hugo Lopes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •