gmane.lisp.clsql.general

"No source tables supplied to select statement."

Petter Gustad — 2013-04-19T12:57:20

I keep getting the above error if I define the following form inside a
function. I have the SQL reader macros enabled:

REGLIB> (get-macro-character #\[ )
#<FUNCTION CLSQL-SYS::SQL-READER-OPEN>
NIL

If I simply run it it in the REPL it works as expected:

REGLIB> (first (clsql:select [max [id]] :from [regrun] :flatp t :field-names nil :database *database*))
1338

But if I embed it inside a function it will not work:

REGLIB> (defun get-latest-regrun-id (&optional (db *database*))
  "return the id of the latest regrun"
  (first (clsql:select [max [id]] 
        :from [regrun]
       :flatp t 
       :field-names nil
       :database db)))

STYLE-WARNING: redefining REGLIB::GET-LATEST-REGRUN-ID in DEFUN
GET-LATEST-REGRUN-ID
REGLIB> (get-latest-regrun *database*)
; Evaluation aborted on #<SIMPLE-ERROR "No source tables supplied to select statement." {1007195083}>.

The result is the same if I compile the function either in SLIME or by
using asdf:load-system.

The odd thing is that it used to work at so

ccl += weak hash

Ben Hyde — 2013-03-29T11:38:44


diff --git a/sql/utils.lisp b/sql/utils.lisp
index 39ee349..2f3c1db 100644
--- a/sql/utils.lisp
+++ b/sql/utils.lisp
< at >< at > -443,7 +443,7 < at >< at > removed. keys are searched with #'MEMBER"
     ;;clearing mechanism. If you are on an implementation that doesn't support
     ;;weak hash tables then you're memory may accumulate.
 
-    #-(or sbcl allegro clisp lispworks)
+    #-(or sbcl allegro clisp lispworks ccl)
     (warn "UNSAFE! use of weak hash on implementation without support. (see clsql/sql/utils.lisp to add)")
 
     (make-hash-table
< at >< at > -451,6 +451,7 < at >< at > removed. keys are searched with #'MEMBER"
       #+clisp     :weak      #+clisp :value
       #+lispworks :weak-kind #+lispworks :value
       #+sbcl :weakness #+sbcl :value
+      #+ccl :weak #+ccl :value
       ,< at >args)
     ))

clsql-sys::|view-class-slot-autoincrement-sequence|

Ben Hyde — 2013-03-28T01:04:20

Loading clsql into ccl doesn't work out well:

cl-user> (handler-case (ql:quickload "clsql") (error (e) (princ e)))
To load "clsql":
  Load 1 ASDF system:
    clsql
