Using parameters: Get SQL sent to database

[Armin Zingler]

Hi,

if I use parameters with an OleDbCommand, the OleDbCommand builds the final
SQL string to be send to the database. How do I retrieve this SQL? Example:

Commandtext = "insert into test (id, value) values (?, ?)"
Value of param1 = 17
Value of param2 = "param2"

SQL sent to database when calling ExecuteNonquery will be:

insert into test (id, value) values (17, "param2")

How do I get this sql? I want to write them into a log file. There is no
MyCommand.GetFinalSQL or such. (Framework 1.1 or 2.0)


Armin

 

[Cor Ligthert [MVP]]

Armin,

Does this simple sample answer both questions (otherwise reply)?

http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

You can use of course direct a DateTime value as well and with that
addseconds.

Be aware that in this in 2.0 are some changes, although the 1.x versions
works as well.

I hope this helps,

Cor

 

[Guest]

Dear Armin Zingler,


For time being, i will tell you one round about way / alternate way like

You can create another dummy command object for example

Dim cmd2 As New OleDbCommand
cmd2.CommandText = "Insert into test (id, value) VALUES('" &
cmd.Parameters(0).Value & "','" & cmd.Parameters(1).Value & "')"

HERE YOU HAVE TO WRITE THEM INTO A LOG FILE

cmd2.Dispose()
cmd2.Close()

THEN REST OF YOUR CODE

NOW YOU WILL GET YOUR FINAL QUERY AS

insert into test (id, value) values (17, 'param2')


For Anything & Everything, Please Let Me Know,

Bye
Venkat_KL

 

[Armin Zingler]

Dear Armin Zingler,


For time being, i will tell you one round about way / alternate way
like

You can create another dummy command object for example

Dim cmd2 As New OleDbCommand
cmd2.CommandText = "Insert into test (id, value) VALUES('" &
cmd.Parameters(0).Value & "','" & cmd.Parameters(1).Value & "')"

HERE YOU HAVE TO WRITE THEM INTO A LOG FILE

cmd2.Dispose()
cmd2.Close()

THEN REST OF YOUR CODE

NOW YOU WILL GET YOUR FINAL QUERY AS

insert into test (id, value) values (17, 'param2')


For Anything & Everything, Please Let Me Know,

This answer is really funny. I want to get the SQL that the OleDbCommand
object sents to the database. If I build another one on my own, I still
don't see the one that is built automatically. But thanks for trying to help
me, anyway.


Armin

 

[Armin Zingler]

Armin,

Does this simple sample answer both questions (otherwise reply)?

No, but none of them.

http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

You can use of course direct a DateTime value as well and with that
addseconds.

You are right, both questions are related. First I didn't know how to
include the milliseconds in the SQL. I thought: Let me see which SQL string
does the OleDBCommand build and send to the database if one parameter is a
DateTime variable containing milliseconds. Then I wanted to look at the SQL
string and see how it is done. Unfortunatelly, there's (still) no way to get
the SQL. Isn't that strange: You are a programmer and can not find out which
SQL your own program sends to the database! (Well, it might be possible with
tools coming with the DBMS, but it must be possible in your own code!).


Armin

 

[Guest]

Dear Armin Zingler,

The reply looks like funny only, but it will solve your probelm which you
had asked How do i get the finaly SQL. You try out, it will give you the SQL
Query that you want to log.

In the Second Command Object, I am building the straight away the SQL &
Passing the First Command Object's Parameter's Values.

I have tried out, I am getting, then you should also get.

If your requirement is different, Please Let me Know.

Bye
Venkat_KL

 

[Armin Zingler]

Dear Armin Zingler,

The reply looks like funny only, but it will solve your probelm
which you had asked How do i get the finaly SQL. You try out, it
will give you the SQL Query that you want to log.

It does not give me the SQL query I want to log. It gives me the string I
build on my own. I want the string that is sent to the database by the
OleDbCommand.

In the Second Command Object, I am building the straight away the
SQL & Passing the First Command Object's Parameter's Values.

I have tried out, I am getting, then you should also get.

If your requirement is different, Please Let me Know.

Yes, it is different. As I wrote, I do not want to know how I can build the
string on my own. I want to know how I can get the string that the
OleDbCommand builds and sends to the database.


Armin

 

[Cor Ligthert [MVP]]

Armin,

From your answer I am still not sure if you got the answer, assuming it was
not, it will be something as (where the date is now + 100 milliseconds)

