Using variables to create SQL strings

[davidlhoffman]

I know that CCX DB Read and DB Write steps let you create SQL statements that include variables as parameters using the "$" prefix.  But is there any way to build the entire SQL string "by hand" (perhaps with the "Set" step) and pass that to the DB Read or Write step in its entirety?

I guessing not, but thought I'd ask.  Many thanks from this newbie for any feedback.

[Anthony Holloway]

I don't believe so, but, then again have you tried it?  I don't have a premium system to try it on, and it sounds like you might.  It would be a trivial task to perform if you did.  Otherwise, someone else will have to give it a go.

Anthony Holloway

Please use the star ratings to help drive great content to the top of searches.

[davidlhoffman]

Thanks for the reply, Anthony.  I've tried everything I can think of.  There's definitely no dropdown available as there is in other steps where the editor expects a variable.

As a long shot, I tried simply entering "$sql" and then put my full SQL statement in that one variable.  (Failed.)  I tried using the $ prefix for fieldnames but it only works for data values.

I would welcome any other "tricks" but I suspect they've intentionally added limitations to keep people out of trouble.  I think I read somewhere in the docs that access to stored procedures is forbidden but I'm going to try anyway....

Thanks again-

-David

[Gergely Szabo]

Hi,

just out of curiosity, may I ask you why you need to have the possibility of "hand crafting" an INSERT or an UPDATE SQL statement?

I checked the docs, there's a note at the section describing the SQL tab of the DB Write step: "Enter the SQL UPDATE, SQL DELETE, or SQL INSERT statements, using standard SQL syntax." So no way of having an expression that may result in a String that would ultimately be an INSERT or an UPDATE.

If you are not afraid of an adventure and some programming, then check this approach:

https://supportforums.cisco.com/docs/DOC-29776. The Create URL Document step allows an expression for the URL, which would generate a String according to the result of the expression. For instance, based on the availability of an entity, it would generate an URL pointing to the "insert" or to the "update" method.

G.

[davidlhoffman]

Gergely, I appreciate the feedback.  The short answer to why I want to do this is that I'm new to CCX scripting and I'm just trying to get a handle on what I can and cannot do.

I typically build my SQL commands in code before executing them, sometimes using several string variables that I concatenate at the last moment.  One variable might store the SELECT specification, then I might have several different WHERE clauses to choose from, depending on certain conditions.  Sometimes I'll swap in different JOIN logic or ORDER BY specs, for various reasons.

Besides the flexibility, another advantage is that, when debugging, I can stop and take a look at the finished query before executing it.  Better yet, I can copy-and-paste it into a tool like MSSQL Management Studio and see the results (or errors).

I'm sure I can make do with what's built into the Cisco editor.  I just like to know what my options are.

I did read your Grails article a few days ago.  It's fascinating, but probably overkill in my case, especially since I don't have admin privileges on my client's servers.

Thanks-

-David

[davidlhoffman]

I just did a SELECT from a MSSQL UDF, so there's hope....

Best-

-David

[Gergely Szabo]

"Pics or it didn't happen!"


Sent from Cisco Technical Support Android App

[davidlhoffman]

You're saying you don't believe me?    Hmmm, I'll have to figure out the best way to document....

I can report that I was not able to pass in a CCX variable as a parameter to the function, though I could use one in a WHERE clause.

[davidlhoffman]

Sorry, I'm trying to upload images but am getting this error:

"An error occurred trying to retrieve data from server. Please try again in a few minutes. Error Code: 'tinyMCEPopup' is undefined"

[davidlhoffman]

Documentation attached as PDF document....

Best-

-David      

[Gergely Szabo]

Hi,

er... read your document twice and I am still kind of lost.

I'll give it a try in the afternoon again.

G.

[davidlhoffman]

Darn.  I was aiming for clarity.  Sorry I didn't do better.  Will try to answer questions if you have them.

In case it's a help, this excellent article is what got me going on my approach:

http://www.sommarskog.se/share_data.html

Best-

-David

[Gergely Szabo]

OK, now I got that. Actually I was kind of focusing on the original request (dynamically generating INSERT or UPDATE statements) and I did not really understand what you want with reading info out of a DB using functions.

Anyway, good luck.

G.

[davidlhoffman]

Yes, sorry, I kind of veered from my original topic.  I'm basically looking for any options that will make dealing with the database more flexible and "natural" for me.

Thanks for your feedback and interest-

-David