; Loading "clsql"
Undefined function clsql-sys::|view-class-slot-autoincrement-sequence| called with arguments (#<view-class-direct-slot-definition for instance slot clsql-sys::view-database #x302001C1DC1D>) .
...

No doubt more knowledgable eyes will scoff, but this fix allowed me to move onto the next puzzle:

diff --git a/sql/metaclasses.lisp b/sql/metaclasses.lisp
index 1c9a6c5..830cf2c 100644
--- a/sql/metaclasses.lisp
+++ b/sql/metaclasses.lisp
< at >< at > -482,7 +482,7 < at >< at > implementations."
 
          (macrolet
              ((safe-copy-value (name &optional default)
-                (let ((fn (intern (format nil "~A~A" 'view-class-slot- name ))))
+                (let ((fn (intern (format nil "~< at >:(~A~A~)" 'view-class-slot- name ))))
                   `(setf (slot-value esd ',name)
                     (or (when (slot-boundp dsd ',name)

Password authentification failed after Quicklis update

Nicolas Neuss — 2013-03-07T14:15:54

Hi,

I am using clsql/postgresql and after doing a (ql:update-all-dists) I
cannot connect anymore to my databases with clsql.

While trying to connect to database localhost/noten/neuss
  using database-type POSTGRESQL-SOCKET:
  Error POSTGRESQL-LOGIN-ERROR / FATAL:  password authentication failed for user "neuss"

  has occurred.
   [Condition of type SQL-CONNECTION-ERROR]

I searched the web and found that in December last year a change in MD5
broke authentification.  Is this bug still active?  Does anyone have a
fix?

Thank you,

Nicolas

Running a procedural query ?

Brian Sorg — 2013-03-06T17:45:51

I am trying to run a query through ODBC on a Mssql Server 2008 database.
The query runs on the database as expected, however it is support to return
a single row of results which vary depending on the results of the query. I
have tested the sql query directly against the database and it runs as
expected, however with running it using clsql:query it only returns the
number 1 instead of the expected list of results. Is there a step I am
missing with my call? The following is an example of what I am trying to
run:

---------------------------------------------------------------
(clsql:with-database (*default-database* *mssql-connect-writer-spec*
:database-type :odbc
                                         :if-exists :new :make-default nil)
  (clsql:set-autocommit nil)
  (clsql:query "
DECLARE < at >batchID INT
DECLARE < at >batchIDNext INT
DECLARE < at >result INT
DECLARE < at >errorMessage VARCHAR(255)

clsql and asdf3

Faré — 2013-02-21T03:55:11

Free variable and/or forward reference to special variables = Bug that
prevents compilation with ASDF3.
Same for functions that remain undefined by end of system.

—♯ƒ • François-René ÐVB Rideau •Reflection&Cybernethics• http://fare.tunes.org
The most exciting phrase to hear in science, the one that heralds new
discoveries, is not "Eureka!" (I found it!) but "That's funny ..."
                — Isaac Asimov

; compilation unit finished
;   Undefined functions:
;     CLSQL-SYS::%SLOT-STOREDP CLSQL-SYS::%SLOT-VALUE-LIST
;   Undefined variables:
;     CLSQL-HELPER:*CONNECTION-SETTINGS*
CLSQL-HELPER::*DEFAULT-LOG-FN* CLSQL-HELPER::*RECORD-THIS-DIRTY-SLOT*
CLSQL-HELPER:+A-DAY+ CLSQL-HELPER:+A-MINUTE+ CLSQL-HELPER:+A-MONTH+
CLSQL-HELPER:+A-NEGATIVE-DAY+ CLSQL-HELPER:+A-NEGATIVE-HOUR+
CLSQL-HELPER:+A-NEGATIVE-MINUTE+ CLSQL-HELPER:+A-NEGATIVE-MONTH+
CLSQL-HELPER:+A-NEGATIVE-SECOND+ CLSQL-HELPER:+A-NEGATIVE-YEAR+
CLSQL-HELPER:+A-SECOND+ CLSQL-HELPER:+A-YEAR+ CLSQL-HELPER:+AN-HOUR+
;   caught 15 WARNIN

oracle long type

Frank Grex — 2013-01-21T19:09:29

Hi I have noticed that clsql-oracle interface does not fetch a query where 
the column is of type LONG. It returns only an epmpty string! Is this 
something know? Is there a workaround for it? Thanks

OODML Refactor

Russ Tyndall — 2012-12-06T19:22:28

Over the last couple weeks I have worked through a refactor of the
sql/oodml.lisp code file.

My goals were:
  * unify codepaths for the update-*-from-* functions where possible
  * general cleanup and documentation
  * reduce complexity and improve readability (through smaller,
    functions with less nesting)
  * convert data passed around in cons & lists to instead be
    objects (for readability and in some cases efficiency)

Along the way I found that almost all of the iteration constructs in
the language were used, often nested and in a not-terribly-readable
way.  I chose to standardize on loop unless it was a very simple use
of a different construct, so that hopefully most of the code in this
file looks like the rest of the code in this file.

Normalized view-classes are now better documented as to what
assumptions need to be met.  Also many cases of recursion were
converted to iterative approaches (by recursing in one place and
producing a list that can be iterated over everywhere else). This
separat

SQL parsing irregularity when using SQL reader syntax.

J Phelps — 2012-11-06T00:13:03

Platform: Postgres/Linux

Reproduction:

(let ((my-string (make-string 1 :initial-element #\Nul)))
   (select [foo] :from [bar] :where [= [foo] my-string]))

You should get an error about an unterminated string constant, caused by the nul
character eventually being treated as a C-string terminating nul. Discovered by
a hacker who was trying to SQL-inject me.

Error: Attempt to call #("clsql_mysql_field_flags".....

Raymond de Lacaze — 2012-10-25T22:22:47

Folks,

 

Has anyone seen or been able to resolve the following error:

 

CLSQL-SYS(6): (create-table [vertices] '(([vertexID] integer)([name] (string
45))))

Error: Attempt to call

       #("clsql_mysql_field_flags" 1669840587 0 2 1669840587) for which

       the definition has not yet been (or is no longer) loaded.

  [condition type: SIMPLE-ERROR]

 

 

I initially had problems compiling CLSQL via ASDF and ultimately had to
download a file called libmysql.dll and place it in the db-mysql and uffi
subdirectories.

 

I am able to connect to my database server and run certain commands like
list-tables and insert-records. However other lisp function such as
create-table or select result in the above error.

 

For the record I am running this on a Windows 7 machine, using MySQL Server
5.5 and Allegro Common Lisp 9.0

 

Any clues or suggestions would be most appreciated, as I am pretty knew to
both MySQL and CLSQL. 

 

Below is a short transcript of a short session demonstrating that some
things are in

CLSQL-MYSQL on Lispworks for OSX

Alexandre Paes — 2012-09-18T00:40:54

Hi everyone,

I'm having a slight problem when trying to load clsql-mysql on lispworks in OSX. Basically I'm using 
Quicklisp for loading libraries. loading CLSQL with quicklisp works without a problem by issuing:

CL-USER> (ql:quickload "clsql")
; Loading system definition from /Users/user/quicklisp/dists/quicklisp/software/uffi-20120520-
git/uffi.asd into
; #<The ASDF1 package, 0/16 internal, 0/16 external>
; Registering #<SYSTEM "uffi">
To load "clsql":
 Load 1 ASDF system:
   clsql
; Loading "clsql"

("clsql")

When I try to load CLSQL-MYSQL this is what happens:

CL-USER> (ql:quickload "clsql-mysql")
Error: Couldn't load foreign libraries "libmysqlclient", "libmysql". (searched CLSQL-SYS:*FOREIGN-LIBRARY-
SEARCH-PATHS*)
...

So I added libmysql.dylib folder to clsql-sys:*foreign-library-search-paths* with the following command:

CL-USER> (push #p"/usr/local/mysql/lib/" clsql-sys:*foreign-library-search-paths*)
(#P"/usr/local/mysql/lib/" #P"/Users/user/quicklisp/dists/quicklisp/software/clsql-20120520-gi

CLSQL on ECL

2012-08-23T09:31:52

Hello.
I am trying to use CLSQL with ECL. I know it is unsupported, but
I am not sure if some of these are worth fixing even without supporting
ECL officially.

(I do use CLSQL with SBCL succesfully)

First, I found a clear typo: in sql/db-interface.lisp, 

--- sql/db-interface.lisp
+++ sql/db-interface.lisp
< at >< at > -380,8 +380,8 < at >< at > of TYPE_NAME (keyword) PRECISION SCALE N
 
 (defgeneric db-type-has-auto-increment? (db-type)
   (:method (db-type)
-    (declare (ignore db-type)
-            nil))
+    (declare (ignore db-type))
+            nil)
   (:documentation "NIL [default] if database-type supports auto-incrementing columns."))
 
 ;;; Large objects support (Marc Battyani)

In most implementations it does work and provides correct result, but in
some sense it is accidental.

Second, it looks like CLSQL uses STANDARD-*-SLOT-DEFINITION, 
which may complain about :db-kind slot initialization option being 
unknown. I do not know MOP well enough to understand how this could be
improved. ECL CLOS implementati

clsql on cmucl under 64 bit ubuntu- won't compile

Blake Hurt — 2012-08-20T19:19:05

I don't know if anyone else is having this problem, but I recently 
switched from 32 bit ubuntu linux to 64bit.  I have my familiar CMUCL 
running on the new operating system, but when I try to load CLSQL to run 
as the interface to mysql, it fails to compile.  On the other hand, It 
seems to work perfectly for SBCL under 64 bit, and continues to work 
fine in CMUCL in 32 bits.  The error results appear below.

If anyone has a simple suggestion for correcting this, it would be 
appreciated.

Blake

; CMU Common Lisp 20c release-20c (20C Unicode), running on atlas
With core: /opt/cmucl20c/lib/cmucl/lib/lisp-sse2.core
Dumped on: Thu, 2011-11-03 03:59:03-04:00 on lorien2
See <http://www.cons.org/cmucl/> for support information.
Loaded subsystems:
     Unicode 1.28 with Unicode version 6.0.0
     Python 1.1, target Intel x86/sse2
     CLOS based on Gerd's PCL 2010/03/19 15:19:03

(asdf:operate 'asdf:load-op 
'clsql)                                                [177/1446]

; [GC threshold exceeded with 41,643,9

[PATCH] Clisp portability patch

Matthew Stickney — 2012-07-31T03:37:01

Hi all,

There's a problem loading the current git version of clsql on clisp
2.49 (probably other versions as well). The loop in pool.lisp uses
both an 'always' and 'thereis' clause, which have different default
return values. Clisp believe this is ambiguous, and throws and error.

I've rewritten the loop to avoid the conflicting clauses and provide
what are equivalent semantics in most lisps (and hopefully what was
intended), based on this discussion[1]. It loads on SBCL 1.0.57 and
CLISP 2.49, but I've had some trouble running the tests (getting an
error about the stream for .clsql-test.config having reached its end).

Any help with testing or feedback would be appreciated.

-Matt Stickney

[1] http://comments.gmane.org/gmane.lisp.clisp.general/9079
_______________________________________________
CLSQL mailing list
CLSQL-0lovp2JerKU< at >public.gmane.org
http://lists.b9.com/cgi-bin/mailman/listinfo/clsql

Problem with clsql-mysql.dll

Michael Zuhause — 2012-07-25T18:52:56

Hi,

I tried today to install clsql via quicklisp for my mysql system with 
SBCL on W7. SBCL and Mysql were just installed in the last two weeks and 
supposed to be the most recent stable versions. However, compilation of 
clsql-mysql failed.

(require 'clsql)
...
; compiling (DEFUN MYSQL-DATA-SEEK ...)

; C:/.../db-mysql/ASDF-TMP-mysql-api.fasl written
; compilation finished in 0:00:00.723
ASDF could not load clsql-mysql because
Undefined alien: "clsql_mysql_field_flags".

I searched in the net, apparently two users solved the issue by 
recompiling the clsql_mysql.dll for windows.

I don't have any C development environment and did my last C programming 
a decade ago, so this would be quite adventures for me. OTOH the most 
recent version of the dll I could find is dated 2007. I tried so far to 
do it with Microsofts Visual C++  but failed miserably.

Can someone help? Maybe it's worthwhile to update the dll on Sourceforge 
as well?

Michael
_______________________________________________
CLSQL mailing list

OUTER JOIN

Gleb Golubitsky — 2012-07-23T04:56:10

Hi guys,

Is there any chance to make OUTER JOIN with clsql?

Column aliases

2012-04-29T14:11:26

I've not had much luck finding a way to alias column names.

(clsql:select
  [response_date] [comment] [val]
  :from *comments-view*
  :where [and [= [survey_id] tool] [null [question_id]]])

I've tried

[response_date date]

but I see no difference in the column names or any error.


How can I do this?

CLSQL bugfixes in GIT

Nathan Bird — 2012-04-24T16:15:31

Relevant bits from the ChangeLog:

+2012-04-24  Nathan Bird <nathan-rGvcZsxnnNT9RPGrWp62eA< at >public.gmane.org>
+
+       * sql/expressions.lisp (output-sql): on mysql CREATE TABLE
+       statements use 'ENGINE=innodb' instead of 'Type=InnoDB'. This has
+       apparently been preferred since mysql 4.1 and mysql 5.5 removed
+       type as a valid keyword.
+
+2012-03-28  Russ Tyndall <russ-rGvcZsxnnNT9RPGrWp62eA< at >public.gmane.org>
+
+        * sql/sequences.lisp: [A Patch FROM 2011-07-28 changed sequences.
+       They were previously prefixed with _CLSQL_SEQ_ but are now
+       suffixed with _CLSQL_SEQ. This is likely to break existing
+       implementations using the default sequence names
+
+       setting *old-sequence-names* to T, should force using the older
+       naming scheme
+
+2012-03-27  Ryan Davis <ryan-rGvcZsxnnNT9RPGrWp62eA< at >public.gmane.org>
+
+       * sql/expressions.lisp: Fixed bug with subqueries in the where
+       clause of update-records and delete-records generating invalid
+       SQ

cannot reconcile mysql client library thread requirementswith clsql code

JTK — 2012-04-24T05:31:10

Hello again,

Apologies in advance if this is all completely wrong.

I'm reading the mysql client manual for threads:
http://dev.mysql.com/doc/refman/5.0/en/threaded-clients.html

I'm wondering if I've found serious deficiencies in the way clsql/mysql handles
threads, including missing per-thread init functions, and missing per-thread cleanups.

I'm trying to compare the requirements to  code in clsql-6.0.1/db-mysql

Because 6.0.1 isn't the latest, I also looked in the clsql git tree.

Among other things, mysql documents say:  [** my notes in brackets **]

 ====
You need to know the following if you have a thread that did not create the connection to the MySQL database but is calling MySQL functions:

[** isn't this what happens when a DATABASE is created, put into the pool, and picked up by another thread ? **]

When you call mysql_init(), MySQL creates a thread-specific variable for the thread that is used by the debug
 library (among other things).  If you call a MySQL function before the thread has call

clsql + mysql on sbcl - no Control-C;lisp freezing, but reviving when connection killed

JTK — 2012-04-24T04:08:51

Software: clsql-5.1.3, sbcl 1.0.42  running in Slime on Linux

I know that these are slightly old versions but they are part of a big system that can't be
modified much without endangering other things.

First, I noticed that when a clsql query is running, I can't do a Control-C in emacs/Slime.
It seems that without-interrupts is used only twice in db-mysql/mysql-sql.lisp, in
database-sequence-next and database-sequence-last, so I don't see why this would happen.
Does the mysql library do its own without-interrupts?


Next, and more important, I find the following

 - I'm running a lisp routine that does a large number of fairly short set of queries.  It mostly works
   for many queries.

 - After a long run time, nothing happens in lisp - it just hangs.  For reason above, I can't Control-C
    to see where the hang is.

 - mysql "show processlist;"  shows a process with INFO=NULL (ie, a connection not doing anything).

 - if I kill this NULL mysql connection inside mysql, my lisp process revives, and begin

update-records-from-instance and thread safety

JTK — 2012-04-13T12:53:21


Please forgive any ignorance or misunderstandings in what follows, and any presumption in offering
solutions to problems I might not fully understand ...

First, I'll describe my understanding of what happens with updates ...

When a STANDARD-DB-OBJECT is read from a database, its VIEW-DATABASE
slot contains the database.

This allows UPDATE-RECORDS-FROM-INSTANCE to work.  When this method
sees the VIEW-DATABASE slot set, it does an SQL update; otherwise it uses an SQL insert.

You cannot override the VIEW-DATABASE slot using the :DATABASE keyword in
UPDATE-RECORDS-FROM-INSTANCE.  The VIEW-DATABASE takes precedence.

However, this presents a problem for threads.   If you read an object in one thread
and pass it to another and update it, then you could have two threads using the same
connection, which is very bad, perhaps fatal.

And if you use pooled connections, you can read an object in thread A, return the database to the pool,
where it gets picked up by thread B.  Then you update the object in thread