How can I expose SQL command under my control?

[Fulltimer]

This is probably not what you think.

I am constructing an SQL command that is stored to a variable. I use a typical statement such as:

sqlcommand1 = "insert into mytable (company, city, zip, phone, mydate ....) values ( :m_comp, :m_city, :m_zip, :m_phone, :m_date ......) "

My statement is a bit more robust, but you get the picture.


I want to see what this var parses out to be. In other words I want the ARGS to be expanded to what they are actually equal to. The reason
is I often get a flag error when I try to execute this statement in my code.

When I try to see the value in sqlcommand1, it shows the var just as listed above (with no expansion). I'm trying to see if there is something wrong
with my ARGS.

Anyone know how to do this? BTW I am not talking about the advanced setting of the show SQL command! This is not a SELECt statement.

[TaiTai]

Re: How can I expose SQL command under my control?

Hi Tom,

Not in front of A5 at the moment. Have a look at the property page in the designer (GRID or DETAIL if a GRID form, not sure where for Dialog) and you will see a checkbox for "Show Update SQL". Remember to turn it off before final publish. It will list the SQL and the ARGS

[Fulltimer]

Re: How can I expose SQL command under my control?

Hi Tom,

Not in front of A5 at the moment. Have a look at the property page in the designer (GRID or DETAIL if a GRID form, not sure where for Dialog) and you will see a checkbox for "Show Update SQL". Remember to turn it off before final publish. It will list the SQL and the ARGS

As stated in my original post, " I am not talking about the advanced setting of the show SQL command! This is not a SELECT statement."

Tom

[Peter.Greulich]

Re: How can I expose SQL command under my control?

Debug SQL.png

[christappan]

Re: How can I expose SQL command under my control?

In XBasic:

query = replace_arguments_in_string(sqlcommand1,args)

Note that it will put quotation marks around values that shouldn't have them, but you can at least tell if an argument isn't valid.

[Fulltimer]

Re: How can I expose SQL command under my control?

In XBasic:

query = replace_arguments_in_string(sqlcommand1,args)

Note that it will put quotation marks around values that shouldn't have them, but you can at least tell if an argument isn't valid.

Chris,

You suggestion was EXACTLY what I was looking for. This method (vs DEBUG) allows me to do my "own" analysis of the "string" being sent to the SQL command.
I may, depending on the results, opt to exchange some bit of data BEFORE it gets sent on to the EXECUTE function.

I am always so surprised when A5 has a pre-built function that is just what I need. The problem is, we often can't find it because of it's naming convention or some other quirky nuance that almost keeps us from finding it. Thankfully, this forum has so many engaged members that often one of those members will come to your rescue.

Thanks a lot.

Tom W.

[christappan]

Re: How can I expose SQL command under my control?

I tried for a long long time until I finally found out about this function somehow. I remember what it's like not to have it.

[Jay Talbott]

Re: How can I expose SQL command under my control?

Alternatively, couldn't you use a javascript alert box to show the command? That is how we often debug our SQL.
Jay

[Fulltimer]

Re: How can I expose SQL command under my control?

Alternatively, couldn't you use a javascript alert box to show the command? That is how we often debug our SQL.
Jay

Jay,

Don't know if javascript alert box would fit the bill here. First, at the point where I need it, I am using Xbasic. Second, not sure how you would do an analysis of an alert function. In other words, an alert might display the information to the human eye but that would not gain me access to that displayed data in case I want to make a programming decision (based on that data) in "real time". I could be wrong and am willing to entertain other schools of thought.

Tom W.