• Welcome to SQLitening Support Forum.
 

sql3client.inc wrappers are not required (example)

Started by mikedoty, October 12, 2007, 03:40:33 PM

Previous topic - Next topic

mikedoty


%UseWrapper = 0
#IF %UseWrapper
  #INCLUDE "sql3client.inc"
#ENDIF
TYPE SQLITE3_MSG_TYPE DWORD FILL    'Fill used to be compatible with Visual Basic
   nAction   AS LONG   ' action to take
   nData     AS LONG   ' memory address to SQL string
   nLen      AS LONG   ' length of the SQL string
   lData     AS LONG   ' generic holder used to pass/receive numeric data to the dll
   hSession  AS LONG   ' handle for the current session
   rsClient  AS LONG   ' client side recordset
   rsServer  AS LONG   ' server side recordset (if applicable)
   rsLastRow AS LONG   ' used for determining next row to return from server side recordset (if applicable)
END TYPE
DECLARE FUNCTION sqlite3Client LIB "SQL3CLIENT.DLL"  ALIAS "SQLITE3CLIENT" (BYVAL msg AS SQLITE3_MSG_TYPE PTR) AS LONG

'Actions (see SQL3Client.inc for the rest)
%SQL3_STARTSESSION = 100: %SQL3_ENDSESSION = 110: %SQL3_USE  = 120
%SQL3_CONNECT      = 140: %SQL3_SELECT     = 160: %SQL3_DISCONNECT = 180
%SQL3_RSDESTROY    = 190: %SQL3_RSROWCOUNT = 290: %SQL3_RSCOLCOUNT   = 300
%SQL3_RSCOLNAME    = 310: %SQL3_REMOTE     = 330

