SBL Script 5

' Declare global variables that will contain field names, database connection and dialog translations
Global MSLSetupData(1 to 20) as String
Global MSLSetupDialog(1 to 20) as String

Function SetupMultiSearchLookupVars(Searchstr as string)

dim tempsearchstr as String

' TODO : Setup all your Custom database setting here
  MSLSetupData(1) = "DSN=verseon;UID=verseon;PWD=verseon;"   ' Connect string

'  msgbox "KfxButgethouder a : " & KfxButgethouder

  if Searchstr = "" then
    tempsearchstr = UCase(KfxButgethouder)
  else
    tempsearchstr = UCase(Searchstr)
  end if

  REM the PHRASE column should contain a value from KEY, otherwise I predict a loop

  MSLSetupData(6) = "SELECT distinct BUDGETHOUDERID, KEY FROM V_KOFAX_BUDGETHOUDER"
  ' use Searchstr as the string entered on the dialog
  MSLSetupData(7) = "WHERE (upper(PHRASE) LIKE '%" & tempsearchstr & "%') OR (BUDGETHOUDERID = '" & tempsearchstr & "') "
  MSLSetupData(8) = "ORDER BY 2"

'  msgbox "MSLSetupData(7): " & MSLSetupData(7)

' TODO : Dialog Translations, does not effect errormessages as they are generated by ODBC
  MSLSetupDialog(1) = "Het is niet mogelijk een vebinding te maken met de Oracle server, neem contact op met systeembeheer."
  MSLSetupDialog(2) = "ODBC verbindings fout"              ' Error message caption
  MSLSetupDialog(3) = "Butgethouder zoeken"                 ' Editor messages cation

' Search dialog
  MSLSetupDialog(9) = "Geen gegevens gevonden voor : "
  MSLSetupDialog(10) = "&Butgethouder Zoeken"
  MSLSetupDialog(11) = "&Zoeken"
  MSLSetupDialog(12) = "&Selecteren"
  MSLSetupDialog(13) = "&Annuleren"   

end function


' **-------------------------
' **-- Below there be dragons
' **-------------------------

Global MSLhdbc as long
Global MSLhstmt as long
Global MSLSQL as string
Global MSLerrors(1 to 3, 1 to 10) as Variant
Global MSLDialogResult As Integer
Global MSLSelection as String

' Hookfunction to connect to the Search lookup table
Function BMMultiSearchLookupLoadValidationHook() as Long

  Call SetupMultiSearchLookupVars("")

  MSLhdbc=SqlOpen(MSLSetupData(1),prompt:=2)

  if MSLhdbc <= 0 then
    SQLError destination:=MSLerrors
    'NOTE : do not show MSLSetupData(1) as it may contain a password
    MsgBox Str(MSLerrors(1,3)) & " #" & Str(MSLerrors(1,1)), 48, MSLSetupDialog(2) & " #001"
  end if   


  BMMultiSearchLookupLoadValidationHook=MSLhdbc
end function


Function BMMultiSearchLookupUnloadValidationHook() as Long
   BMMultiSearchLookupUnloadValidationHook=SqlClose(MSLhdbc)
   MSLhdbc = -1
end function


' call thif function to get 1 item
Function BMMSLGetDataFromSQL(Searchstr as string) as string
  Dim MultiSearchdata(1 To 10, 1 To 50) As Variant
  Dim SearchString as string
  dim retcode as long
  dim counter as integer
  dim innercounter as integer
  dim SQL as string
  dim tfieldlength as integer
  dim maxreturn as integer

  maxreturn = 2

  ' update search strings
  Call SetupMultiSearchLookupVars(Searchstr$)

  SQL = MSLSetupData(6) & " " & MSLSetupData(7) & " " & MSLSetupData(8)
  MSLhstmt=SqlExecQuery(MSLhdbc, SQL)
  if MSLhstmt< 0 then
'$CStrings
    SQLError destination:=MSLerrors
    MsgBox SQL & "\n\n" & Str(MSLerrors(1,3)) & " #" & Str(MSLerrors(1,1)), 48, MSLSetupDialog(2) & " #005"
'$CStrings
  end if      

  if MSLhstmt > 0 then
' TODO : if you want more fields shown, change the third argument (Change the dimention of MultiSearchdata too)
    retcode = SQLRetrieve(MSLhdbc, MultiSearchdata, 10,maxreturn, 0 )
    if retcode <= 0 then
'$CStrings
      SQLError destination:=MSLerrors
      if MSLerrors(1,1) <> 0 then    ' retcode returns an error when there is no data, only special errors are shown
        MsgBox  SQL &"\n\n" & Str(MSLerrors(1,3)) & " #" & Str(MSLerrors(1,1)), 48, MSLSetupDialog(2) & " #006"
      end if
      retcode = 0
'$CStrings     
    end if      
  else
    retcode = 0
  end if

  if retcode > 0 then  ' did we find anything
     SearchString = MultiSearchdata(1,1)
  else 'we did not find anything, inform user
    Redim SearchListContent(0)
      MsgBox MSLSetupDialog(9) & Searchstr$,64, MSLSetupData(3) & " #007"
  end if
  BMMSLGetDataFromSQL = SearchString 
end Function