How to read commandtext after added parameters?

[Eric]

Hi,

If I construct a new Select command I use parameters to fill in the data i
want to query for.

Sometimes I make mistakes and I would then like to check the text directly
on the sql server. Before I started using parameters I could just copy the
commandtext to the server and see what happens, but if I do that now, I see
the parameters, not their values.

How can I see the commandtext as it is executed by the server?

i.e.

mycmd = new commandtext("Select * from Table1 Where user = @user",connection")
mycmd.parameters.add("@user",varchar,25).value = "myself"

I would like to see: "Select * from Table1 Where user = 'myself'"

Is this possible?

rg.
Eric

 

[Armin Zingler]

Am 24.03.2010 12:52, schrieb Eric:

Hi,

If I construct a new Select command I use parameters to fill in the data i
want to query for.

Sometimes I make mistakes and I would then like to check the text directly
on the sql server. Before I started using parameters I could just copy the
commandtext to the server and see what happens, but if I do that now, I see
the parameters, not their values.

How can I see the commandtext as it is executed by the server?

A very good question that I've also asked some years ago. (and in advance:
I still don't have a solution) I couldn't imagine that I, as a programmer,
am not able to see the SQL I'm sending myself to the database. For example,
I wanted to log them in a file. Logging the parameterized query and the
parameters seperately was and is not satisfying. In addition, I was trying
to quickly find out the format of some values (date format etc) so that
I could use it in queries inside Sql Server Management Studio, probably
just like you.

I was told to enable SQL logging in the DBMS. I still think this is looking
at the wrong end, but once again I seemed to be the only one that complained
- til now.

 

[Mark Hurd]

Am 24.03.2010 12:52, schrieb Eric:

A very good question that I've also asked some years ago. (and in
advance:
I still don't have a solution) I couldn't imagine that I, as a
programmer,
am not able to see the SQL I'm sending myself to the database. For
example,
I wanted to log them in a file. Logging the parameterized query and
the
parameters seperately was and is not satisfying. In addition, I was
trying
to quickly find out the format of some values (date format etc) so
that
I could use it in queries inside Sql Server Management Studio,
probably
just like you.

I was told to enable SQL logging in the DBMS. I still think this is
looking
at the wrong end, but once again I seemed to be the only one that
complained
- til now.

I think part of the problem is that the provider can decide to use
parameterisation to pass the base query once and then pass just the
parameters to the DBMS for each query.

What we want is a "debug mode" for the provider (or a different
provider) that does just pass unparameterised SQL each time, and then it
might as well make the final SQL available through a read only
parameter.

 

[Cor Ligthert[MVP]]

Eric,

Why inventing that wheel again with squares instead of a rounds.

In my gives SQL profiler you back all the information you ask about?

http://msdn.microsoft.com/en-us/library/ms187929.aspx

Cor

 

[Armin Zingler]

Am 29.03.2010 09:46, schrieb Cor Ligthert[MVP]:

Eric,

Why inventing that wheel again with squares instead of a rounds.

In my gives SQL profiler you back all the information you ask about?

http://msdn.microsoft.com/en-us/library/ms187929.aspx

How to determine the SQL I send to an Access database?
I send it but I don't know what I send.

 

[Cor Ligthert[MVP]]

Armin,

I expected this answer, but I've read first what the OP sent.

(I had almost connected my answer to your reply )

Sometimes I make mistakes and I would then like to check the text directly
on the sql server.

Is it really to expect that this is needed for an Jet database engine?

But nobody prevents you to get the Command.Text and replace the OleDB
parameters which are then unnamed (and in fact ?) that yourself.

I'm sure you know how to do that?



Cor

 

[Armin Zingler]

Am 29.03.2010 13:50, schrieb Cor Ligthert[MVP]:

Armin,

I expected this answer, but I've read first what the OP sent.

I have no doubts.

Is it really to expect that this is needed for an Jet database engine?

Yes, I needed it.

But nobody prevents you to get the Command.Text and replace the OleDB
parameters which are then unnamed (and in fact ?) that yourself.

I'm sure you know how to do that?

Even if I know how to do it, the String that I build is not the String
that is sent to the database (engine). The latter is what I want to see.
And if I already build the string myself, I could send it to the database
instead of using the Parameters property, but isn't the latter recommended?

 

[Cor Ligthert[MVP]]

Armin,

I am not so sure that a complete string is sent, the command procedure is
the same as with a stored procedure, where the mechanism of the parameters
is used.

http://msdn.microsoft.com/en-us/library/ms378675.aspx

Cor

 

[Bruno Ratnieks]

Hi,

I've seen lot's of developers suggesting users to write their own CommandText/Parameters parser.

Here my code snippet that will do the Job and may be easily converted to VB.Net.

Some basic integrity checks were done regarding quotes and backslashes but for sure this code need some care before use on production enviroment:

Note: You will need to change the type SQLiteCommand/SQLiteParameter to the one that matches your ADO methods set.


public static string getQueryFromCommand(SQLiteCommand cmd)
{
string CommandTxt = cmd.CommandText;

foreach (SQLiteParameter parms in cmd.Parameters)
{
string val = String.Empty;
if (parms.DbType.Equals(DbType.String) || parms.DbType.Equals(DbType.DateTime))
val = "'" + Convert.ToString(parms.Value).Replace(@"\", @"\\").Replace("'", @"\'") + "'";
if (parms.DbType.Equals(DbType.Int16) || parms.DbType.Equals(DbType.Int32) || parms.DbType.Equals(DbType.Int64) || parms.DbType.Equals(DbType.Decimal) || parms.DbType.Equals(DbType.Double))
val = Convert.ToString(parms.Value);
string paramname = "@" + parms.ParameterName;
CommandTxt = CommandTxt.Replace(paramname, val);
}
return (CommandTxt);
}

Best wishes,
Bruno Lacerda Ratnieks
CTO Sniffer.net
(e-mail address removed)

Hi,

If I construct a new Select command I use parameters to fill in the data i
want to query for.

Sometimes I make mistakes and I would then like to check the text directly
on the sql server. Before I started using parameters I could just copy the
commandtext to the server and see what happens, but if I do that now, I see
the parameters, not their values.

How can I see the commandtext as it is executed by the server?

i.e.

mycmd = new commandtext("Select * from Table1 Where user = @user",connection")
mycmd.parameters.add("@user",varchar,25).value = "myself"

I would like to see: "Select * from Table1 Where user = 'myself'"

Is this possible?

rg.
Eric

52, schrieb Eric:

A very good question that I have also asked some years ago. (and in advance:
I still do not have a solution) I could not imagine that I, as a programmer,
am not able to see the SQL I am sending myself to the database. For example,
I wanted to log them in a file. Logging the parameterized query and the
parameters seperately was and is not satisfying. In addition, I was trying
to quickly find out the format of some values (date format etc) so that
I could use it in queries inside Sql Server Management Studio, probably
just like you.

I was told to enable SQL logging in the DBMS. I still think this is looking
at the wrong end, but once again I seemed to be the only one that complained
- til now.

 

[Bruno Ratnieks]

Hi,

I've seen lot's of developers suggesting users to write their own CommandText/Parameters parser.

Here my code snippet that will do the Job and may be easily converted to VB.Net.

Some basic integrity checks were done regarding quotes and backslashes but for sure this code need some care before use on production enviroment:

Note: You will need to change the type SQLiteCommand/SQLiteParameter to the one that matches your ADO methods set.


public static string getQueryFromCommand(SQLiteCommand cmd)
{
string CommandTxt = cmd.CommandText;

foreach (SQLiteParameter parms in cmd.Parameters)
{
string val = String.Empty;
if (parms.DbType.Equals(DbType.String) || parms.DbType.Equals(DbType.DateTime))
val = "'" + Convert.ToString(parms.Value).Replace(@"\", @"\\").Replace("'", @"\'") + "'";
if (parms.DbType.Equals(DbType.Int16) || parms.DbType.Equals(DbType.Int32) || parms.DbType.Equals(DbType.Int64) || parms.DbType.Equals(DbType.Decimal) || parms.DbType.Equals(DbType.Double))
val = Convert.ToString(parms.Value);
string paramname = "@" + parms.ParameterName;
CommandTxt = CommandTxt.Replace(paramname, val);
}
return (CommandTxt);
}

Best wishes,
Bruno Lacerda Ratnieks
CTO Sniffer.net
(e-mail address removed)

Hi,

If I construct a new Select command I use parameters to fill in the data i
want to query for.

Sometimes I make mistakes and I would then like to check the text directly
on the sql server. Before I started using parameters I could just copy the
commandtext to the server and see what happens, but if I do that now, I see
the parameters, not their values.

How can I see the commandtext as it is executed by the server?

i.e.

mycmd = new commandtext("Select * from Table1 Where user = @user",connection")
mycmd.parameters.add("@user",varchar,25).value = "myself"

I would like to see: "Select * from Table1 Where user = 'myself'"

Is this possible?

rg.
Eric

52, schrieb Eric:

A very good question that I have also asked some years ago. (and in advance:
I still do not have a solution) I could not imagine that I, as a programmer,
am not able to see the SQL I am sending myself to the database. For example,
I wanted to log them in a file. Logging the parameterized query and the
parameters seperately was and is not satisfying. In addition, I was trying
to quickly find out the format of some values (date format etc) so that
I could use it in queries inside Sql Server Management Studio, probably
just like you.

I was told to enable SQL logging in the DBMS. I still think this is looking
at the wrong end, but once again I seemed to be the only one that complained
- til now.