Omnis Technical Note TNGI0008

Storing JPEGs in a Server Database

For Omnis Studio
By Chris Stevens
Solution provided by Ian Thurbon
Question posed by Lee Snover

Storing JPEGs in a Server Database

For Omnis Studio
By Chris Stevens
Solution provided by Ian Thurbon
Question posed by Lee Snover


How can I bring JPEGs into Studio and store them in an Oracle database?


Use the following method to read the JPEG, in this case a file called Tuscany.JPG,
into Studio by storing it in a Binary variable.

; Contains the following local variables
; lError of type Long integer
; lFileObj of type Object, subtype FileOps.FileOps
; lJpegPath of type Character 1000
; Contains the following Instance variables
; iBinary of type Binary
; iRetrievedjpeg of type Binary
; iFilename of type Character 32
; This gets the path of the jpeg file
Do $cinst.$GetJPEGPath('Tuscany') Returns lJpegPath
; The $GetJPEGPath method is described below
; Create an instance of the file and read the data
; into a binary variable
Do lFileObj.$openfile(lJpegPath)
If lFileObj.$isopen()
     Do lFileObj.$readfile(iBinary) Returns lError
     D
o lFileObj.$closefile()
     If lError<>1
         OK message {FileOps Error code [lError] when
            reading a file}
         Quit method 0
     End If
     Quit method 1
Else
     OK message Error {[lJpegPath] does not exist}
     Quit method 0
End If


Use the following method to create an Oracle Table/Item and store the
JPEG from the Studio variable.


; Create a table on the database to store the Jpeg
; The table has three columns id,jpeg & name
Set current session {oraclejpeg}
Perform SQL {drop table Jpegtable} ;; just in case it exists
Perform SQL {create table Jpegtable (Id Number(11,0),
     Name Varchar(256), Jpeg Long Raw)}
     ; see notes below about Datatype
If flag false
OK message Error {Failed to create table on server [sys(132)]}
Quit method 0
End If
; This inserts the jpeg into the database
Perform SQL {insert into Jpegtable
     values (1,@[iFileName],@[iBinary])}
If flag false
   OK message Error {Failed to insert jpeg
     into database. [sys(132)]}
   Quit method 0
End If
Quit method 1


For ORACLE, the Datatype must be set to LONG RAW.
For SYBASE, the Datatype must be set to IMAGE (You Must use the SET TEXTSIZE SQL command first, otherwise the default size is 32K).
For DB2, the Datatype should be set to BLOB (You Must use the BLOBSIZE () command first, default size is 64K).
For ODBC, the item should be set to SQL_LONGVARBINARY (or the relevant server Datatype).

For further details see Chapter 15, Server Specific Programming of the Omnis Studio v2 programming manual.


Use the following method to retrieve the JPEG from the Oracle database

; This method retrieves the jpeg from the server
Set current session {oraclejpeg}
Perform SQL {select Jpeg from Jpegtable where Id=1}
If flag false
   OK message Error {Failed to select jpeg
     from database. [sys(132)]}
   Quit method 0
End If
Fetch next row into { iRetrievedJpeg }
If flag false
   OK message Error {Failed to retrieve jpeg
     from sql buffer. [sys(132)]}
   Quit method 0
End If
Quit method 1

; Method $GetJPEGPath
; Contains the following Parameter variables
; pFileName of type Character 256
; Contains the following Local variables
; lDrive of type Character 32
; lExtension of type Character 32
; lFolder of type Character 256
; lPath of type Character 10000
; The jpeg will need to be located at the same level as the
; library in order for this method to find it.
Calculate lPath as sys(10)
Do FileOps.$splitpathname(lPath, lDrive,
     lFolder, iFileName, lExtension)
Calculate iFileName as pFileName
Calculate lExtension as '.jpg'
Calculate lPath as con(lDrive,lFolder,iFileName,lExtension)
Quit method lPath