• Welcome, Guest. Please login.
 
August 26, 2019, 12:15:52 am

News:

Welcome to the SQLitening support forums!


Write files without slGetFile and slPutFile to blobs 4 Helper functions

Started by cj, August 16, 2017, 10:13:44 pm

Previous topic - Next topic

cj

Using normal PowerBASIC file handling to read local files can eliminate having to load and unload SQLitening routines
if a program is accessing both local flat files and a remote database using the SQLitening server routines.

Using GetLocalFile(sFileName,sData)  and PutLocalFile(sFileName,sData) instead of slGetFile and slPutFile
can eliminate the need to use slSetProcessMods "L0" and slSetProcessMods "L1".

To make more robust need to add TableName and BlobColumnName parameters to GetBlob and PutBlob (to do list) which would match names in create statement.
The PutBlob(sKey,sData) function replaces the data if found or inserts the data if not found.

4 Helper functions:
FUNCTION PutBlob(sBlobKey AS STRING,sBlob AS STRING) THREADSAFE AS LONG
FUNCTION GetBlob(sBlobKey AS STRING,sBlobData AS STRING) THREADSAFE AS LONG
FUNCTION GetLocalFile(sFileName AS STRING, sData AS STRING) THREADSAFE AS LONG
FUNCTION PutLocalFile(sFileName AS STRING, sData AS STRING) THREADSAFE AS LONG


As always, comments welcome!

#INCLUDE "sqlitening.inc"
'-------------------------------------------------------------------------------------------------
FUNCTION PBMAIN() AS LONG

LOCAL sLocalFileName,sLocalData,sData,sKey AS STRING, eCode AS LONG

'create local test file which will later be saved into a SQLitening blob column
sLocalFileName="test.txt"
OPEN sLocalFileName FOR OUTPUT AS #1:PRINT #1,"If you read this, it worked!!":CLOSE

slconnect "think.freemyip.com",47381   
slOpen    "Test.db3","C"                          'database to open
slexe     "create table if not exists PictureTable(blobkey unique,blobdata)"

'read local file
eCode = GetLocalFile(sLocalFileName,sLocalData)
IF eCode THEN ? "GetLocalFile error" + STR$(eCode),%MB_SYSTEMMODAL,"GetLocalFile Error"

'save data to server using key
sKey  = "Heidi Klum"                              'get and save key
eCode = PutBlob(sKey,sLocalData)
IF eCode THEN ? slGetError,%MB_SYSTEMMODAL,"WriteBlob"           :EXIT FUNCTION

'read data from server
eCode = GetBlob(sKey,sData)
IF eCode THEN ? slGetError,%MB_SYSTEMMODAL,"Getblob"             :EXIT FUNCTION
? sData,%MB_SYSTEMMODAL,"GetBlob"

slDisconnect

eCode = PutLocalFile("junk.txt",sData)            'write data to a local file
IF eCode THEN ? "PutLocalFile error" + STR$(eCode),%MB_SYSTEMMODAL,"PutLocalFile":EXIT FUNCTION

eCode = GetLocalFile("junk.txt",sLocalData)
IF eCode THEN ? "GetLocalFile error" + STR$(eCode),%MB_SYSTEMMODAL,"GetLocalFile Error"
? sData,%MB_SYSTEMMODAL,"GetLocalFile"

END FUNCTION
'-------------------------------------------------------------------------------------------------
FUNCTION PutBlob(sBlobKey AS STRING,sBlob AS STRING) THREADSAFE AS LONG

LOCAL NumberOfChanges AS LONG 'function returns 0 on success

NumberOfChanges = slGetChangeCount("T")
slexeBind "replace into PictureTable values('" + sBlobKey + "',?)",slBuildBindDat(sBlob)
IF slGetErrorNumber THEN
   FUNCTION = slGetErrorNumber
   EXIT FUNCTION
END IF
NumberOfChanges = slGetChangeCount("T") - NumberOfChanges
IF NumberOfChanges <> 1 THEN FUNCTION = -9999

END FUNCTION
'-------------------------------------------------------------------------------------------------
FUNCTION GetBlob(sBlobKey AS STRING,sBlobData AS STRING) THREADSAFE AS LONG

sBlobData = ""   'function returns 0 on success
slSel "select blobdata from PictureTable where blobkey ='"+sBlobKey + "'"
IF slGetErrorNumber THEN
  FUNCTION = slGetErrorNumber
  EXIT FUNCTION
END IF
IF slGetRow THEN sBlobData = slf(1)

END FUNCTION
'-------------------------------------------------------------------------------------------------
FUNCTION GetLocalFile(sFileName AS STRING, sData AS STRING) THREADSAFE AS LONG

sData = "" 'function returns 0 on success
LOCAL MaxRetry AS LONG
LOCAL hFile AS LONG

IF ISFALSE(ISFILE(sFileName)) THEN 'local file not found
  FUNCTION = 53                'set error 53, file not found
  EXIT FUNCTION                'exit function
END IF

hFile = FREEFILE              'file handle
FOR MaxRetry = 1 TO 10        'maximum 10 attempts to open
  ERRCLEAR                     'clear error
  OPEN sFileName FOR BINARY ACCESS READ LOCK WRITE AS #hFile 'block writers
  IF ERR THEN                  'open error
   SLEEP 500                   'wait
   ITERATE                     'retry open
  END IF
  GET$ #hFile,LOF(hFile),sData 'read data
  FUNCTION = ERR               '0 if success
  CLOSE #hFile                 'close file
  EXIT FOR
