SQL Query into a STRING$

Question

I have a MySQL query (about 20 lines long). I want to know if there is an online something or other that can make it into a string for my code. For example...

   SELECT
   `tblapplied`.*
   , `tbljobs`.*
   , CONCAT(salutation," ",firstname," ",lastname)
   , `tblusers`.`id`
   , `tblcountry`.`countryname`
   , `tblquestions`.`question`
   , `tblquestions_1`.`question`

Becomes

MySql = "SELECT"
MySql += "`tblapplied`.*"
MySql += ", `tbljobs`.*"
MySql += ", CONCAT(salutation," ",firstname," ",lastname)"

and so on, you get the picture.. I ask because I keep on having to embed SQL into code and would like to be able to quick convert it to a string$

Many Thanks

[This question has some answers](http://stackoverflow.com/questions/706382/multiline-strings-in-vb-net) addressing the multiline query problem by using XML literals in CDATA. — Michael Berkowski, Oct 07 '14 at 16:49

score 0 · Answer 1 · answered Oct 07 '14 at 18:00

I would just create a little program to do this yourself. I work often between the database and VB and I have a form/app with about 20 buttons on it that will do various things with the data in my clipboard. For example:

Converting the selected items from a query result into a comma separated list so I can use it in an IN() operator
Remove square brackets
Convert multiple lines of SQL into something for VB, and the reverse of this

I used to do a lot of this with macros in VS but I guess I was the only one that used macros because VS doesn't support them anymore.

score 0 · Accepted Answer · answered Oct 07 '14 at 18:00

I do this all the time with a text editor that allows me to search for and replace end of line characters with new characters.

My editor of choice is NoteTab Pro, but I am sure there are many others.

In this text editor, a line end is represented by ^P, so I would copy the raw SQL into a new document and replace

^P

with

"^P MySql += "

This will add a double quote at the end of each line and MySql += " at the beginning of the next line.

This has saved me tons of time over the years and is very simple to use.

One other comment: if this were my code, I would be using StringBuilder instead of string concatenation since concatenation will generate a lot of unnecessary string objects to clean up.

There is also another very powerful tool in our arsenal that we use for automatically generating SQL (such as add or insert stored procedures) and code (such as DTO classes) from existing database tables or views: CodeSmith.

This tool allows you to write code that can format output to whatever you need it to be. It does have a little ramp up time and wouldn't be applicable if you are writing SQL in the runtime tool to test it prior to moving it into code, but it is good to be aware of for other tasks.

Event better than StringBuild, Line Continuation. IE, 'MySql = "SELECT" & _ "`tblapplied`.*"' — Steve, Oct 07 '14 at 18:02
@Steve: good point, but only if MySQL is defined as a Const (which many programmers don't often do); otherwise the & is concatenating each of the constant strings into a variable, so you have the excess object problem again. — competent_tech, Oct 07 '14 at 18:06
Good point about the combining of strings with the `&`. I never thought of that. While I don't create them as constants, I usually use the STATIC keyword with a parameterized string. This way it is only dimensioned once anyway. — Steve, Oct 07 '14 at 18:55

SQL Query into a STRING$

2 Answers2