FUNCTION PBMAIN AS LONG
  LOCAL msg              AS SQLITE3_MSG_TYPE
  LOCAL hSession         AS LONG
  LOCAL result           AS LONG
  LOCAL ConnectionString AS STRING
  LOCAL sDataBase        AS STRING
  LOCAL sSQL             AS STRING
  LOCAL rs               AS DWORD
  LOCAL nCacheSize       AS LONG
  nCacheSize = 1000

  #IF %UseWrapper                                                              'START SESSION
    ? "Use wrapper functions to call client"
    result = sql3_StartSession(hSession)
  #ELSE
    ? "Do not use wrapper functions to call client"
   
    msg.nAction = %SQL3_STARTSESSION
    result = sqlite3Client(VARPTR(msg))
    hSession = msg.hSession
  #ENDIF
 
  IF result = 0 THEN ? "hSession" + STR$(hSession) ELSE ? "StartSession error" + STR$(result):GOTO endProgram

  ConnectionString = "SERVER= 192.168.0.100;PORT=12069;UID=PSS234;PASSWORD=sqlrocks"
  #IF %UseWrapper                                                              'CONNECT
    result = sql3_Connect(hSession, ConnectionString)
  #ELSE
    msg.nAction  = %SQL3_CONNECT
    msg.hSession  = hSession
    msg.nData  = STRPTR(ConnectionString)
    msg.nLen = LEN(ConnectionString)
    result& = sqlite3Client(VARPTR(msg))
  #ENDIF
  IF result = 0 THEN ? "Connected" ELSE ? "Unable to connect" + STR$(result):GOTO endProgram

                                                                               
  sDataBase = "catalog.db3"
  #IF %UseWrapper                                                              'USE DATABASE
    result = sql3_Use(hSession, sDatabase,%SQL3_REMOTE)
  #ELSE
    msg.hSession = hSession
    msg.nAction = %SQL3_USE
    msg.nData = STRPTR(sDataBase)
    msg.nLen = LEN(sDatabase)
    msg.lData = %SQL3_REMOTE
    result = sqlite3Client(VARPTR(msg))
  #ENDIF
  IF result = 0 THEN ? "USE okay" ELSE ? "USE error" + STR$(result):GOTO endProgram

  sSQL$ = "SELECT * from parts limit 11;"
  #IF %UseWrapper                                                              'SELECT
    result = sql3_select(hSession,sSQL,rs,nCacheSize)
  #ELSE
    msg.nAction  = %SQL3_SELECT
    msg.hSession = hSession
    msg.nData = STRPTR(sSQL )
    msg.nLen = LEN( sSQL )
    msg.lData = nCacheSize
    result = sqlite3Client(VARPTR(msg))
    rs = msg.rsClient
  #ENDIF
  IF result = 0 THEN ? "Select okay" ELSE ? "Select error" + STR$(result): GOTO endProgram

  #IF %UseWrapper                                                              'ROWS
    result = sql3_rsRowCount(hSession,rs)
  #ELSE
    msg.nAction  = %SQL3_RSROWCOUNT
    msg.hSession = hSession
    msg.rsClient = rs
    sqlite3Client VARPTR(msg)
    result = msg.lData
  #ENDIF
  ? "(Rows) = " +  STR$(result)

  #IF %UseWrapper                                                              'COLUMNS
    result =  sql3_rsColCount(hSession,rs)
  #ELSE
    msg.nAction = %SQL3_RSCOLCOUNT
    msg.hSession = hSession
    msg.rsClient = rs
    sqlite3Client VARPTR(msg)
    result = msg.lData
  #ENDIF
 
  ? "(Cols)" +  STR$(result)

  #IF %UseWrapper                                                              'DESTROY RECORD SET
    result& = sql3_rsDestroy (hSession,rs)
  #ELSE
    msg.nAction  = %SQL3_RSDESTROY
    msg.hSession = hSession
    msg.rsClient = rs
    result = sqlite3Client(VARPTR(msg))
  #ENDIF

  #IF %UseWrapper                                                              'DISCONNECT
    result& = sql3_Disconnect(hSession)
  #ELSE
    msg.nAction  = %SQL3_DISCONNECT
    msg.hSession = hSession
    result = sqlite3Client(VARPTR(msg))
  #ENDIF
  ? "Result of sql3_Disconnect" + STR$(result)
                                                                               'END SESSION
  #IF %UseWrapper
    result& = sql3_EndSession(hSession)
  #ELSE
    msg.nAction  = %SQL3_ENDSESSION
    msg.hSession = hSession
    result = sqlite3Client( VARPTR(msg) )
  #ENDIF
  ? "Result of sql3_EndSession" + STR$(result)
  hSession = 0

EndProgram:
  #IF %DEF(%PB_CC32)
    SLEEP 2000
  #ENDIF
END FUNCTION

Paul Squires

Hi Mike,

Do you see any gain from not using the wrapper functions? I can't imagine there being any appreciable speed advantage and the extra code needed to set up and call each client function seems to be a long term code maintenance issue. What are your thoughts?



Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

mikedoty

Just using for learning. 
I'm working on one function that does it all like this:
result = sql(hSession, %SQL3_SELECT, sSql, rs)

mikedoty

#3

'Everything encapsulated/trapped in one function.
'Allows looping through all functions by action number.
'Same syntax on all calls.
'Easier to see program flow.
'No include file needed.
'No function names needed, just actions.
'Less code.
'Easier to maintain.
'Could be used to read in commands to process and perform the actions.
'One entry and exit point for all database access.
'One error trap for all database access.
'Note: Only complete up to actions called by this test program.

'Type used for each database that is opened
TYPE SQLITE3_MSG_TYPE DWORD FILL    ' Fill used to be compatible with Visual Basic
   nAction   AS LONG   ' action to take
   nData     AS LONG   ' memory address to SQL string
   nLen      AS LONG   ' length of the SQL string
   lData     AS LONG   ' generic holder used to pass/receive numeric data to the dll
   hSession  AS LONG   ' handle for the current session
   rsClient  AS LONG   ' client side recordset
   rsServer  AS LONG   ' server side recordset (if applicable)
   rsLastRow AS LONG   ' used for determining next row to return from server side recordset (if applicable)
END TYPE

