Saving BLOBs

Saving BLOBs

Storm does not directly support saving BLOB columns. This is because Storm creates SQL behind the scenes for you and BLOBs cannot be used with SQL. You have a few options:

Don’t store the BLOB in the database

Rather than storing the binary data directly in the database, it is often faster and more efficient to store the data in the file system and then store the path to the file in the database. This works well for pictures and other binary objects.

Convert BLOBs

One strategy that you can try is to convert your BLOB object to Base64 text and then add that to the database. When you retreive the data from the database, you would have to use DecodeBase64 to convert the data back to its original format. This might work, but only for smaller BLOBs because this would greatly increase the length of the SQL sent to the database and may run into limits with SQLite.

Save the BLOBs Manually Outside Storm

You could add new methods that are responsible for saving/loading the BLOB to your DBObject subclass. In this method, you would directly get the BLOB using a RecordSet and directly save it using a RecordSet or DatabaseRecord.

Sample Code for retrieving a Picture:

Dim rs As RecordSet rs = Storm.DBConnection.Default.SQLSelect(“SELECT BlobCol FROM MyTable WHERE ID = “ + Str(Self.ID))

Dim blobValue As Picture blobValue = rs.IdxField(1).PictureValue

rs.Close

Return blobValue