Commandtext = "insert into test (id, value) values (?, ?)"
cmd.Parameters.AddWithValue("0", "17") 'VBNet 2005
cmd.Parameters.Add("1", (Now.AddSeconds(1/10)) 'VBNet 2002/3


Be aware that this is the first time, the next time it is
cmd.Parameters(0).value = 'etc.

I assume that you know how to set your param2 to a datetime value.

Be aware that the datetime format of your database is important with that,
if you know that from Access, than I will be pleased if you can tell me
that. In SQLServer it are units of milliseconds*10/3.


Is this more clear?

Cor

 

[Armin Zingler]

Armin,

From your answer I am still not sure if you got the answer, assuming
it was not, it will be something as (where the date is now + 100
milliseconds)

Commandtext = "insert into test (id, value) values (?, ?)"
cmd.Parameters.AddWithValue("0", "17") 'VBNet 2005
cmd.Parameters.Add("1", (Now.AddSeconds(1/10)) 'VBNet 2002/3


Be aware that this is the first time, the next time it is
cmd.Parameters(0).value = 'etc.

I assume that you know how to set your param2 to a datetime value.

Be aware that the datetime format of your database is important with
that, if you know that from Access, than I will be pleased if you
can tell me that. In SQLServer it are units of milliseconds*10/3.


Is this more clear?

I know how to execute the command. And it works. This is not the problem.
What I want to see is the *exact SQL* that the OleDBCommand sends to the
database.


Armin

 

[Patrice]

I would log just the CommandText and the parameters values which should be
enough. AFAIK the actual SQL could be different depending on the underlying
DB (and this is likely why you can't get at it).

For the date/time issue I would check first the DB I'm using allows for this
precision (also some DBs are provided several precisions for datetime
value). Start first to see if the type you are using allows for this
precision.

 

[Armin Zingler]

I would log just the CommandText and the parameters values which
should be enough. AFAIK the actual SQL could be different depending
on the underlying DB (and this is likely why you can't get at it).

Yes, it could be different, but the OleCBCommand *is* able to build the SQL.
So I don't know why I can't ask for it.

For the date/time issue I would check first the DB I'm using allows
for this precision (also some DBs are provided several precisions
for datetime value). Start first to see if the type you are using
allows for this precision.

The problem is not the precision. I was only looking for the right *format*
for milliseconds. I can insert date/time values containing milliseconds
using parameters without a problem, so it must be possible in general. If I
could get the SQL string that the Oledbcommand builds, I would see the
format it uses.


Armin

 

[Patrice]

What is the DB ?

--

Yes, it could be different, but the OleCBCommand *is* able to build the SQL.
So I don't know why I can't ask for it.



The problem is not the precision. I was only looking for the right *format*
for milliseconds. I can insert date/time values containing milliseconds
using parameters without a problem, so it must be possible in general. If I
could get the SQL string that the Oledbcommand builds, I would see the
format it uses.


Armin

 

[Armin Zingler]

Does this matter? I don't get the SQL from the OleDBCommand, no matter which
database. It's Access.

Armin

 

[Guest]

Hi Dear Armin Zingler,

Initially you asked,

insert into test (id, value) values (17, "param2")

How do I get this sql?

Now you are telling That is not you wanted, you wanted the command object
generated SQL.

Command Object will not generate any SQL, It will only execute what ever SQL
Syntax you give it to that.

With Declaring Parameters and adding them to Parameters collection and
addding it to the command object, You can not get the SQL Query what you
asked initially.

That is, I will copy and paste here

insert into test (id, value) values (17, "param2")

How do I get this sql?

first of all it is not double quotes, it is single quotes for the string or
char values
like

insert into test (id, value) values (17, 'param2')

TO GET THIS QUERY, I SHOWED YOU THE METHOD.

Correct me If I am wrong, You want to log the SQL Queyries along with what
ever value user inputs right!!

That's all from my side

bye
Venkat_KL


TO GET THIS

 

[Armin Zingler]

Hi Dear Armin Zingler,

Initially you asked,

insert into test (id, value) values (17, "param2")

How do I get this sql?

Before I wrote:

"SQL sent to database when calling ExecuteNonquery will be:"

Yes, the mentioned SQL is the "SQL that is sent to the database", and I want
to know how I can "get this SQL". Thus, "this SQL" = "SQL sent to the
database". The "Insert into.." is only an example - or do you think I always
want to insert 17 and "param2"?

Now you are telling That is not you wanted, you wanted the command
object generated SQL.

Command Object will not generate any SQL, It will only execute what
ever SQL Syntax you give it to that.

With Declaring Parameters and adding them to Parameters collection
and addding it to the command object, You can not get the SQL Query
what you asked initially.

That is, I will copy and paste here

insert into test (id, value) values (17, "param2")

How do I get this sql?

Yes, "this sql". The "SQL sent to the database" as mentioned in the sentence
that you did not quote here again.

first of all it is not double quotes, it is single quotes for the
string or char values
like

insert into test (id, value) values (17, 'param2')

Both is possible. Single or double quotes. I've always used double quotes
without a problem. (Of course, doesn't matter when using parameters.)

TO GET THIS QUERY, I SHOWED YOU THE METHOD.

<quote>
if I use parameters with an OleDbCommand, the OleDbCommand builds the final
SQL string to be send to the database. How do I retrieve this SQL?
</quote>

Obviously, "this SQL" refers to the sentence before that also mentions the
"SQL string to be send to the database." Maybe you lost the context.

Thus, you did NOT show me how to get the "SQL that is send to the database".
What you showed me is: a way to build an SQL that is hopefully equal to the
one that is send to the database. That's *the* difference, and that's why I
first wrote that your answer is a little bit funny (BTW, with a smiley
added). I said it's funny because we have Parameters now and we don't have
to build the SQL on our own, but if I want to have a look at the SQL that is
send to the database, you told me I have to build it on my own. Just to
explain it.

Correct me If I am wrong, You want to log the SQL Queyries along
with what ever value user inputs right!!

Right, but logging is only an example. It should be possible in general to
get the SQL I send to a database.


Armin

 

[Cor Ligthert [MVP]]

Armin,

The parameter is as well used in the Stored Procedure and sent as a
parameter to the Database and not as a part of a SQL string.

Therefore I think that it is in your efforts more interesting how fysically
the database that you use receives a datetime parameter for an SP without
ADONET, than you have probably as well your other answer.

I would if I needed it in your case ask that in the SQL server newsgroup.

Just my thought,

Cor

 

[Cor Ligthert [MVP]]

Armin,

If it was not somebody as you I would not trust it.

(It is mostly easily to trace where fakes are sending from, I did not do
that in this case)

I have never seen this question in this newsgroup and than today we saw
this.

http://groups.google.com/group/micr...abb0d82bbbc/2447f12d5078a768#2447f12d5078a768

I think that it helps in your question.

Cor

 

[Armin Zingler]

Armin,

The parameter is as well used in the Stored Procedure and sent as a
parameter to the Database and not as a part of a SQL string.

I don't understand you. I don't use a stored procedure. I'm still using an
OleDbCommand object.

Therefore I think that it is in your efforts more interesting how
fysically the database that you use receives a datetime parameter
for an SP without ADONET, than you have probably as well your other
answer.

I would if I needed it in your case ask that in the SQL server
newsgroup.

I'm using Access.

I really don't understand what's so difficult with this question. I only
want to know, if it's possible to get the SQL that an OleDBCommand builds
and sends to the database. The answer is yes or no. If no, the question is
answered. If yes, I'd like to know which property or method returns the SQL.
I didn't find one, therefore I asked. Obviously there is no such method. A
simple "MyCommandObject.GetFinalSQL" or similar would be sufficient. I don't
see a reason why this final SQL is hidden to us.

Ok, it seems it is not possible and I am not able to see the SQLs that is
send to a database if I use parameters. Strange to say this about my own
program.

Thanks, Cor for trying to help.


Armin

 

[Armin Zingler]

Armin,

If it was not somebody as you I would not trust it.

(It is mostly easily to trace where fakes are sending from, I did
not do that in this case)

I have never seen this question in this newsgroup and than today we
saw this.

http://groups.google.com/group/micr...abb0d82bbbc/2447f12d5078a768#2447f12d5078a768

I think that it helps in your question.

Thanks for the link. I've searched before but didn't find it.
Yes, the question was the same. Still I think, tracing would be much simpler
if there was a simple simple MyCommandObject.GetFinalSQL method. I missed it
in Framework 1.1 and obviously it's still not there in Framework 2.0 now.
Disappointing.

Thanks again.


Armin

 

[Armin Zingler]

Ok, now I see it's from today. That's why I didn't find it *before*.
BTW, he almost literally chose the same words.


Armin