SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Write files without slGetFile and slPutFile to blobs 4 Helper functions  (Read 773 times)

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile

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!
Code: [Select]
#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


« Last Edit: March 30, 2018, 06:08:56 PM by cj »
Logged