Solved .NET Provider 12.0.1 and Stored Procedure with Parameters

Report SqlBase bugs and possible workarounds.
Clifford Bass

.NET Provider 12.0.1 and Stored Procedure with Parameters

Post by Clifford Bass » 22 Apr 2016, 00:41

Hi,

The new .NET provider 12.0.1 does not pass parameters properly to stored procedures. They all seem to come through as null. Here is code I am using. It works fine when tweaked to use the 11.7.3 .NET provider.

Code: Select all

REMARK
\
Add the following two custom errors to your error.sql.

30001 SVM 001 Parameter 1 Cannot Be Null.

      Reason: You did not provide a value for strParam1.

      Remedy: Fix your program.

30002 SVM 002 Parameter 2 Cannot Be Null.

      Reason: You did not provide a value for nParam2.

      Remedy: Fix your program.
/

store SysAdm.An_SP
PROCEDURE: An_SP
  PARAMETERS
    Receive String strParam1
    Receive Number nParam2

  ACTIONS
    On Procedure Execute
      If strParam1 = String_Null
        return 30001
      If nParam2 = Number_Null
        Return 30002
/
commit;

execute SysAdm.An_SP
\
,123
/

execute SysAdm.An_SP
\
"ABC",,
/

execute SysAdm.An_SP
\
"ABC",123
/

Code: Select all

      private void btnTestIt_Click(object sender, EventArgs e)
      {
         SQLBaseConnection sqlConn = new SQLBaseConnection(
                "DataSource=exsample.com;" +
                "Port=2155;" +
                "Database=Island;" +
            "SSL=Always;" +
            "UserID=SYSADM;" +
            "Password=" + txtPassword.Text + ";" +
            "INI=C:\\Program Files\\Gupta\\SQLBase120\\sql.ini");
         SQLBaseCommand myCommand = new SQLBaseCommand();
         SQLBaseDataReader sbDR;
         try
         {
            sqlConn.Open();

            myCommand.Connection = sqlConn;
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = "SysAdm.An_SP";

            myCommand.Parameters.Clear();
            myCommand.Parameters.Add(":1", DbType.String).Value = "Some Data";
            myCommand.Parameters.Add(":2", DbType.Int32).Value = 1234;

            sbDR = myCommand.ExecuteReader();
                if (sbDR.Read())
                MessageBox.Show("It Worked!\r\n\r\n" +
                   sbDR.GetString(0) + "\r\n" +
                   sbDR.GetInt32(1).ToString());
         }
         catch (Exception ex)
         {
            MessageBox.Show(ex.Message);
         }
         finally
         {
            myCommand.Dispose();

            if (sqlConn.State != ConnectionState.Closed)
               sqlConn.Close();
            sqlConn.Dispose();
         }
I will provide a Visual Studio project for testing it.

Clifford Bass

tfahey
United States of America
Posts: 99
Joined: 30 Mar 2017, 21:57
Location: La Crosse Wisconsin USA

Re: .NET Provider 12.0.1 and Stored Procedure with Parameters

Post by tfahey » 14 Jun 2016, 15:26

Hi Mike and Cliff,
Just so you know, we have reported a similar problem in TD Mobile 2.0.2. In our case the invocation of a stored procedure must be in capital letters for the procedure to receive the parameters. (weird!) We suspect that TD Mobile 2.0.2 is now using the 12.0 driver pack. All the previous versions of Mobile were working beautifully!
This is reported as TDM-1237 and we are waiting for a fix.

tfahey
United States of America
Posts: 99
Joined: 30 Mar 2017, 21:57
Location: La Crosse Wisconsin USA

Re: .NET Provider 12.0.1 and Stored Procedure with Parameters

Post by tfahey » 22 Jun 2016, 23:00

Yes, things are working fine now! 8)

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests