Hi
I have been using H2 for a couple of years for data analysis tasks where
Excel is not powerful enough. So typically I will read my data into tables
in H2, run SQL scripts on it, and then export the results into a CSV file
to display it in Excel or somewhere else.
When I have a SQL script with strings, e.g. FORMATDATETIME(date, 'yyyy-mm'),
I will need to escape those quotes when encapsulating the statement with
CSVWRITE(filename,
' ... '), i.e. it will become something like
CSVWRITE(filename, '
SELECT FORMATDATETIME(date, ''yyyy-mm'') FROM ...
');
Typically I will develop the SQL statement in the console, and the last
step is to encapsulate it with the CSVWRITE(). I use my editor to replace
all occurrences of single quotes (') with double quotes ('').
However, when I want to go back and change the script and test it again in
the console, I have to go back and forth between single and double quotes
and I cannot paste the script directly into the console for testing.
My suggestion therefore is to introduce something like a here document (
https://en.wikipedia.org/wiki/Here_document#Unix_shells) as known from bash
or other shells. The syntax for the above example could look like this:
CSVWRITE(filename, << END_SCRIPT
SELECT FORMATDATETIME(date, 'yyyy-mm') FROM ...
END_SCRIPT
);
This would allow the user to develop and test a script, and then the only
step to create the CSV export would be to encapsulate it with the CSVWRITE.
No need to change from single to double quotes and back.
Of course, I am interested to hear how other people solved this. Maybe
there is a much simpler way that I did not think about.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
