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

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

No, but none of them. :-)

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

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

Show quote Hide quote 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

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

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

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,

From the Jet engine of Access you find almost nothing about this on MSDN, I
asked you in this message thread if you even knew the internal format that
Access uses and you did not reply that to me..

I assume that you have seen this page?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp

Cor

Sorry

And in that the datetime and smalldatetime at the left side, this part from
MSDN doesn't return a good link

Cor

Sorry Cor, where? Which internal format? I don't care about the internal
format. This thread is about getting the SQL.

Sorry again, these are Tsql keywords I don't know what you are trying to
tell me. As I wrote, I'm using Access.

Sorry once more, I can only repeat myself if I write more.


Armin

Armin,

If Jet can not store milliseconds, that it is not important how you will get
that in a SQL string or whatever, I am myself interested if Access can do
that.

If it can do that, than the change that it divers in the SQL string with the
way it is done in Access in SQL server is mostly in this kind of
instructions.

It sound for me if you now are asking "I am using Access,  what operator do
I have to use to add".

This are the pages about SQL and specific Jet Access.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acadvsql.asp

Sorry if this is not clear.

Cor

Show quote Hide quote It can, otherwise I would not have asked how to store them. I already wrote
that inserting records with parts of a second into the database using
parameters works without a problem.

You didn't answer where you asked for this, but never mind.

It can.

Show quoteHide quoteI don't know what you're talking about, but thanks for the links....

For me is EOT here.


Armin

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/microsoft.public.dotnet.framework.adonet/browse_frm/thread/464f0abb0d82bbbc/2447f12d5078a768#2447f12d5078a768

I think that it helps in your question.

Cor

Show quote Hide quote 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

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

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

Show quote Hide quote 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

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

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.


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

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.

Show quoteHide quote

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

What is the DB ?

Show quoteHide quote

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

Armin


Show quoteHide quote

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

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"?



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


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


<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.


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


Armin

Armin Zingler wrote:

(A Gentle Introduction to ADO.NET 2.0 Trace Facilities):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/tracingdataaccess.asp

Show quoteHide quote

luxspes wrote:
Copy&Pasted from (A Gentle Introduction to ADO.NET 2.0 Trace Facilities):

Using Tracing to Debug a Parameter Binding Problem

Now that we've gone through the quick overview of tracing, I'd like to
present a simple use case. I'd often use ODBC trace to do problem
determination when an application would "eat" a rich error message and
produce a polite but fairly "information-free" message. Such application
code would look like this:

string s = GetConnectionStringFromConfigFile();
using (SqlConnection conn = new SqlConnection(s))
using (SqlCommand cmd = new SqlCommand(
   "select * from authors where au_id = @auid", conn))
{
   // the error is hardcoded here but could have come from suboptimal
   // editing in a graphic user interface
   cmd.Parameters.Add("@auid", SqlDbType.Int);
   cmd.Parameters[0].Value = 123456789;
   SqlDataReader rdr = null;
   try {
    // some code that could fail goes here
   conn.Open();
   rdr = cmd.ExecuteReader();
   while (rdr.Read())
      Console.WriteLine(rdr[0]);
   rdr.Close();
   }
   catch (Exception e) {
     MessageBox.Show("polite error message");
   }
}

In this case, the error was caused by a parameter type mismatch, and the
person diagnosing the error might not have access to the source code of
the program. Turning on the trace, we'll see output like this:

"enter_01 <sc.SqlCommand.ExecuteReader|API> 1#"
  "<sc.SqlCommand.get_Connection|API> 1#"
  "<sc.SqlCommand.get_Connection|API> 1#"
  "<sc.TdsParser.CreateSession|ADV> 1# created session 3"
  "<sc.TdsParserSessionPool.CreateSession|ADV> 1# adding session 3 to pool"
  "<sc.TdsParserSessionPool.GetSession|ADV> 1# using session 3"
  "<sc.TdsParser.GetSession|ADV> 1# getting session 3 from pool"
  "<sc.SqlCommand.ExecuteReader|INFO> 1#  Command executed as RPC."
  "<sc.SqlCommand.get_Connection|API> 1#"
  "leave_01"
  "enter_01 <sc.SqlDataReader.Read|API> 1#"
  "<sc.SqlError.SqlError|ERR> infoNumber=245  errorState=1  errorClass=16
errorMessage='Syntax error converting the varchar value '172-32-1176' to a
column of data type int.'  procedure=''  lineNumber=1"
  "leave_01"

This shows us directly that there is a parameter value mismatch. The
sample and the trace file are provided in the article code. Note that
the trace file is much more compact in this case because we're only
tracing with the System.Data.1 provider.

Luxspes,

I gave the link to your message to Rene already to Armin, I had never seen
this question and now almost exatly the same in one day (accoording to time
zones probably)

Cor

SQL Storage, Enums, Roles and Clean Coding !!!
Accept/Reject Cascading - DataSet
ADO.NET 2.0 TransactionScope object question: across business obje
SQL tables question
Alternative to using DSN to connect to database