How to format SQL strings?



  • Hello,

    Does any one knows a tool, utility or Qt Creator plugin for formatting C++ friendly strings?

    This issue nagging me very much because whenever I copy SQL string I've to format the content manually before using it inside my C++\Qt source code... Example:

    I've to format:
    [code]"SELECT public."Accounts".name, public."Analyze".name, year_2014."Results".value, year_2014."Results"."date", year_2014."Results".requestnumber FROM year_2014."Results" INNER JOIN public."Accounts" ON (year_2014."Results".account_id = public."Accounts".id) INNER JOIN public."Analyze" ON (year_2014."Results".analyze_id = public."Analyze".id) WHERE public."Analyze".isparent = 'FALSE' AND public."Accounts".id = %1 OR public."Accounts".name LIKE '%%2%' UNION SELECT public."Accounts".name, public."SubAnalyze".name, year_2014."Results".value, year_2014."Results"."date", year_2014."Results".requestnumber FROM year_2014."Results" INNER JOIN public."Accounts" ON (year_2014."Results".account_id = public."Accounts".id) INNER JOIN public."SubAnalyze" ON (year_2014."Results".subanalyze_id = public."SubAnalyze".id) WHERE public."Accounts".id = %1 OR public."Accounts".name LIKE '%%2%'";[/code]

    To be like this:
    [code]"SELECT public."Accounts".name, public."Analyze".name, year_2014."Results".value, year_2014."Results"."date", year_2014."Results".requestnumber FROM year_2014."Results" INNER JOIN public."Accounts" ON (year_2014."Results".account_id = public."Accounts".id) INNER JOIN public."Analyze" ON (year_2014."Results".analyze_id = public."Analyze".id) WHERE public."Analyze".isparent = 'FALSE' AND public."Accounts".id = %1 OR public."Accounts".name LIKE '%%2%' UNION SELECT public."Accounts".name, public."SubAnalyze".name, year_2014."Results".value, year_2014."Results"."date", year_2014."Results".requestnumber FROM year_2014."Results" INNER JOIN public."Accounts" ON (year_2014."Results".account_id = public."Accounts".id) INNER JOIN public."SubAnalyze" ON (year_2014."Results".subanalyze_id = public."SubAnalyze".id) WHERE public."Accounts".id = %1 OR public."Accounts".name LIKE '%%2%'";[/code]


  • Moderators

    Hi,

    You could do a find+replace before pasting the string into your .cpp file. (e.g. in Microsoft Notepad, the shortcut is Ctrl+H)



  • Of course I already know that;

    My question is very specified. I need a tool for reformat this kind of strings.



  • There is no such specific tool but you have two options:

    1. QtCreator supports some subset of Vim and its regular expressions.
      You can switch to Vim mode by Alt+V,Alt+V
      Put cursor on the string and enter to command mode by :

    For this particular string you can use the following RegEx and press enter.
    @
    s/."([^"]*)"/.\"\1\"/g
    @

    1. QtCreator "Search & Replace" also supports "RegEx":http://qt-project.org/doc/qtcreator-3.1/creator-editor-finding.html
      It is based on "QRegExp":http://qt-project.org/doc/qt-5/qregexp.html#details

    An expression to search
    @
    ."([^"]*)"
    @

    An expression to replace
    @
    ."\1"
    @

    [EDIT]; fixed a replace expression.



  • [quote author="andreyc" date="1405395356"]There is no such specific tool
    [/quote]

    No there is a such tool; I used it before but it's a commercial one (Navicat: it has "Copy to C++" action which format the string for me); Beside that I remember that some contributor started a Qt Creator plugin adds "Special Paste" action for formatting such kind of strings but I missed his post (I read it in Qt's bug tracker).

    I'm sure there is such kind of tools (commercial and open source too) but I forgot it the correct term for find them in google.



  • [quote]No there is a such tool; I used it before [/quote]
    Right. I guess I wanted to say that I don't know such tool.


  • Moderators

    I don't know of any such tool either, sorry.

    Good luck with your search.



  • I might be wrong with this, I wouldn't consider myself an SQL expert, but this seems to works for me using SQLite within Qt.

    In my SQL I use the single quote ' rather than the double quote " and it doesn't need escaping, so I avoid the problem.

    I would also have thought that table and field names only need quotation marks if they have spaces in them. I use underscores in field names to avoid that and only use the single quote when I am including strings for things like date formats within the strftime function.

    I guess things could be different with different SQL libraries within Qt?


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.