NEXT
FUNCTION = ERR                '10 attempts to open reached

END FUNCTION

FUNCTION PutLocalFile(sFileName AS STRING, sData AS STRING) THREADSAFE AS LONG

LOCAL MaxRetry AS LONG
LOCAL hFile AS LONG

hFile = FREEFILE              'file handle
FOR MaxRetry = 1 TO 10        'maximum 10 attempts to open
  ERRCLEAR                     'clear error
  OPEN sFileName FOR OUTPUT AS #hFile 'exclusive
  IF ERR THEN                  'open error
   SLEEP 500                   'wait
   ITERATE                     'retry open
  END IF
  PRINT #hFile, sData;         'write data
  FUNCTION = ERR               '0 if success
  CLOSE #hFile                 'close file
  EXIT FOR
NEXT
FUNCTION = ERR                '10 attempts to open reached

END FUNCTION


#INCLUDE "sqlitening.inc"
'-------------------------------------------------------------------------------------------------
FUNCTION PBMAIN() AS LONG

LOCAL sLocalFileName,sLocalData,sData,sKey AS STRING, eCode AS LONG

'create local test file
sLocalFileName="test.txt"
OPEN sLocalFileName FOR OUTPUT AS #1:PRINT #1,"If you read this, it worked!!":CLOSE

slconnect "sqlitening.freemyip.com"   
slOpen    "Test.db3","C"                   
slexe     "create table if not exists PictureTable(blobkey unique,blobdata)"

'read local file
eCode = GetLocalFile(sLocalFileName,sLocalData)
IF eCode THEN ? "GetLocalFile error" + STR$(eCode),%MB_SYSTEMMODAL,"GetLocalFile Error"

'save data to server using key
sKey  = "Heidi Klum"                           
eCode = PutBlob(sKey,sLocalData)
IF eCode THEN ? slGetError,%MB_SYSTEMMODAL,"WriteBlob"           :EXIT FUNCTION

'read data from server
eCode = GetBlob(sKey,sData)
IF eCode THEN ? slGetError,%MB_SYSTEMMODAL,"Getblob"             :EXIT FUNCTION
? sData,%MB_SYSTEMMODAL,"GetBlob"

slDisconnect

END FUNCTION
'-------------------------------------------------------------------------------------------------
FUNCTION PutBlob(sBlobKey AS STRING,sBlob AS STRING) THREADSAFE AS LONG

LOCAL NumberOfChanges AS LONG 'function returns 0 on success

NumberOfChanges = slGetChangeCount("T")
slexeBind "replace into PictureTable values('" + sBlobKey + "',?)",slBuildBindDat(sBlob)
IF slGetErrorNumber THEN
   FUNCTION = slGetErrorNumber
   EXIT FUNCTION
END IF
NumberOfChanges = slGetChangeCount("T") - NumberOfChanges
IF NumberOfChanges <> 1 THEN FUNCTION = -9999

END FUNCTION
'-------------------------------------------------------------------------------------------------
FUNCTION GetBlob(sBlobKey AS STRING,sBlobData AS STRING) THREADSAFE AS LONG

sBlobData = ""   'function returns 0 on success
slSel "select blobdata from PictureTable where blobkey ='"+sBlobKey + "'"
IF slGetErrorNumber THEN
  FUNCTION = slGetErrorNumber
  EXIT FUNCTION
END IF
IF slGetRow THEN sBlobData = slf(1)

END FUNCTION
'-------------------------------------------------------------------------------------------------
FUNCTION GetLocalFile(sFileName AS STRING, sData AS STRING) THREADSAFE AS LONG

sData = "" 'function returns 0 on success
LOCAL MaxRetry AS LONG
LOCAL hFile AS LONG

IF ISFALSE(ISFILE(sFileName)) THEN 'local file not found
  FUNCTION = 53                'set error 53, file not found
  EXIT FUNCTION                'exit function
END IF

hFile = FREEFILE              'file handle
FOR MaxRetry = 1 TO 10        'maximum 10 attempts to open
  ERRCLEAR                     'clear error
  OPEN sFileName FOR BINARY ACCESS READ LOCK WRITE AS #hFile 'block writers
  IF ERR THEN                  'open error
   SLEEP 500                   'wait
   ITERATE                     'retry open
  END IF
  GET$ #hFile,LOF(hFile),sData 'read data
  FUNCTION = ERR               '0 if success
  CLOSE #hFile                 'close file
  EXIT FOR
NEXT
FUNCTION = ERR                '10 attempts to open reached

END FUNCTION

FUNCTION PutLocalFile(sFileName AS STRING, sData AS STRING) THREADSAFE AS LONG

LOCAL MaxRetry AS LONG
LOCAL hFile AS LONG

hFile = FREEFILE              'file handle
FOR MaxRetry = 1 TO 10        'maximum 10 attempts to open
  ERRCLEAR                     'clear error
  OPEN sFileName FOR OUTPUT AS #hFile 'exclusive
  IF ERR THEN                  'open error
   SLEEP 500                   'wait
   ITERATE                     'retry open
  END IF
  PRINT #hFile, sData;         'write data
  FUNCTION = ERR               '0 if success
  CLOSE #hFile                 'close file
  EXIT FOR
NEXT
FUNCTION = ERR                '10 attempts to open reached

END FUNCTION