locked
COALESCE function problem RRS feed

  • Question

  • Hi, I downloaded "Microsoft SQL Server Compact Edition" v3.1, installed, created database in VS2005, created table TESTTABLE with one column TEST NVARCHAR 200 and run this command against it:

     

    SELECT coalesce(test,NULL) from testtable

     

    but it says

     

    Error : The function is not recognized by SQL Server Compact Edition. [ Name of function = coalesce,Data type (if known) =  ]

     

    In this forums there are more usages of coalesce function, so I think it is supported and fully functional, but where is the problem, then ?

     

    My system is Windows Vista Business (Czech version), version of sqlcese30.dll and sqlceqp30.dll is 3.0.5300.0.

     

    thank you for suggestions

     

    J.S.

    Friday, September 14, 2007 8:24 AM

Answers

  • Hi Jan.

     

    This piece of code breaks with the error you mention using SQL Compact 3.1 in VS 2005, but works (res = "no value") with SQL Compact 3.5 beta 2 and VS 2008 beta 2:

     

    Code Snippet

    SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf;");
    conn.Open();
    SqlCeCommand cmd2 = new SqlCeCommand();
    cmd2.CommandText = "SELECT COALESCE(Region,@p1) FROM Customers WHERE [Customer Id] = 'ALFKI'";
    cmd2.Parameters.AddWithValue("p1", "no value"); 
    cmd2.Connection = conn;
    object res;
    res = cmd2.ExecuteScalar();

     

     

    So time will mend all wounds!

    Monday, September 17, 2007 3:52 PM

All replies

  • The COALSECE finction works fine in SQL Compact 3.1, it is a problem with the tool you are using. Have successfully tested in SQL Server Management Studio SP2. It is a problem with the Visual Studio Server Explorer query parser.

    Friday, September 14, 2007 9:51 AM
  • But, I'am not using any tool - I am using it from application (.NET 2.0). I will make a sample...

     

    Friday, September 14, 2007 9:55 AM
  •  

    Hmm, i dont't know where was the problem, but now problem is another:

     

    The specified argument value for the function is not valid. [ Argument # = 2,Name of function(if known) = coalesce ]

     

    my source code i used:

     

    string fn = args[0];

    string cs = "Data source='"+fn+"'";

    SqlCeConnection conn = new SqlCeConnection(cs);

    conn.Open();

    //only if table does not exists

    //SqlCeCommand cmd1 = new SqlCeCommand("CREATE TABLE TESTTABLE ( TEST NVARCHAR(200) NULL )", conn);

    //cmd1.ExecuteNonQuery();

    SqlCeCommand cmd2 = new SqlCeCommand();

    cmd2.CommandText = "SELECT COALESCE(TEST,@p1) FROM TESTTABLE";

    cmd2.Parameters.AddWithValue("p1", "no value");

    cmd2.Connection = conn;

    SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd2);

    DataSet ds = new DataSet();

    adapter.Fill(ds);

     

    another command

    SELECT COALESCE(TEST,'no value') FROM TESTTABLE

    is OK, but this is not my scenario...

     

    I think the problem is in parameter - it cannot be used in COALESCE function ?

     

     

    Friday, September 14, 2007 12:15 PM
  • Looks like you must change the statement to not use parameters!

    Friday, September 14, 2007 5:09 PM
  •  

    But this is against th best practices - to use parameters instead of literals...

    Of course, I am using SqlServerCe as alternative data provider (user can configure SqlServer/SqlServerCe) and i dont want to write another (bad!) code to achive same functionality...

     

    any ideas ? will this be supported in another version ?

    thank you

    Monday, September 17, 2007 10:43 AM
  • Hi Jan.

     

    This piece of code breaks with the error you mention using SQL Compact 3.1 in VS 2005, but works (res = "no value") with SQL Compact 3.5 beta 2 and VS 2008 beta 2:

     

    Code Snippet

    SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf;");
    conn.Open();
    SqlCeCommand cmd2 = new SqlCeCommand();
    cmd2.CommandText = "SELECT COALESCE(Region,@p1) FROM Customers WHERE [Customer Id] = 'ALFKI'";
    cmd2.Parameters.AddWithValue("p1", "no value"); 
    cmd2.Connection = conn;
    object res;
    res = cmd2.ExecuteScalar();

     

     

    So time will mend all wounds!

    Monday, September 17, 2007 3:52 PM
  • Oh ! Great, this is, what I want to read...

    Thank you for your quick and brilliant answers.

     

     

    Tuesday, September 18, 2007 8:16 AM
  • Hi Erik

     

    I have no problem using SqlCe Native provider with parameters but I can't seem to make it work using the OleDb provider=

    Microsoft.SQLSERVER.MOBILE.OLEDB.3.0.

    Exception "The given type name was unrecognized. [,,,,,]" thrown whenever paramaters were used in the sql command.

    Consider the following:

    String msg = "";

    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=" + openFileDialog1.FileName);

    //OleDbConnection conn = new OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=.");

    try {

      DataTable table = new DataTable();

      conn.Open();

      OleDbDataAdapter da = new OleDbDataAdapter("select Id, CompanyName, ContactName, Address from \"Customer\" " +

        " where (CompanyName > ?) ", conn);

      da.SelectCommand.Parameters.AddWithValue("@v0", "T");

      da.Fill(table);

      mDataGridView.DataSource = table;

    } catch (Exception ex) {

      msg = ex.Message;

    } finally {

      conn.Close();

      mStatus.Text = msg;

    }

     

    Again, the same code works when using the OleDb provider=SQLOLEDB.1 and Native SqlCe provider System.Data.SqlServerCe.

     

    Does Sql Compact Edition 3.1 supports command parameters? Or do we have to wait until 3.5?

    Please advise. Thanks.

     

    Jason

     

    Wednesday, October 10, 2007 7:06 PM
  • The 3.1 OLEDB provider has problems with parameters. You must use System.Data.SqlServerCe client.

    The good news are, however, that parameters work with the OLEDB provider in 3.5 

    I have tested your code with 3.5 beta 2, and the table fills without problems.

    The provider name in 3.5 is: Microsoft.SQLSERVER.CE.OLEDB.3.5

     

     

    Thursday, October 11, 2007 5:41 PM
  • Thank you for the quick reply.  We'll support the native client now and test the OLEDB provider when 3.5 is released.

     

    Jason

     

    Thursday, October 11, 2007 8:31 PM
  • The System.Data.SqlServerCe provider also has serious issues with passing null values using parameterized queries.  This is in 3.1 and results in nasty errors about the IsNull function (used internally by the Prepare method).

     

    So a simple where-clause like "where @mycol is null" will fail if passing a null (or DBNull.Value in C#) on the parameter's Value property.  The extreme workaround is to do something akin to the following...

     

    if (param.Value == null)

    {

    cmd.CommandText = cmd.CommandText.Replace(param.ParameterName, "null");

    }

     

    This is despite marking the parameter as nullable.  I am still hoping for time to mend some SQL wounds...

    Wednesday, January 16, 2008 11:34 PM
  • One more issue with parameters (tested on SqlCe 2005 beta 2)

    You cannot use same parameter twice in one query:

    SELECT * FROM TABLE where COL1=@p1 or COL2=@p1

     

    which is not so big problem, but if you want to group by expression made with parameters, you got problems

    SELECT COALESCE(COL1,@p1), count(*) FROM TABLE GROUP BY COALESCE(COL1,@p2)

    @p1='no value', @p2='no value'

    you got some SQL error, that you can group only by expressions used in select clause!

     

     

    Thursday, January 17, 2008 9:12 AM