Hello,
i'm looking for a database layer, which should support at least MySQL
and PostGres. Oracle is optional.
First i tried clsql but it seems, that it do not support prepared
statements. But i really need them.
Another import point is, that i want to pass the query as a (prepared)
string to the database and not in a lisp-like form. This is needful,
because i often generate queries and i like it to separate the
persistent-layer/scheme (aka database) from the rest of my code.
Thank you for any tip!
Greetings,
Torsten
On 24 Sie, 12:09, Torsten Z�hlsdorff <······@meisterderspiele.de>
wrote:
> Hello,
>
> i'm looking for a database layer, which should support at least MySQL
> and PostGres. Oracle is optional.
>
Postmodern has support for prepared statements.
http://common-lisp.net/project/postmodern/cl-postgres.html#prepare-query
--
Cheers,
Rafal Strzalinski
http://nablaone.net
Rafal Strzalinski schrieb:
> On 24 Sie, 12:09, Torsten Z�hlsdorff <······@meisterderspiele.de>
> wrote:
>> Hello,
>>
>> i'm looking for a database layer, which should support at least MySQL
>> and PostGres. Oracle is optional.
>>
>
> Postmodern has support for prepared statements.
>
> http://common-lisp.net/project/postmodern/cl-postgres.html#prepare-query
But Postmodern do not support MySQL?
Greetings,
Torsten
On 27 Sie, 11:39, Torsten Z�hlsdorff <······@meisterderspiele.de>
wrote:
> Rafal Strzalinski schrieb:
>
> > On 24 Sie, 12:09, Torsten Z�hlsdorff <······@meisterderspiele.de>
> > wrote:
> >> Hello,
>
> >> i'm looking for a database layer, which should support at least MySQL
> >> and PostGres. Oracle is optional.
> But Postmodern do not support MySQL?
>
Oops. You're right.
Richard M Kreuter schrieb:
>> i'm looking for a database layer, which should support at least MySQL
>> and PostGres. Oracle is optional.
>>
>> First i tried clsql but it seems, that it do not support prepared
>> statements. But i really need them.
>
> I don't know about MySQL, but PostgreSQL has a PREPARE statement in
> the DML, and so you can do your own preparing with EXECUTE-COMMAND.
> However, clsql does support prepared statements (though perhaps not in
> all backends and perhaps not with the same placeholder syntax
> everywhere; I don't know):
MySQL supports prepared statements (in a rudimentary form) since version
5. But clsql don't support prepared statements for MySQL. :(
> Hope that helps,
Yes, it does! The function "prepare-sql" was not mentioned in the manual
- so i believed that there is no support of prepared statements.
Greetings,
Torsten
From: Richard M Kreuter
Subject: Re: Database-layer which supports prepared statements?
Date:
Message-ID: <87d4x7pdi4.fsf@tan-ru.localdomain>
Torsten Zühlsdorff <······@meisterderspiele.de> writes:
> Richard M Kreuter schrieb:
>
>>> i'm looking for a database layer, which should support at least MySQL
>>> and PostGres. Oracle is optional.
>>>
>>> First i tried clsql but it seems, that it do not support prepared
>>> statements. But i really need them.
>>
>> I don't know about MySQL, but PostgreSQL has a PREPARE statement in
>> the DML, and so you can do your own preparing with EXECUTE-COMMAND.
>> However, clsql does support prepared statements (though perhaps not in
>> all backends and perhaps not with the same placeholder syntax
>> everywhere; I don't know):
>
> MySQL supports prepared statements (in a rudimentary form) since
> version 5. But clsql don't support prepared statements for MySQL. :(
What I meant is that if the RDBMS supports a PREPARE statement in the
data manipulation language (the query language), then you can roll
your own prepared statements pretty easily.
I used something like the below before I noticed that CLSQL already
did it for Postgres. Presumably you can do something similar for
MySQL. Note that the below exposes the RDBMS-specific prepared
statement parameter syntax (for Postgres, "$1", "$2", etc.).
Hope that helps,
RmK
--
(in-package clsql-user)
(defparameter *prepared-statement-counter* 0)
(defvar *prepared-statement-hash* (make-hash-table))
;; Warning: this is for demonstration purposes only, and is known not
;; to work right. CLSQL probably already has one of these somewhere.
(defun quote-for-sql (thing)
(etypecase thing
(integer (format nil "~D" thing))
(string (format nil "'~A'" thing))))
(defun prepare (statement argtypes &key (db *default-database*))
(let* ((plan-name (format nil " __prepared_statement_~D"
(prog1 *prepared-statement-counter*
(incf *prepared-statement-counter*))))
(prepare-query
(format
nil
"PREPARE ~A ···@[(·@{~A~})~]~} AS ~A"
plan-name argtypes statement)))
(incf *prepared-statement-counter*)
(execute-command prepare-query :database db)
(let ((fun (lambda (&rest args)
(query (format nil "EXECUTE ~A(~{~A~^,~})"
plan-name (mapcar 'quote-for-sql args))
:database db))))
(setf (gethash fun *prepared-statement-hash*) (cons plan-name db))
fun)))
(defun release-prepared-statement (fun &optional)
(multiple-value-bind (pair foundp)
(gethash fun *prepared-statement-hash*)
(unless foundp
(error "Can't release ~S." fun))
(destructuring-bind (name . db) pair
(execute-command (format nil "DEALLOCATE ~A" name)
:database db)
(remhash fun *prepared-statement-hash*)))
--
Here's a demo:
CLSQL-USER> (connect (list "host" "db" "name" "pass")
:database-type :postgresql-socket)
#<CLSQL-POSTGRESQL-SOCKET:POSTGRESQL-SOCKET-DATABASE
host/db/name OPEN {519E7A61}>
CLSQL-USER> (execute-command
"CREATE TEMPORARY TABLE temp (i INTEGER, s VARCHAR)")
; No value
CLSQL-USER> (loop
for i upfrom 0 below 10
do (execute-command
(format nil "INSERT INTO temp VALUES (~D, '~R')"
i i)))
NIL
CLSQL-USER> (query "SELECT * FROM temp")
((0 "zero") (1 "one") (2 "two") (3 "three") (4 "four") (5 "five")
(6 "six") (7 "seven") (8 "eight") (9 "nine"))
("i" "s")
CLSQL-USER> (prepare "SELECT * FROM TEMP WHERE i = $1" '("INTEGER"))
#<CLOSURE (LAMBDA (&REST ARGS)) {5144E69D}>
CLSQL-USER> (defparameter *prepared-fun* *)
*PREPARED-FUN*
CLSQL-USER> (funcall *prepared-fun* 4)
((4 "four"))
("i" "s")
CLSQL-USER> (funcall *prepared-fun* 7)
((7 "seven"))
("i" "s")
CLSQL-USER> (release-prepared-statement *prepared-fun*)
; No value
CLSQL-USER> (funcall *prepared-fun* 4)
While accessing database #<POSTGRESQL-SOCKET-DATABASE
host/db/name OPEN {51BA02B1}>
with expression "EXECUTE __prepared_statement_4(4)":
Error POSTGRESQL-ERROR / ERROR: prepared statement "__prepared_statement_4" does not exist
has occurred.
[Condition of type SQL-DATABASE-DATA-ERROR]
; Evaluation aborted.