Export query to clipboard as code

[MisterrMac]

AS I am sure many do, I use SQLyog to test my querys prior to actually placing them into my code pages. When I have generated the SQL I want, I copy and paste it into the code and go through the hastle of adding quotes and concating the lines. It would be so much easier if I could just click a button, go to my code and paste and each line would paste surrounded by quotes concated to the next, the last terminated. I'm not sure about other languages but this is an example for PHP:

Example:

SELECT *

FROM table

WHERE x=1

would paste as

“SELECT * ” .

“FROM table ” .

“WHERE x=1 “;

—

Loren McDonald

Managing Editor/Reviewer/Webmaster

Gods Of Music

[email protected]

http://www.GodsOfMusic.com

[Shadow]

Exactly how many programming languages should this feature support? Because most of them have different syntax…

[David]

There could be a configuration in an options dialog as to which language you wanted to use (or some other selection method).

Many programming languages are similar, and many just require different quoting and concatenation characters (obviously some are a bit more subtle in their requirements). Perl uses double-quotes and periods for concatenation (changing in Perl 6 but don't worry about that for a while!) and VB uses double-quotes as well, but the ampersand (&) for concatenation. PHP's like Perl. Perl and PHP escape quotes in the string using backslashes, VB uses a doubled double-quote. I'm sure the programming to implement the quoting mechanism would be very simple…in fact the configuration could even just have three fields…quote escape character, quoting character, and concatenation operator, and let people set them to whatever works for their language.

Just my two cents…

[nero]

Some assistance for this is nice but if you are working with php there is a simple solution.

$query=”

SELECT *

FROM table

WHERE x=1

“;

Only the bold blue on top and bottom and you are ready!! 😛

nero :ph34r:

[David]

Perl users could of course use HERE documents:

Code:

print <<__ENDOFSQL__;
SELECT *
FROM testtable;
__ENDOFSQL__

NOTE that this DOES do interpolation for everything other than the double-quote (it acts like a double-quoted string but double-quotes can be used in it without escaping). If you want it to act like a single-quoted string (no variable/other interpolation), do this:

Code:

print <<'__ENDOFSQL__';
SELECT *
FROM testtable;

Note the single-quotes around the EOD (end of document/data) marker, making it act like a single-quoted string, but you don't have to escape single quotes so often used in SQL!

[David]

Oh yeah, I forgot to mention that in Perl you can also use alternate quote characters without HERE documents:

Code:

print qq~
QUERY HERE
~;

If you want to use single-quotes (the above does double-quote style interpolation):

Code:

print q~
QUERY HERE
~;

Note that you can use any character as the quote replacement (I use the tilde, ~ above), but you will have to backslash-escape this character in the string (but you won't have to escape the single or double quotes). So you could do this:

Code:

print qq!
QUERY HERE
!;

Make sure you use the same character at the beginning and end, and the q (single quote style) or qq (double-quote style) must also be present, as above.

[nero]

The Perl way HERE is also in Php

Taken from the manual:

Code:

echo << This uses the “here document” syntax to output
multiple lines with $variable interpolation. Note
that the here document terminator must appear on a
line with just a semicolon no extra whitespace!
END;

will be translated for the query as

Code:

$qry=<< SELECT *
FROM testtable;
END;

While I don't use Perl and have no acknowledge of it I see here that Php uses 3 < instead of 2 that Perl does but the way it works should be the same. For the other Perl methods I don't know there equivalents if they exist in Php. Well this about the advanced non M$ languages. How to do it in VBasic (VBA)?

Code:

Dim qry as string 'otherwise I get error for shure;)
qry=”SELECT *
FROM table
WHERE id=1″

Oh no this doesn't work.

Code:

Dim qry as string 'otherwise I get error for shure;)
qry=”SELECT * _
 FROM table _
 WHERE id=1″

And this should does it. End every line where a follow up exists with a space+underscore.

The only thing I found in the variants of VBA is that sometimes it works and sometimes not.

The reason is't clear for me.

Code:

Dim qry as string 'otherwise I get error for shure;)
qry=””
qry=qry & “SELECT *”
qry=qry & ” FROM table”
qry=qry & ” WHERE id=1″

This way or there equivalent's have one nice feature, you can easy add or comment a part of the query.

And with copy/paste the leading part is not so worse to add. Even with the trailer you can do it.

BTW For Php (and Perl?) you can also do it as

Code:

$qry =””
$qry.=”SELECT *”;
$qry.=” FROM table”;
$qry.=” WHERE id=1″;

Note to the dots at secondline and up

This method I prefer to do for the easy adjusment by commenting a line(s).

Just remember an other method I have seen

Code:

$qry =””
. “SELECT *”
. ” FROM table”
. ” WHERE id=1″;

nero :ph34r:

Summary:

SQLyog is to create the queries and test them.

How they wil be used in the many programming languages and the way they can handle this stuff is, I think, out the scope of SQLyog.

If they can add a way to add a prefix and suffix for each line then the only thing you have to do is adjusting the first and last line to match the language and variant in it.

And, I think/wish, is the only help they can provide to make many people happy with this issue.

I agree with the option of a button to do this fixxing would be nice.

The use of cut/paste by the use of a key shortcut as ctrl-A to select all, already in use by many applications, and ctrl-V in the external application does the job very well, without this fixing.

nero :ph34r:

[Author]

Posts