DECLARE FUNCTION sqlite3Client LIB "SQL3CLIENT.DLL" ALIAS "SQLITE3CLIENT" (BYVAL msg AS SQLITE3_MSG_TYPE PTR ) AS LONG
DECLARE FUNCTION s(hSession AS LONG, Action AS LONG, sql AS STRING, rs AS DWORD) AS LONG

' Actions
%SQL3_STARTSESSION    = 100
%SQL3_ENDSESSION      = 110
%SQL3_USE             = 120
%SQL3_CLOSE           = 130
%SQL3_CONNECT         = 140
%SQL3_EXEC            = 150
%SQL3_SELECT          = 160
%SQL3_VALIDATEUSER    = 170
%SQL3_DISCONNECT      = 180
%SQL3_RSDESTROY       = 190
%SQL3_RSMOVEFIRST     = 200
%SQL3_RSMOVENEXT      = 210
%SQL3_RSMOVELAST      = 220
%SQL3_RSMOVEPREV      = 230
%SQL3_RSMOVETO        = 240
%SQL3_RSEOF           = 250
%SQL3_RSBOF           = 260
%SQL3_RSGET           = 270
%SQL3_RSGETAT         = 280
%SQL3_RSROWCOUNT      = 290
%SQL3_RSCOLCOUNT      = 300
%SQL3_RSCOLNAME       = 310
%SQL3_LOCAL           = 320
%SQL3_REMOTE          = 330
%SQL3_FIX             = 340


FUNCTION PBMAIN AS LONG
  LOCAL msg              AS SQLITE3_MSG_TYPE
  LOCAL hSession         AS LONG
  LOCAL result           AS LONG
  LOCAL sql              AS STRING
  LOCAL rs               AS DWORD
  LOCAL action           AS LONG

  result = s(hSession, %SQL3_STARTSESSION,sql, rs)
  result = s(hSession, %SQL3_CONNECT,"SERVER= 192.168.0.100;PORT=12069;UID=PSS234;PASSWORD=sqlrockss", rs)
  result = s(hSession, %SQL3_USE, "catalog.db3", rs)
  result = s(hSession, %SQL3_SELECT,"SELECT * from parts limit 11;", rs)
  result = s(hSession, %SQL3_RSROWCOUNT,sql, rs)
  ? "Rows" +  STR$(result)
  result = s(hSession, %SQL3_RSCOLCOUNT,sql, rs)
  ? "Cols" +  STR$(result)
  result = s(hSession, %SQL3_RSDESTROY,sql,rs)
  result = s(hSession, %SQL3_DISCONNECT,sql,rs)
  result = s(hSession, %SQL3_ENDSESSION, sql, rs)
  hSession = 0
 
EndProgram:
  #IF %DEF(%PB_CC32)
    SLEEP 2000
  #ENDIF
END FUNCTION

FUNCTION s(hSession AS LONG, Action AS LONG, sql AS STRING, rs AS DWORD) AS LONG
  LOCAL msg AS SQLITE3_MSG_TYPE
  LOCAL result AS LONG
  msg.nAction = Action
  msg.nData = STRPTR(sql)
  msg.nLen  = LEN(sql)
  msg.hSession = hSession

  SELECT CASE Action

    CASE %SQL3_STARTSESSION
      sqlite3Client(VARPTR(msg))
      hSession = msg.hSession
      FUNCTION = hSession

    CASE %SQL3_USE
       msg.lData = %SQL3_REMOTE                 'always use remote
       FUNCTION = sqlite3Client(VARPTR(msg))

    CASE %SQL3_SELECT
      msg.lData = 1000                          'cache size
      result = sqlite3Client(VARPTR(msg))
      rs = msg.rsClient

    CASE %SQL3_RSROWCOUNT, %SQL3_RSCOLCOUNT
      msg.rsClient = rs
      result = sqlite3Client(VARPTR(msg))
      FUNCTION = msg.lData

    CASE %SQL3_RSDESTROY
      msg.rsClient = rs
      result = sqlite3Client(VARPTR(msg))

    CASE ELSE
      result = sqlite3Client(VARPTR(msg))
  END SELECT

END FUNCTION