On 8/16/2016 10:04 AM, ksiimson wrote:
Hi!
I'm a new user of Racket and I am trying to make a simple application which
interfaces a MySQL database.
Fetching the data is quite straightforward, but I am having trouble coming up
with a reasonable way to insert the data back into database. Basically the
fetched vector contains many different data types, but I haven't found an easy
way to serialize them back to a string for SQL queries.
SELECT now();
Would result in a vector like this
(vector (sql-timestamp 2016 8 16 17 1 39 0 #f))
What would be a reasonable way to serialize this to "2016-08-16 17:01:39", for
example:
INSERT INTO x VALUES ("2016-08-16 17:01:39")
This question is not particular to serializing a sql-timestamp, but any data
type.
You don't need to convert the values to text. You want to use query
parameters instead.
MySQL queries use "?" for parameter placeholders, so you want to do
something like:
(query-exec dbc "INSERT INTO x ( a_datetime, an_integer, a_float )
VALUES (?,?,?)"
"2016-08-16 17:01:39"
42
3.14159 )
The arguments to *query-exec* (or *query*, see below) need to match the
order and expected types of the parameter placeholders in the query
string, but you don't need to convert arguments to text or splice them
into the string. In general you don't want to do that anyway because
that is how SQL injection flaws can creep into your code. There are
occasions when deficiencies in the SQL syntax actually will force you to
splice things directly into the query string, but you should avoid doing
so as much as possible. Use query parameters unless you really have no
choice.
If the argument type isn't correct you need to cast or convert it to the
right type in the query string: e.g., to pass a datetime to a query
that expects just a date:
(define the-date (sql-timestamp 2016 8 16 17 1 39 0 #f))
(query-exec dbc "INSERT INTO x ( a_datetime ) VALUES ( cast( ? as date ) )"
the-date )
Note: *query-exec* doesn't return status. If you need to know how many
rows were affected, use *query *instead. For more information see
http://docs.racket-lang.org/db/query-api.html?q=query
http://docs.racket-lang.org/db/sql-types.html?q=mysql#%28part._mysql-types%29
Hope this helps,
George
--
You received this message because you are subscribed to the Google Groups "Racket
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
