s c h e m a t i c s : c o o k b o o k

/ Cookbook.SaveBinaryDataInSQL

This Web


WebHome 
WebChanges 
TOC (with recipes)
NewRecipe 
WebTopicList 
WebStatistics 

Other Webs


Chicken
Cookbook
Erlang
Know
Main
Plugins
Sandbox
Scm
TWiki  

Schematics


Schematics Home
Sourceforge Page
SchemeWiki.org
Original Cookbook
RSS

Scheme Links


Schemers.org
Scheme FAQ
R5RS
SRFIs
Scheme Cross Reference
PLT Scheme SISC
Scheme48 SCM
MIT Scheme scsh
JScheme Kawa
Chicken Guile
Bigloo Tiny
Gambit LispMe
GaucheChez

Lambda the Ultimate
TWiki.org

Save Binary Data in SQL

Problem

Postgres has a binary datatype. However, if your using hans's sqlid database driver with postgres, and try to save a bytestring to the postgres binary datatype, it will break when it encounters unusual charactors/bytes. (example: a ascii 0)

Note: hans's package is a planet package:

(require (planet "sqli.scm" ("oesterholt" "sqlid.plt" 1 5)))
(require (planet "sqld-psql.scm" ("oesterholt" "sqld-psql.plt" 1 0)))

Solution

TO SAVE A BYTESTRING TO POSTGRES: (to a "bytea" datatype field)

(define (octify byte-in)
 (list->string (list #\                     #\                     (integer->char (+ 48 (quotient byte-in 64)))
                     (integer->char (+ 48 (modulo (quotient byte-in 8) 8)))
                     (integer->char (+ 48 (modulo byte-in 8))))))

(define (bytes->sql-bytea bytes-in)
 (apply string-append
        (map octify
             (bytes->list bytes-in))))


(sqli-query sqli "INSERT INTO blobdemo (file) VALUES ($1)"
             (bytes->sql-bytea bytestring))

TO READ A BYTESTRING FROM POSTGRES: (from a postgres "bytea" datatype field)

(define (fix-quotes-for-sql-octal-input string-in)
 (regexp-replace* (list->string (list #\"))
                  string-in
                  (list->string (list #\\ #\\ #\"))
                  ))

(define (hans-get-everything-test)
 (define results (sqli-query sqli "SELECT ALL * FROM blobdemo where id = 32"))
 (sqli-fetchall sqli))


(letrec ([result (hans-get-everything-test)]
        [data (read (open-input-string (string-append "#\""

(fix-quotes-for-sql-octal-input
                                                       (second (first result)))  ;;;you should do somethign prettier then this to access your data.  this is just for a example.
                                                      "\"")))]
 data)

Discussion

I've only tested this on postgres, and i've only tested it with a small amount of data so far. It may work on other database systems, but I have no way of testing that.


Comments about this recipe

Contributors

-- HiSeeComments - 04 Jul 2008

CookbookForm
TopicType: Recipe
ParentTopic: DatabaseRecipes
TopicOrder: 999

 
 
Copyright © 2004 by the contributing authors. All material on the Schematics Cookbook web site is the property of the contributing authors.
The copyright for certain compilations of material taken from this website is held by the SchematicsEditorsGroup - see ContributorAgreement & LGPL.
Other than such compilations, this material can be redistributed and/or modified under the terms of the GNU Lesser General Public License (LGPL), version 2.1, as published by the Free Software Foundation.
Ideas, requests, problems regarding Schematics Cookbook? Send feedback.
/ You are Main.guest