Greetings! This is my first time using Automise. I am used to several scripting languages (Perl and TCL mainly), so that is where I am coming from.
I am working on porting a Perl script to Automise logic. I am using a CSV iterator to get data from a file and assign it to variables. Now I want to run a SQL statement updating data in our database with the variable contents (and using one of the variables for the key value for the update). How do I run a SQL statement and include variables in it?
Apologies if this has been covered elsewhere - I did a search for SQL and Variables in this forum, but I did not see anything that answered my question.
Thanks,
Jonathan
Hi Jonathan,
The action your looking for to run SQL on a MSSQL server is “MSSQL Execute SQL”. This will let you define the SQL you would like to run against an MSSQL server. It can take a script from a file, variable, or static text. To use a variable in the input area use the variable syntax of %MySQLStatement%. Not that this can be combined with static text around it.
Thank you for that! That is very helpful.
Followup question - how are quote marks handled in the context of a SQL statement? Do quotes need to be put around a variable if quotes will surround the data? Or does Automise look at the variable type (string, integer) and decide whether to use quote marks accordingly?
Also, if a variable is used in a SQL statement, and the variable contains quote marks, will the quote marks be escaped correctly, or do I need to double the quote marks in the variable value before including the variable in the SQL statement?
Thanks,
Jonathan
Hi Jonathan,
FinalBuilder doesn’t perform any alterations to quotations in text variables. With regards to SQL statements, we simply quote the entire SQL string before passing it to be executed. The internals of the SQL statement should be quoted as required by SQL rules. So if a text value is included inside the SQL statement it should be quoted as well.
Some examples:
Variables:
%MyWhere% = WHERE 1=2
%MyWhereStr% = WHERE FieldOne = “One”
%MyFieldOneWhere% = One
Usages:
SELECT * FROM tblMyTable %MyWhere%
SELECT * FROM tblMyTable %MyWhereStr%
SELECT * FROM tblMyTable WHERE FieldOne = “%MyFieldOneWhere%”
Note that when using a string value I have quoted it at the location it was used. On the last example you could quote inside where the value is used, or in the variable itself.
With regards to escaping quotation marks, this shouldn’t be required. As stated above the whole string will be quoted when passed over for execution.
I am not sure how you can use a double quote around a string value in MS SQL Server, though…I always have to use a single quote mark at the start and end of the string. In that case, any single quotes inside the string itself will mess up the SQL unless they are escaped. Am I missing something?
Hi Jonathan,
No I don’t believe you have missed anything.
If your required to use single quotation marks for text, that will work as well. Take all my examples as having single quotations if that is the requirement of the SQL server. The resulting output will still be the same. Quotations (single or double) will not be changed. If you find an example of when they are please report it and I will get fixed or documented.
I hope that helps.
The example I have is that I have put a description into a %notes% variable, and then I am doing an INSERT statement to set a database field equal to that description. When I do not escape out the quotes inside the %notes% variable, then I get a SQL error if the %notes% variable holds a single quote. An example is if someone includes the word can’t in their notes. When the insert query is run, the apostrophe in can’t is seen as the end quote of the string, which causes an error.
Hi Jonathan,
The way I deal with escaping is to use the string replace actions before the string is used. So for example I will replace all occurrences of single quote, with the escaped version (double single quote). After this it should now work are required in the SQL query.
That is what I did, actually, to solve the issue. I misunderstood your statement above: “The internals of the SQL statement should be quoted as required by SQL rules.” I thought you were saying that FinalBuilder/Automise would take care of that for me. What you meant was that I had to do that myself to get it to work.
Hi Jonathan,
Yes, apologies for the misunderstanding. As different tools and situations require different escaping we leave that up to the user to determine when its required.