fukamachi / cl-dbi Goto Github PK
View Code? Open in Web Editor NEWDatabase independent interface for Common Lisp
cl-dbi's People
Stargazers
Watchers
Forkers
snmsts subtlepath lambdasakura kuny spacebat rudolph-miller ghard isutton tatrix fchurca salewski dchanko tamurashingo svetlyak40wt cage2 davidalphafox masatoi gptix kat-co vindarel poga jnc-nj desiderius yaktender mrlightningbolt altmoketcl-dbi's Issues
one-time query
DB-FILLING> (dbi:with-connection (db-connection :sqlite3
:database-name *db-path*)
(dbi:do-sql db-connection
"CREATE TABLE colors (id integer primary key autoincrement, label text)")
(let ((query (dbi:prepare db-connection
"INSERT INTO colors(label) VALUES (?)")))
(map nil
(lambda (color)
(dbi:execute query
color))
(list "Red" "Blue" "Green")))
(dbi:fetch-all
(dbi:execute
(dbi:prepare db-connection "SELECT * FROM colors;"))))
0: (DBI.DRIVER:EXECUTE #<DBD.SQLITE3::<DBD-SQLITE3-QUERY> {B6A4C39}> "Red")
0: DBI.DRIVER:EXECUTE returned #<DBD.SQLITE3::<DBD-SQLITE3-QUERY> {B6A4C39}>
0: (DBI.DRIVER:EXECUTE #<DBD.SQLITE3::<DBD-SQLITE3-QUERY> {B6A4C39}> "Blue")
0: DBI.DRIVER:EXECUTE returned #<DBD.SQLITE3::<DBD-SQLITE3-QUERY> {B6A4C39}>
0: (DBI.DRIVER:EXECUTE #<DBD.SQLITE3::<DBD-SQLITE3-QUERY> {B6A4C39}> "Green")
0: DBI.DRIVER:EXECUTE returned #<DBD.SQLITE3::<DBD-SQLITE3-QUERY> {B6A4C39}>
0: (DBI.DRIVER:EXECUTE #<DBD.SQLITE3::<DBD-SQLITE3-QUERY> {B6B2E11}>)
0: DBI.DRIVER:EXECUTE returned #<DBD.SQLITE3::<DBD-SQLITE3-QUERY> {B6B2E11}>
((:|id| 1 :|label| "Red"))
typo in Usage: Connecting and executing a query
In the first code snippet on the main README file, I think there is a typo:
(loop for row = (dbi:fetch result) while row ;; process "row". )
the row assignment should be (dbi:fetch query)
Sqlite3 nested transactions
BEGIN TRANSACTION / END TRANSACTION cannot be used in a nested fashion.
SAVEPOINT dummy_name/ RELEASE dummy_name should work nested.
http://www.sqlite.org/lang_savepoint.html
unexpected behaviour in insert-into
#<SXQL-STATEMENT: INSERT INTO `assets` (`content`, `history`, `description`, `tags`, `callsign`, `asset_id`, `create_time`, `update_time`) VALUES (1, '', '', '', 'test', 'khzjAW+3tId6g6lm8AK0Qw==', '2021-08-13T14:02:21.550229+08:00', '2021-08-13T14:02:21.550229+08:00') ON DUPLICATE KEY UPDATE `id` = `id`>
the above, when processed with dbi:prepare and dbi:execute does not insert into the database as expected
i'm getting munged row of
id, asset_id, callsign, history, content, tags, description, create_time, update_time
472, , , 2021-08-13T14:02:21.550229+08:00, 2021-08-13T14:02:21.550229+08:00, test, khzjAW+3tId6g6lm8AK0Qw==, , 1
i'll see if there's a fix in the afternoon, seems to be an issue with how the sql statement is being sent to the client
loading simple-date changes result
loading postmodern's simple-date causes query results to change type.
Is this behavior intended? I find no explicit reference to simple-date in the source using grep.
CL-USER> (ql:quickload :cl-dbi)
To load "cl-dbi":
Load 1 ASDF system:
cl-dbi
; Loading "cl-dbi"
(:CL-DBI)
CL-USER> (dbi:with-connection (conn :postgres
:database-name "dbtullius"
:username "utullius"
:password "**")
(let* ((query (dbi:prepare conn
"SELECT current_date, interval '1 month'"))
(result (dbi:execute query)))
(dbi:fetch result)))
To load "dbd-postgres":
Load 1 ASDF system:
dbd-postgres
; Loading "dbd-postgres"
(:|date| 3650832000 :|interval|
((:MONTHS 1) (:DAYS 0) (:SECONDS 0) (:USECONDS 0)))
CL-USER> (ql:quickload :simple-date)
To load "simple-date":
Load 1 ASDF system:
simple-date
; Loading "simple-date"
(:SIMPLE-DATE)
CL-USER> (dbi:with-connection (conn :postgres
:database-name "dbtullius"
:username "utullius"
:password "**")
(let* ((query (dbi:prepare conn
"SELECT current_date, interval '1 month'"))
(result (dbi:execute query)))
(dbi:fetch result)))
(:|date| #<SIMPLE-DATE:DATE 10-09-2015> :|interval| #<SIMPLE-DATE:INTERVAL P1M>)
CL-USER>
dbi:connect somehow blocks Keyboard Interrupt
in sbcl 1.4.10, running:
(defvar *conn* (dbi:connect :mysql :database-name "db" :username "root"))somehow disables the keyboard/interactive interrupt (<C-c>). It remains disabled when the connection
is closed.
Make ":binds" of "retrieve-by-sql" into an optional param
Hi!
Looking at (defgeneric retrieve-by-sql (sql &key binds) it seems that :binds can be &optional instead of a key param:
(mito:retrieve-by-sql "select * from users where email = $1" :binds (list *default-email*))
;; vs
(mito:retrieve-by-sql "select * from users where email = $1" (list *default-email*))Is there a specific reason for using :binds here instead of just an &optional (or &rest)?
Thanks!
Begin/ End transaction - fails on the commit
I believe I've found a base issues on the code for transactions. I"m working on MySQL 8 and the following sequence:
(begin-transaction conn)
(query conn "insert...")
(commit conn)
Ends up with MySQL holding an open transaction on the connection thread.
I believe the bug is in driver.lisp in the begin-transaction code. The code for commit checks the variable transaction-state using the method get-transaction-state . This is returning _n_il so the when clause simply falls through and the call-next-method is never called (which would actually issue the commit).
The reason appears to begin-transaction never adds a cons to this variable which does happen in %with-transaction. This could be set with with-savepoint. Is that the required process? If so the documentation should be updated. Otherwise this is a bug.
Rollback has the same issue as Commit.
Functions connect-cached and get-connection-pool are not thread-safe
Because of that, Ultralisp sometimes fails with this error:
failed AVER:
(= (HASH-TABLE-COUNT SB-IMPL::TABLE) SB-IMPL::HWM)
This is probably a bug in SBCL itself. (Alternatively, SBCL
might have been corrupted by bad user code, e.g. by an undefined
Lisp operation like (FMAKUNBOUND 'COMPILE), or by stray pointers
from alien code or from unsafe Lisp code; or there might be a
bug in the OS or hardware that SBCL is running on.) If it seems
to be a bug in SBCL itself, the maintainers would like to know
about it. Bug reports are welcome on the SBCL mailing lists,
which you can find at <http://sbcl.sourceforge.net/>.
[Condition of type SB-INT:BUG]
Restarts:
0: [ABORT] abort thread (#<THREAD "searching-for-repositories" RUNNING {102F529873}>)
Backtrace:
0: (SB-IMPL::GROW-HASH-TABLE #<HASH-TABLE :TEST EQ :COUNT 35 {10020957B3}>)
1: ((FLET SB-IMPL::INSERT-AT :IN SB-IMPL::GET-PUTHASH-DEFINITIONS) #<unavailable argument> #<HASH-TABLE :TEST EQ :COUNT 35 {10020957B3}> #<SB-THREAD:THREAD "searching-for-repositories" RUNNING {102F52987..
2: (SB-IMPL::PUTHASH/EQ #<SB-THREAD:THREAD "searching-for-repositories" RUNNING {102F529873}> #<unavailable argument> #<unavailable argument>)
3: (SB-KERNEL:%PUTHASH #<SB-THREAD:THREAD "searching-for-repositories" RUNNING {102F529873}> #<HASH-TABLE :TEST EQ :COUNT 35 {10020957B3}> #<HASH-TABLE :TEST EQUAL :COUNT 0 {102F5305A3}>)
4: (DBI:CONNECT-CACHED :POSTGRES :HOST "172.17.0.1" :DATABASE-NAME "ultralisp" :USERNAME "ultralisp" :PASSWORD "****")
5: (ULTRALISP/DB::CONNECT :HOST NIL :DATABASE-NAME NIL :USERNAME NIL :PASSWORD NIL :CACHED T)
6: ((LAMBDA NIL :IN ULTRALISP/GITHUB/WIDGETS/REPOSITORIES::SET-OAUTH-TOKEN))
7: ((LABELS BORDEAUX-THREADS::%BINDING-DEFAULT-SPECIALS-WRAPPER :IN BORDEAUX-THREADS::BINDING-DEFAULT-SPECIALS))
This code short be protected with lock.
@export annotations cause sbcl package variance warnings
If I attempt to reload cl-dbi, I get warnings of the following form:
; file: /home/sly/quicklisp/dists/quicklisp/software/cl-dbi-20191007-git/src/dbd/sqlite3.lisp
; in: DEFPACKAGE DBD.SQLITE3
; (DEFPACKAGE DBD.SQLITE3
; (:USE :CL :DBI.DRIVER :DBI.LOGGER :DBI.ERROR :SQLITE :ANNOT.CLASS)
; (:SHADOWING-IMPORT-FROM :DBI.DRIVER :DISCONNECT)
; (:IMPORT-FROM :TRIVIAL-GARBAGE :FINALIZE)
; (:IMPORT-FROM :UIOP/FILESYSTEM :FILE-EXISTS-P))
; --> EVAL-WHEN
; ==>
; (SB-IMPL::%DEFPACKAGE "DBD.SQLITE3" 'NIL 'NIL 'NIL
; '(("DBI.DRIVER" "DISCONNECT"))
; '("CL" "DBI.DRIVER" "DBI.LOGGER" "DBI.ERROR" "SQLITE"
; "ANNOT.CLASS")
; '(("UIOP/FILESYSTEM" "FILE-EXISTS-P")
; ("TRIVIAL-GARBAGE" "FINALIZE"))
; 'NIL 'NIL '("DBD.SQLITE3") 'NIL ...)
;
; caught WARNING:
; DBD.SQLITE3 also exports the following symbols:
; (DBD.SQLITE3:SQLITE3-USE-STORE DBD.SQLITE3:<DBD-SQLITE3-QUERY>
; DBD.SQLITE3:<DBD-SQLITE3-CONNECTION>
; DBD.SQLITE3:<DBD-SQLITE3>)
; See also:
; The ANSI Standard, Macro DEFPACKAGE
; The SBCL Manual, Variable *ON-PACKAGE-VARIANCE*Preparing statement
Hello,
I have the following issue :
(dbi:with-connection (conn :sqlite3 :database-name #P"/tmp/enemy.db")
(dbi:execute (dbi:prepare conn "CREATE TABLE enemy (
name STRING PRIMARY KEY,
age INTEGER NOT NULL,
address TEXT,
fatal_weakness TEXT NOT NULL DEFAULT ?,
identifying_color CHAR(20) UNIQUE);")
(list "None")))
DB Error: near "?": syntax error (Code: ERROR)
[Condition of type DBI.ERROR:DBI-PROGRAMMING-ERROR]
cl-dbi from quicklisp cl-dbi-20210228, latest quicklisp client and latest updates
sbcl 2.1.1
Linux Ubuntu 20.04
My initial request was :
(multiple-value-bind (query-string query-args)
(sxql:yield (sxql:create-table :enemy
((name :type 'string
:primary-key t)
(age :type 'integer
:not-null t)
(address :type 'text
:not-null nil)
(fatal_weakness :type 'text
:not-null t
:default "None")
(identifying_color :type '(:char 20)
:unique t))))
(dbi:with-connection (conn :sqlite3 :database-name #P"/tmp/enemy.db")
(dbi:execute (dbi:prepare conn query-string)
query-args)))
Sql-injection ?
(if param
-
(concatenate 'string part "'" param "'") -
part)
Where escaping? May be Sql-injection ?
Error in installing by Quicklisp with SBCL
when i tried to use it by sbcl, clone it on quicklisp/local-projects, register-local-projects and quickload it, then i got the error.
To load "dbi":
Load 1 ASDF system:
dbi
; Loading "dbi"
[package dbi.error]Unhandled EDITOR-HINTS.NAMED-READTABLES:READER-MACRO-CONFLICT in thread #<SB-THREAD:THREAD
"main thread" RUNNING
{1002BDD2A3}>:
Reader macro conflict while trying to merge the macro character #\# from
#<NAMED-READTABLE CL-ANNOT::SYNTAX {1005BDFDC3}> into
#<NAMED-READTABLE :CURRENT {1005D1C163}>.
though it caused with SBCL, it didnt cause with CLISP
regressions due to osicat
Since quicklisp 2013-11-11 dbd-sqlite3 system can not be loaded on systems, where it was loading OK before. Namely Windows systems, or ECL bytecode compiler.
This is because of the new dependency osicat.
As far as I see, osicat is only used in a single form: (osicat:regular-file-exists-p database-path)
IMHO it is too restrictive to reject some lisp platforms by using osicat for such a simple need.
Why not just use (cl:probe-file database-path).
Postgres and timestamp values
Inserting timestamp data into Postgres is not a problem since it reads ISO 8601, but reading it is, since local-time can't parse strings like "2016-01-16 01:28:08.926922+00".
Now, cl-postgres has a function to define functions to read the timestamps from simpler values.
I think an option in the driver object to set the timestamp reader function would be useful. Changing it by default would break backwards compatibility, but setting it optionally would work.
Inserting a nil into a text column results in 'false' (PostgreSQL)
E.g.,
(dbi:do-sql dbh "CREATE TABLE example (name text)")
(dbi:execute (dbi:prepare dbh "INSERT INTO example (name) VALUES (?)") nil)
yields a text string 'false' in the name column. So, is it intended behaviour?
Freeing query resources
I was working on an ODBC driver for cl-dbi the other day, and I've run into a snag: the driver has to allocate some (foreign) resources to execute a query, which need to be freed after results have been fetched, but there's no obvious place to do this. It seems like there are three cases:
- Callers use
EXECUTEandFETCHdirectly. - Callers use
DO-SQLorFETCH-ALL, but signal a condition before all results are fetched. - Callers use
DO-SQLorFETCH-ALL, and fetch all results normally.
3 is easy to do, but 1 & 2 are harder. The thing that comes to mind is that cl-dbi needs a FREE-QUERY counterpart to EXECUTE (and probably some kind of WITH-QUERY macro), but that would mean an API change. Thoughts?
Insert with returning doesn't work
I'm using Postgresql and trying to return row from insert-into command and keep getting ; No value:
(execute
(insert-into :test
(set= :text "asdf")
(returning :*)))
SXQL seems to produce correct statement: #<SXQL-STATEMENT: INSERT INTO test (text) VALUES ('asdf') RETURNING *>. Is there a problem with execute or am I missing something?
Thank you!
with-connection
One thing that annoys me when writing db code is having to remember to close connections after I'm done. This has become annoying enough that I wrote a small macro, with-connection, that ensures dbi:disconnect will be called. Here I humbly submit it to you, in the hope that maybe you'll find it useful enough to include in cl-dbi proper.
Definition
(defmacro with-connection ((conn-sym &rest rest) &body body)
`(let ((,conn-sym (dbi:connect ,@rest)))
(unwind-protect
(progn ,@body)
(dbi:disconnect ,conn-sym))))Sample usage
(with-connection (conn :sqlite3 :database-name db-path)
(let* ((query (dbi:prepare conn "SELECT * FROM People"))
(result (dbi:execute query)))
(loop for row = (dbi:fetch result)
while row
do (format t "~a~%" row))))))Preparing statement with lists
I have the following issue:
(dbi:with-connection (con :postgres
:username "test"
:database-name "test")
(dbi:execute
(dbi:prepare con
"SELECT a,
b
FROM T
WHERE c = ? AND
a IN ? AND")
2
(list "1" "2")))
results in
DB Error: syntax error at or near "$2" (Code: 42601)
But
(dbi:with-connection (con :postgres
:username "rc-study-manager"
:database-name "rc-study")
(dbi:execute
(dbi:prepare con
"SELECT a,
b
FROM T
WHERE c = ? AND
a IN ('1', '2')")
2))
does not.
How can I prepare a statement when I have list(s) as parameters?
cl-dbi:commit should make a non-local exit
This behavior is implicit, unexpected and leads to many WTFs.
This is a short illustration:
CL-USER> (dbi:with-transaction mito.connection:*connection*
(format t "BEFORE COMMIT~%")
(cl-dbi:commit mito:*connection*)
(format t "AFTER COMMIT~%"))
BEFORE COMMIT
TCode after cl-dbi:commit have no change to be executed! This is because cl-dbi:commit signals dbi.driver::transaction-done-condition and it is catched in dbi:with-transaction by handler-case:
(let ((#:conn-var1322 mito.connection:*connection*))
(if (find #:conn-var1322 dbi.driver:*in-transaction* :test #'eq)
(dbi:with-savepoint #:conn-var1322
(format t "BEFORE COMMIT~%")
(dbi.driver:commit mito.connection:*connection*)
(format t "AFTER COMMIT~%"))
(let* (#:transaction-done1323
#:transaction-ok1324
(#:conn-var1325 #:conn-var1322)
(dbi.driver:*in-transaction*
(cons #:conn-var1325 dbi.driver:*in-transaction*)))
(dbi.driver:begin-transaction #:conn-var1325)
(unwind-protect
(handler-case
(multiple-value-prog1
(progn
(format t "BEFORE COMMIT~%")
(dbi.driver:commit mito.connection:*connection*)
(format t "AFTER COMMIT~%"))
(setf #:transaction-ok1324 t))
(dbi.driver::transaction-done-condition nil
(setf #:transaction-done1323 t)))
(unless #:transaction-done1323
(handler-case
(if #:transaction-ok1324
(dbi.driver:commit #:conn-var1325)
(dbi.driver:rollback #:conn-var1325))
(dbi.driver::transaction-done-condition nil)))))))Probably, handler-bind could be used here to keep track if transaction was commited.
- memory leak regression
Connect cached does not reconnect with sqlite
When using connect-cached, after a disconnect the next connect-cached returns the previous connection but because of the disconnect the handle slot is unbound.
CL-USER> (let ((conn (dbi:connect-cached :sqlite3 :database-name "/tmp/test.db")))
(let* ((query (dbi:prepare conn "PRAGMA stats"))
(result (dbi:execute query)))
(loop for row = (dbi:fetch result)
while row
do (format t "~A~%" row)))
(dbi:disconnect conn))
"(table sqlite_master index NIL width 65 height 200)"
=> #<SQLITE:SQLITE-HANDLE {1006E2ED43}>
CL-USER> (let ((conn (dbi:connect-cached :sqlite3 :database-name "/tmp/test.db")))
(let* ((query (dbi:prepare conn "PRAGMA stats"))
(result (dbi:execute query)))
(loop for row = (dbi:fetch result)
while row
do (format t "~A~%" row)))
(dbi:disconnect conn))
=> The slot SQLITE::HANDLE is unbound in the objectThe connections remain cached and is not detected by connect-cached because ping only checks if the sqlite db file exists not if the connection is valid (ie: handle is bound).
Relevant code:
Please Consider Converting `prepare-sql` from Variadic to take in a list
(defgeneric prepare-sql (conn sql)
(:documentation
"Create a function that takes parameters, binds them into a query and returns SQL as a string.")
(:method ((conn dbi-connection) (sql string))
(labels ((param-to-sql (param)
(typecase param
(string (concatenate 'string "'" (escape-sql conn param) "'"))
(null "NULL")
(t (princ-to-string param)))))
(let ((sql-parts (split-sequence #\? sql)))
(lambda (&rest params)
(if params
(with-output-to-string (out)
(loop for (part . rest) on sql-parts
do
(let ((param (pop params)))
(write-sequence
(if rest
(concatenate 'string part (param-to-sql param))
part)
out))))
sql))))))https://github.com/fukamachi/cl-dbi/blob/master/src/driver.lisp#L415
I am running into a confirmed bug on SBCL wherein mid-iteration in the loop, the params list suddenly gets set to 0. The advice I've received from an SBCL developer is that passing large numbers of arguments within a &rest variable is not very portable Common Lisp because of call-arguments-limit.
For performance reasons, users (such as myself) may want many bind parameters to take advantage of so-called extended inserts, e.g.:
INSERT INTO tbl_name (a,b,c) VALUES(?,?,?),(?,?,?),(?,?,?),(?,?,?),...When this is the case, cl-dbi is artificially coupling how many &rest parameters can be passed with how many extended inserts a DB can support thus potentially artificially limiting an application's performance. Further, it becomes more difficult to write portable code because of this implementation detail.
Please consider changing this lambda so that it takes in a list.
Thank you for the library!
postgres.lisp import nit picking
the defpackage form for dbd.postgres looks as follows:
(defpackage dbd.postgres
(:use :cl
:dbi.driver
:dbi.error
:cl-postgres)
(:import-from :cl-postgres
:connection-socket
:send-parse)
(:import-from :cl-postgres-error
...
It would be nice if we either imported all of cl-postgres or listed the symbols we want in the :import-from clause, but doing both seems bogus.
MySQL unable to load Foreign library
Hello,
I am currently trying to use your library which seems to be very useful for my project.
I am still a newbie in Lisp and when I try to install your library and try a connection I get this error:
Error: Unable to load foreign library (LIBMYSQLCLIENT). Could not register handle for external module LIBMYSQLCLIENT (file-name libmysql.dll): Le module spécifié est introuvable.
(MySQL-server is install)
Do you have any idea where this is coming from?
I am using Win 10 64bits on LispWork Pro
Thx for the future feedback
f2cfb60: 'Converts Calls With Parameters to `&optional` instead of `&rest`' broke my code
It's nice that there's a higher performance version of execute now, but this could have been done by adding a new function and preserving the old behavior. Now I have to update my code and make sure that my systems are using the latest and greatest. At a minimum this sort of API-breaking change should have been 1) more loudly announced and 2) accompanied with a version number increment.
Don't know how widely supported this is "in the wild" yet, but my preferred path would be to back this out and use new function names.
SQLite3 driver install
Hi Eitaro,
First of all, thank you very much for the amazing number of systems you are developing for Common Lisp, like clack, lack, ningle and Caveman2.
I am having the following problem when installing the DBD driver for SQLite:
(quicklisp:quickload "dbd-sqlite3")
To load "dbd-sqlite3":
Load 1 ASDF system:
dbd-sqlite3
; Loading "dbd-sqlite3"
I get the following error:
Unable to load any of the alternatives:
((:DEFAULT "libsqlite3") (:DEFAULT "sqlite3"))
[Condition of type CFFI:LOAD-FOREIGN-LIBRARY-ERROR]
EDIT: I see that CFFI is trying to find the external library for SQLITE3.
Do you have a suggestion on which SQLITE3 library to use with CFFI? I was caught off-guard, i thought that the CL-DBI SQLITE3 "driver" already included the "driver" ;-P
Losing connection when error in SQL (MySQL adapter)
Hello,
First of all thank you for this nice library.
When I execute an SQL statement that yields to an error, the connection is no longer usable.
The connection must be created again.
Best Regards,
Camille
Strange values for date type in postgres.
CL-USER> (dbi:with-connection (conn :postgres
:database-name "dbtullius"
:username "utullius"
:password "********")
(let* ((query (dbi:prepare conn
"SELECT current_date"))
(result (dbi:execute query)))
(dbi:fetch result)))
(:|date| 3650745600)
CL-USER> (local-time:unix-to-timestamp (second *))
@2085-09-07T21:00:00.000000-03:00
CL-USER> (local-time:today)
@2015-09-08T20:00:00.000000-04:00
CL-USER> (local-time:timestamp-to-unix *)
1441756800
in psql:
dbtullius=> select current_date;
date
------------
2015-09-09
(1 row)
I see the same thing when using real columns of the date type in real tables.
Unable to load libmysqlclient.dylib
OSX 10.11.6,
Mysql 5.7.11
I got this error message:
debugger invoked on a LOAD-FOREIGN-LIBRARY-ERROR in thread
#<THREAD "main thread" RUNNING {10029565B3}>:
Unable to load any of the alternatives:
("libmysqlclient.20.dylib" "libmysqlclient.dylib")
Type HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.
restarts (invokable by number or by possibly-abbreviated name):
0: [RETRY ] Try loading the foreign library again.
1: [USE-VALUE ] Use another library instead.
2: [TRY-RECOMPILING ] Recompile system and try loading it again
3: [RETRY ] Retry
loading FASL for #<CL-SOURCE-FILE "cl-mysql" "system">.
4: [ACCEPT ] Continue, treating
loading FASL for #<CL-SOURCE-FILE "cl-mysql" "system">
as having been successful.
5: [ABORT ] Give up on "dbd-mysql"
6: Recompile sql-demo and try loading it again
7: Retry
loading FASL for #<CL-SOURCE-FILE "sql-demo" "sql-demo">.
8: Continue, treating
loading FASL for #<CL-SOURCE-FILE "sql-demo" "sql-demo">
as having been successful.
9: Retry ASDF operation.
10: [CLEAR-CONFIGURATION-AND-RETRY] Retry ASDF operation after resetting the
configuration.
11: Give up on "sql-demo"
12: Exit debugger, returning to top level.
(CFFI::FL-ERROR "Unable to load any of the alternatives:~% ~S" ("libmysqlclient.20.dylib" "libmysqlclient.dylib"))
These are the files in mysql lib directory (/usr/local/mysql/lib) :
sun: /usr/local/mysql/lib $ ll
total 1391056
drwxr-xr-x 11 root wheel 374 2 18 2016 ./
drwxr-xr-x 12 root wheel 408 2 18 2016 ../
-rwxr-xr-x 1 root wheel 4696864 2 2 2016 libmysqlclient.20.dylib*
-rw-r--r-- 1 root wheel 11075208 2 2 2016 libmysqlclient.a
lrwxr-xr-x 1 root wheel 23 2 18 2016 libmysqlclient.dylib@ -> libmysqlclient.20.dylib
-rw-r--r-- 1 root wheel 329627816 2 2 2016 libmysqld-debug.a
-rw-r--r-- 1 root wheel 366774784 2 2 2016 libmysqld.a
-rw-r--r-- 1 root wheel 36464 2 2 2016 libmysqlservices.a
drwxr-xr-x 4 root wheel 136 2 2 2016 mecab/
drwxr-xr-x 3 root wheel 102 2 2 2016 pkgconfig/
drwxr-xr-x 40 root wheel 1360 2 2 2016 plugin/
What should I do ?
Non thread safe connect-cached
Seems that cleanup-connection-pool should acquire a lock on a hashtable, but it didn't.
I've just catched such error:
Traceback (most recent call last):
File "NIL", line NIL, in SIGNAL-CO…
(SB-IMPL::SIGNAL-CORRUPT-HASH-TABLE
#<HASH-TABLE :TEST EQ :COUNT 15 {10021904E3}>)
File "NIL", line NIL, in (FLET BOD…
((FLET "BODY-FUN-0" :IN REMHASH))
File "NIL", line NIL, in REMHASH
(REMHASH
#<SB-THREAD:THREAD "Anonymous thread" FINISHED values: NIL {1007AEFF63}>
#<HASH-TABLE :TEST EQ :COUNT 15 {10021904E3}>)
File "/app/quicklisp/dists/quicklisp/software/cl-dbi-20190202-git/src/dbi.lisp", line 115, in CLEANUP-C…
(DBI::CLEANUP-CONNECTION-POOL)
File "/app/quicklisp/dists/quicklisp/software/cl-dbi-20190202-git/src/dbi.lisp", line 98, in CONNECT-C…
(DBI:CONNECT-CACHED :POSTGRES :HOST "postgres" :DATABASE-NAME "ultralisp"
:USERNAME "ultralisp" :PASSWORD "ultralisp")
File "/app/src/db.lisp", line 44, in CONNECT
(ULTRALISP/DB::CONNECT :HOST NIL :DATABASE-NAME NIL :USERNAME NIL :PASSWORD
NIL :CACHED T)
File "/app/src/cron.lisp", line 64, in PERFORM-C…
(ULTRALISP/CRON::PERFORM-CHECKS)
File "/app/quicklisp/dists/quicklisp/software/bordeaux-threads-v0.8.6/src/bordeaux-threads.lisp", line NIL, in (LAMBDA (…
((LAMBDA () :IN BORDEAUX-THREADS::BINDING-DEFAULT-SPECIALS))
File "NIL", line NIL, in (FLET BOD…
((FLET SB-UNIX::BODY :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE))
File "NIL", line NIL, in (FLET WIT…
((FLET "WITHOUT-INTERRUPTS-BODY-4" :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE))
Condition: Corrupt NEXT-chain in #<HASH-TABLE :TEST EQ :COUNT 15 {10021904E3}>. This is probably caused by multiple threads accessing the same hash-table without locking.
SQLite error handling problem when using prepared queries.
When certain errors occur while executing an SQLite prepared query, no condition is
signalled until the next time the statement is used. As a result, I have to add special
SQLite-specific code to my project to get correct results.
Reproduction:
(defvar *conn* (connect :sqlite3 :database-name #P"/tmp/test.sqlite"))
(dbi:execute (dbi:prepare *conn* "CREATE TABLE TEST (FOO INTEGER PRIMARY KEY);"))
(let ((prepared (dbi:prepare *conn* "INSERT INTO TEST (FOO) VALUES (?);")))
(dbi:execute prepared 1)
(dbi:execute prepared 1)
(format t "That should have resulted in an error, since we inserted two 1s, but the error~%")
(format t "will be deferred until SQLITE:RESET-STATEMENT is called next.~%")
(format t "Press ENTER to trigger the improperly deferred error: ")
(read-line)
(dbi:execute prepared 2))
Solution: Call sqlite:reset-statement after using the statement, and before dbi:execute returns.
Workaround: End users can add the following method to their project to make
the errors throw as soon as the failing query returns.
(defmethod dbi:execute :after ((query dbd.sqlite3::<dbd-sqlite3-query>)
&rest parameters)
(declare (ignore parameters))
(sqlite:reset-statement (slot-value query 'dbi.driver::prepared)))
MySQL memory leak
(let* ((pq (dbi:prepare conn "SELECT * FROM visit"))
(query (dbi:execute pq nil)))
(dbi:fetch-all query))
above code run in hunchentoot will lead to memory leak in MySQL
https://blog.actorsfit.in/a?ID=01800-28830ddf-a436-4fff-b97d-d952487c8948
the result set of mysql_store_result(), mysql_use_result(), and mysql_list_dbs() must call mysql_free_result() to release the memory used by the result set after completing the operation on the result set.
(defmethod execute-using-connection ((conn dbd-mysql-connection) (query dbd-mysql-query) params)
(let* (took-usec
(result
(with-error-handler conn
(with-took-usec took-usec
(query (funcall (query-prepared query) params)
:database (connection-handle conn)
:store nil)))))
(return-or-close (owner-pool result) result)
(next-result-set result)
(cond
((mysql-use-store query)
(multiple-value-bind (rows count)
(fetch-all-rows result)
**(cl-mysql-system::mysql-free-result (cl-mysql-system::result-set result))**
(sql-log (query-sql query) params count took-usec)
(setf result (make-mysql-result-list rows count))
(setf (query-row-count query) count)))
(t
(sql-log (query-sql query) params nil took-usec)))
(setf (query-results query) result)
query))
cl-dbi does not work when using PostgreSQL@1279347a4f
In CL-DBI 1279347 & postgresql, I run the following code
(ql:quickload "dbi")
(defparameter *pg-connection*
(dbi:connect :postgres
:host "192.168.59.104"
:port 15432
:database-name "test_db"
:username "postgres"))
(dbi:execute (dbi:prepare *pg-connection*
"CREATE TABLE TestTable (user_id SERIAL PRIMARY KEY,user_name TEXT)"))
I got the following error
> Error: The value NIL can not be used to set the value of the slot DBD.POSTGRES::%MODIFIED-ROW-COUNT in #<DBD.POSTGRES:<DBD-POSTGRES-CONNECTION> #x30200115D02D>, because it is not of type FIXNUM.
let in usage example should be let*
@6502 pointed out to me on Twitter that the let form in the usage example in README.md should be let*.
Range of elements
How could I do this query (from PostgreSQL SQL dialect)?
SELECT * FROM student ORDER BY id DESC LIMIT (20-10+1) OFFSET 10;sqlite3 transaction behavior changed
I'm still in the process of tracking things down, but my code used to work before:
02d7b41 | * | @ Refactored the way how transactions and savepoints are handled.
dbi table exists function?
Would it be possible to add something like:
https://rdrr.io/cran/DBI/man/dbExistsTable.html
?
thanks!
CL-MSSQL
What would need to happen before "MS SQL Server" could be added to this list https://github.com/fukamachi/cl-dbi#databases ?
CL-DBI throws EXCEPTION_ACCESS_VIOLATION when used with MYSQL database query with multiple columns
Lisp Environment: SBCL on Windows
OS: Windows 8.1 Enterprise and Windows 10 Personal
DB : MYSQL 64 bit
Scenario: Any query with more than one column or a * in it will trigger this issue. Find more in the stack trace below.
Code:
(dbi:connect :mysql
:host "127.0.0.1"
:database-name "testdatabase"
:username "Testuser"
:password "Testuser"))
(setq query (dbi:prepare conn "select name, username from crm_users;"))
(setq result (dbi:execute query))
stack trace
EXCEPTION_ACCESS_VIOLATION
[Condition of type SIMPLE-ERROR]
Restarts:
0: [RETRY] Retry SLIME REPL evaluation request.
1: [*ABORT] Return to SLIME's top level.
2: [ABORT] abort thread (#<THREAD "repl-thread" RUNNING {1003750033}>)
Backtrace:
0: (CFFI::FOREIGN-STRING-LENGTH #.(SB-SYS:INT-SAP #X8D420EB00000000) :ENCODING :UTF-8 :OFFSET 0)
1: (CFFI:FOREIGN-STRING-TO-LISP #.(SB-SYS:INT-SAP #X8D420EB00000000) :OFFSET 0 :COUNT NIL :MAX-CHARS 4611686018427387900 :ENCODING :UTF-8)
2: ((:METHOD CFFI:TRANSLATE-FROM-FOREIGN (T CFFI::FOREIGN-STRING-TYPE)) #.(SB-SYS:INT-SAP #X8D420EB00000000) #<CFFI::FOREIGN-STRING-TYPE :UTF-8>) [fast-method]
3: (COM.HACKINGHAT.CL-MYSQL-SYSTEM::PROCESS-ROW # # # # #)
4: ((:METHOD COM.HACKINGHAT.CL-MYSQL-SYSTEM::RESULT-DATA (COM.HACKINGHAT.CL-MYSQL-SYSTEM:CONNECTION T)) # #) [fast-method]
5: (COM.HACKINGHAT.CL-MYSQL-SYSTEM:QUERY "select name, username from crm_users;" :TYPE-MAP NIL :DATABASE #<COM.HACKINGHAT.CL-MYSQL-SYSTEM:CONNECTION-POOL {1006CC1B33}> :STORE T)
6: ((:METHOD DBI.DRIVER:EXECUTE-USING-CONNECTION (DBD.MYSQL: DBD.MYSQL: T)) #DBD.MYSQL:<DBD-MYSQL-CONNECTION {1006FB4203}> #DBD.MYSQL:<DBD-MYSQL-QUERY {10074BE5..
7: ((LAMBDA ()))
8: (SB-INT:SIMPLE-EVAL-IN-LEXENV (SETQ RESULT (DBI.DRIVER:EXECUTE QUERY)) #)
9: (EVAL (SETQ RESULT (DBI.DRIVER:EXECUTE QUERY)))
10: (SWANK::EVAL-REGION "(setq query (dbi:prepare conn "select name, username from crm_users;")) ..)
11: ((LAMBDA NIL :IN SWANK-REPL::REPL-EVAL))
12: (SWANK-REPL::TRACK-PACKAGE #<CLOSURE (LAMBDA NIL :IN SWANK-REPL::REPL-EVAL) {10071A960B}>)
13: (SWANK::CALL-WITH-RETRY-RESTART "Retry SLIME REPL evaluation request." #<CLOSURE (LAMBDA NIL :IN SWANK-REPL::REPL-EVAL) {10071A956B}>)
14: (SWANK::CALL-WITH-BUFFER-SYNTAX NIL #<CLOSURE (LAMBDA NIL :IN SWANK-REPL::REPL-EVAL) {10071A954B}>)
15: (SWANK-REPL::REPL-EVAL "(setq query (dbi:prepare conn "select name, username from crm_users;")) ..)
16: (SB-INT:SIMPLE-EVAL-IN-LEXENV (SWANK-REPL:LISTENER-EVAL "(setq query (dbi:prepare conn "select name, username from crm_users;")) ..)
17: (EVAL (SWANK-REPL:LISTENER-EVAL "(setq query (dbi:prepare conn "select name, username from crm_users;")) ..)
18: (SWANK:EVAL-FOR-EMACS (SWANK-REPL:LISTENER-EVAL "(setq query (dbi:prepare conn "select name, username from crm_users;")) ..)
19: (SWANK::PROCESS-REQUESTS NIL)
20: ((LAMBDA NIL :IN SWANK::HANDLE-REQUESTS))
21: ((LAMBDA NIL :IN SWANK::HANDLE-REQUESTS))
22: (SWANK/SBCL::CALL-WITH-BREAK-HOOK # #<CLOSURE (LAMBDA NIL :IN SWANK::HANDLE-REQUESTS) {100375800B}>)
23: ((FLET SWANK/BACKEND:CALL-WITH-DEBUGGER-HOOK :IN "c:/Users/i072746/quicklisp/dists/quicklisp/software/slime-2.14/swank/sbcl.lisp") # #<CLOSURE (LAMBDA NIL :IN SWANK..
24: (SWANK::CALL-WITH-BINDINGS ((STANDARD-OUTPUT . #1=#<SWANK/GRAY::SLIME-OUTPUT-STREAM {1003736333}>) (STANDARD-INPUT . #2=#<SWANK/GRAY::SLIME-INPUT-STREAM {1002B28003}>) (TRACE-OUTPUT . #1#) (*ERR..
25: (SWANK::HANDLE-REQUESTS #<SWANK::MULTITHREADED-CONNECTION {1002C2C713}> NIL)
26: ((FLET #:WITHOUT-INTERRUPTS-BODY-1024 :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE))
27: ((FLET SB-THREAD::WITH-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE))
28: ((FLET #:WITHOUT-INTERRUPTS-BODY-335 :IN SB-THREAD::CALL-WITH-MUTEX))
29: (SB-THREAD::CALL-WITH-MUTEX #<CLOSURE (FLET SB-THREAD::WITH-MUTEX-THUNK :IN SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE) {968FB5B}> #<SB-THREAD:MUTEX "thread result lock" owner: #<SB-THREAD:THREAD "..
30: (SB-THREAD::INITIAL-THREAD-FUNCTION-TRAMPOLINE #<SB-THREAD:THREAD "repl-thread" RUNNING {1003750033}> NIL #<CLOSURE (LAMBDA NIL :IN SWANK-REPL::SPAWN-REPL-THREAD) {100374FF9B}> (#<SB-THREAD:THREAD "re..
31: ("foreign function: #x42E62C")
32: ("foreign function: #x4032CE")
33: ("foreign function: #x906F07B")
Incorrect defmethod
According to the Hyperspec, a method must accept all keywords which are part of the generic function definition. They can accept more, but at least they have to accept those ones.
The problem with cl-dbi is that it does not have an explicit defgeneric statement for dbi-interface.pepare. This means that the first method that appears, which is in src/driver.lisp, gets to set the generic function list, including a &key query-class argument. Unfortunately, none of the child files includes this keyword arguments in the subsequent method definitions.
The following patch solves the problem:
https://dl.dropbox.com/u/23177754/121021-cl-dbi.patch
The id of the new record with PostgreSQL, how?
One stupid question: with PostgreSQL, how would you get the id of the newly created record?
(with-connection (db :school)
(execute
(insert-into :students
(set=
:last_name "Uzumaki"
:first_name "Naruto"
:address "Ever green st #598, Konoha"
(returning :id)))))Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.