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
Do
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