SACommand stored procedure parameters get messed up

[former_SQLA_member1694896]

Hello, all Some of our stored procedures contain numerous parameters and the client program is not supposed to pass all of them (some get default values). We see in SQL Anywhere debugger that the values of parameters received are not the ones in the Parameters array of the SACommand. Some parameters, which show empty (null) in the code is received as "1" for some reason. Our question: is there a way to see the actual SQL command being created by the .NET code, so we could see why the values are messed up?

Thank you Arcady

[chris_keating]

Have you checked the Results View of the Command.Parameters? The results view will enumerate the parameters of the command object.

[former_SQLA_member1694896]

The results view are correct, but the actual SQL is not. I worked around it by formatting the SQL string in C# code and launching the command as Text

[chris_keating]

Does a request level log (with at least SQL+HOSTVARS) show a difference in the parameter values being passed? For the following parameters to ShowSalesOrderDetail procedure,

cmd.Parameters.AddWithValue("@custid", 101);
cmd.Parameters.AddWithValue("@prodid", 301);

the request log output is

=,<,1,PREPARE,call "ShowSalesOrderDetail"(:?,:?)
+2,>,1,PREPARE,65538
+46,<,1,OPEN,65538
+1,H,1,,integer,101  
+1,H,1,,integer,301

The parameters will be listed below the OPEN line. For a NULL value such as in this code,

SAParameter parm = cmd.Parameters.Add("@custid", DbType.Int32 );
parm.Value = DBNull.Value;

the log will show

=,H,9,,integer,<null>

Do you have a repro that shows this behavior?

[jack_schueler]

Re: Our question: is there a way to see the actual SQL command being created by the .NET code

Yes, by setting the database server option "-zr sql". Use "-o logfile" to direct the output log to a file. You'll see all the SQL being sent from all clients to the server.

[former_SQLA_member1694896]

As I said, I worked around this problem by not using the StoredProcedure type command at all. So, now I got it working and, unfortunately, do not have time to create a repro.

Sorry, for the trouble