Here's how to store binary data in a PostgreSQL Bytea column without
needing to escape it first:
(This requires pgSQL4RB v2 in demo mode or with the "Extended" License.)
Create a new empty REALbasic GUI project and import the pgSQL4RB classes.
Add a pgSQLcoreSocket and a pgSQLcoreCache to the window and bind
them together using visual control-binding.
Enter your connection parameters in pgSQLcoreSocket1's properties pane.
Create a new set of FileTypes to accept all file types.
In your PostgreSQL database, create a new table named BTable having a
text column named TextColumn and a bytea column named BinaryColumn.
In pgSQLcoreSocket1's Connected event:
Sub Connected()
Cache.QueueSimpleQuery("PREPARE upload (TEXT, BYTEA) AS INSERT INTO
BTable (TextColumn, BinaryColumn) VALUES ($1, $2);")
End Sub
In Window1's Open event:
Sub Open()
me.AcceptFileDrop("allfiles")
me.pgSQLcoreSocket1.Connect
End Sub
In Window1's DropObject event:
Sub DropObject(obj As DragItem, action As Integer)
Dim TheFile as BinaryStream
Dim ParameterValues(), s as String
Dim EmptyArray(), ParameterFormats() as Integer
Const SimpleProtocol = False
If pgSQLcoreSocket1.Connected then
TheFile = obj.FolderItem.OpenAsBinaryFile
While not TheFile.EOF
s = TheFile.read(99999) //BinaryStream.ReadAll seems to be
broken in 2007r1
Wend
TheFile.Close
If SimpleProtocol Then //Simple Query Protocol WITH escaping
pgSQLcoreCache1.QueueSimpleQuery("INSERT INTO BTable
(TextColumn, BinaryColumn) VALUES ('" + obj.FolderItem.DisplayName +
"', '" + pgSQLescapeBytea(s) + "');")
Else //Extended Query Protocol WITHOUT escaping
ParameterValues.Append(obj.FolderItem.DisplayName)
ParameterFormats.Append(0) //first parameter is in text form
ParameterValues.Append(s)
ParameterFormats.Append(1) //second parameter is in binary form
pgSQLcoreCache1.ClearExtendedQuery
pgSQLcoreCache1.AppendBindCommand("upload", ParameterFormats(),
ParameterValues(), "", EmptyArray())
pgSQLcoreCache1.AppendExecuteCommand("", 0)
pgSQLcoreCache1.AppendsyncCommand
pgSQLcoreCache1.SendExtendedQuery
end if
End if
End Sub
Run your application now and drop files on the window to store them
in PostgreSQL.
(Change the constant SimpleProtocol to True or False depending on
whether you want to use the Simple or Extended Query Protocol.)
As discussed in previous message, this is to store binary data in a
Bytea field, not in the Large Object Table (aka BLOB).
Enjoy,
Marc
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>
