Code Optimization, PostgreSQL BLOBs (Cheat Method)

Frank D. Engel, Jr. fde101 at fjrhome.net
Tue Oct 12 13:34:13 EDT 2004


After some research and "playing," I've *finally* found a way to get 
info into and out of PostgreSQL BYTEA BLOBs through RevDB, but it is 
painfully slow.  I wrote the following functions:

function dbBLOB bdat
   put "'" into x
   repeat for each char c in bdat
     put format("\\\\%03o", charToNum(c)) after x
   end repeat

   return x & "'::bytea"
end dbBLOB

function dbUNBLOB adat
   put empty into x
   put 0 into y
   put 0 into n
   put false into esc
   repeat for each char c in adat
     if esc then
       if c is "\" then
         put "\" after x
         put false into esc
       else
         put (8 * y) + c into y
         add 1 to n
         if n is 3 then
           put numToChar(y) after x
           put 0 into y
           put 0 into n
           put false into esc
         end if
       end if
     else if c is "\" then put true into esc
     else put c after x
   end repeat

   return x
end dbUNBLOB


dbBLOB encodes binary data stored in strings (such as the text property 
of an image) into a BYTEA literal which can be used in an INSERT or 
UPDATE statement:

on mouseUp
   revExecuteSQL the database of this stack, "INSERT INTO table1 VALUES 
(" & dbBLOB(image "Image 1") & ")"
end mouseUp


dbUNBLOB decodes binary data returned from the server back into a 
string:

on mouseUp
   put revQueryDatabase(the database of this stack, "SELECT * FROM 
table1") into q
   if q is an integer then
     if revNumberOfRecords(q) > 0 then
       put dbUNBLOB(revDatabaseColumnNumbered(q, 1)) into image "Image 1"
     else answer "No Records"
     revCloseCursor q
   else answer q titled "Error"
end mouseUp


Perhaps the functions will help someone else in a similar situation; 
also, if anyone can find a way to speed these up, I would certainly 
appreciate it...

-----------------------------------------------------------
Frank D. Engel, Jr.  <fde101 at fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$ 


More information about the use-livecode mailing list