Re: How to insert blobs? (sqlite3 backend)

Hello,

just wanted to ask if someone has any idea with my problem on inserting
blobs into a sqlite3 database.

I appreciate your anwers!

Kind regards, Daniel.

Am 13.07.2010 17:43, schrieb Daniel Brunner:
> Hi everybody,
> 
> I am using CLSQL for a few weeks on a small project. I didn't manage to
> insert a blob into a SQLite3 database. As long as I understood this
> should be possible. I didn't find it in the documentation neither on the
> web. Therefore I would like to raise that question here on the list
> hoping for a hint.
> 
> 1. I create a new database:
> 
> --8<--8<--8<--
> (require 'clsql)
> (use-package :clsql)
> 
> (defvar *x*)
> (enable-sql-reader-syntax)
> 
> (connect '("/home/dbr/test.sqlite") :database-type :sqlite3)
> 
> (create-table [test]
> 	      '(([name] text)
> 		([picture] blob)))
> --8<--8<--8<--
> 
> 
> 2. When I insert a binary file (png file) using another into that table
> using another front end I can select it:
> 
> --8<--8<--8<--
> (setf *x* (caar (select [picture] :from [test]
> 			:where [= [name] "emacs" ] )))
> (type-of *x*)
> ;; --> (SIMPLE-ARRAY (UNSIGNED-BYTE 8) (2162))
> --8<--8<--8<--
> 
> So it seems that the BLOBS come as arrays of type unsigned-byte.
> 
> 3. But I have no clue how to insert a blog into that database using
> CLSQL. One thing I tried for example has been:
> 
> --8<--8<--8<--
> (insert-records :into [test]
> 		:values (list "emacs2"
> 			      *x*))
> --8<--8<--8<--
> 
> But that resulted in an error:
> 
> While accessing database #<SQLITE3-DATABASE /home/dbr/test.sqlite OPEN
> {AD05BE1}>
>   with expression "INSERT INTO TEST VALUES ('emacs2',137,80,78,... )":
>   Error 1 / table TEST has 2 columns but 2163 values were supplied
>   has occurred.
>    [Condition of type SQL-DATABASE-DATA-ERROR]
> 
> Restarts:
>  0: [RETRY] Retry SLIME interactive evaluation request.
>  1: [ABORT] Return to SLIME's top level.
>  2: [TERMINATE-THREAD] Terminate this thread (#<THREAD "worker" RUNNING
> {B26B281}>)
> 
> Backtrace:
>   0: ((SB-PCL::FAST-METHOD CLSQL-SYS:DATABASE-EXECUTE-COMMAND (T
> CLSQL-SQLITE3:SQLITE3-DATABASE)) ..)
>   1: ((LAMBDA (SB-PCL::.PV. SB-PCL::.NEXT-METHOD-CALL. SB-PCL::.ARG0.
> SB-PCL::.ARG1.)) ..)
>   2: ((SB-PCL::FAST-METHOD EXECUTE-COMMAND (STRING)) ..)[:EXTERNAL]
>   3: ((SB-PCL::FAST-METHOD EXECUTE-COMMAND (CLSQL-SYS::%SQL-EXPRESSION))
> ..)[:EXTERNAL]
>   4: (SB-INT:SIMPLE-EVAL-IN-LEXENV (INSERT-RECORDS :INTO
> #<CLSQL-SYS:SQL-IDENT-ATTRIBUTE TEST> :VALUES (LIST "emacs2" *X*))
> #<NULL-LEXENV>)
>   5: ((LAMBDA ()))
> 
> 
> 
> Does anybody can provide a short code snippet that shows how to use
> blobs with SQLite3/CLSQL or provide a link to a how-to?
> 
> I would very much appreciate your help and sorry for the noise if the
> question is too dumb.
> 
> My system:
> Kubuntu 10.04
> SBCL 1.0.40
> CLSQL 5.1.1
> 
> 
> Kind regards, Daniel.
>