#include-once

; we need that SQLite.au3 be #included _before_ this UDF
#include <SQLite.au3>

; #INDEX# =======================================================================================================================
; Title .........: SQLiteBinds
; AutoIt Version : 3.3.4.0
; Language ......: English
; Description ...: Functions that assist access to an SQLite database with parameter bindings.
; Author(s) .....: jchd
; Dll ...........: SQLite3.dll (indirectly: supplements SQLite.au3)
; ===============================================================================================================================

; ------------------------------------------------------------------------------
; This software is provided 'as-is', without any express or
; implied warranty.  In no event will the authors be held liable for any
; damages arising from the use of this software.

; #CURRENT# =====================================================================================================================
; _SQLite_Bind_Blob
; _SQLite_Bind_Numeric
; _SQLite_Bind_Null
; _SQLite_Bind_String
; _SQLite_Bind_ZeroBlob
; _SQLite_ClearBindings
; _SQLite_Step
; ===============================================================================================================================

; #INTERNAL_USE_ONLY# ===========================================================================================================
; __SQLite_ParameterIndex
; ===============================================================================================================================

#comments-start
    Changelog:
    10.07.11    Created.
#comments-end

Global Const $SQLITE_RANGE     =  25  ; /* 2nd parameter to sqlite3_bind out of range */

Global Const $SQLITE_STATIC    =   0
Global Const $SQLITE_TRANSIENT =  -1



; #INTERNAL_USE_ONLY# ===========================================================================================================
; Name...........: __SQLite_ParameterIndex
; Description ...: returns the numeric index of a named SQL parameter
; Syntax.........: __SQLite_ParameterIndex($hQuery, $sName)
; Parameters ....: $sName - Unicode String
; Return values .: On Success - parameter index (1..SQLITE_LIMIT_VARIABLE_NUMBER (having default 999)]
;                  On Failure - 0
;                  @error Value(s):    1 - unknown name
; Author ........: jchd
; ===============================================================================================================================
Func __SQLite_ParameterIndex($hQuery, $sName)
    If __SQLite_hChk($hQuery, 7, False) Then Return SetError(@error, 0, $SQLITE_MISUSE)
    Local $tName = __SQLite_StringToUtf8Struct($sName)
	Local $res = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_bind_parameter_index", "ptr", $hQuery, "ptr", DllStructGetPtr($tName))
	If $res[0] Then
		Return($res[0])
	Else
		Return(SetError(1, 0, 0))
	EndIf
EndFunc   ;==>__SQLite_ParameterIndex

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_Bind_Blob
; Description ...: Binds binary data as a BLOB parameter to a prepared statement
; Syntax.........: _SQLite_Bind_Blob($hQuery, $Param, $binData[, $iSize = 0])
; Parameters ....: $hQuery - prepared SQL statement returned by _SQLite_Query()
;                  $Param - parameter number or name
;                  $binData - binary data to be bound as BLOB (do _not_ _SQLite_Encode it!)
; Return values .: On Success - $SQLITE_OK
;                  On Failure - Error code set by SQLite
;                  @error Value(s):    1 - SQLite returned an error code (see return value)
;                  2 - $binData is not a binary type
;                  3 - sqlite3_bind_blob() call failed
; Author ........: jchd
; ===============================================================================================================================
Func _SQLite_Bind_Blob($hQuery, $Param, $binData, $iSize = 0)
    If __SQLite_hChk($hQuery, 7, False) Then Return SetError(@error, 0, $SQLITE_MISUSE)
	Local $iParam
	If IsNumber($Param) Then
		$iParam = Int($Param)
	Else
		$iParam = __SQLite_ParameterIndex($hQuery, String($Param))
	EndIf
	If $iParam <= 0 Then Return(SetError(1, 0, $SQLITE_RANGE))
	If Not IsBinary($binData) Then Return(SetError(2, 0, -1))
	If Not $iSize Or $iSize > BinaryLen($binData) Then $iSize = BinaryLen($binData)
	Local $tData = DllStructCreate("byte[" & $iSize & "]")
	DllStructSetData($tData, 1, $binData)
	Local $res = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_bind_blob", _
									"ptr", $hQuery, _
									"int", $iParam, _
									"ptr", DllStructGetPtr($tData), _
									"int", $iSize, _
									"ptr", $SQLITE_TRANSIENT)
	If @error Then Return(SetError(3, 0, -1))
	If $res[0] Then  Return(SetError(1, 0, $res[0]))
	Return($SQLITE_OK)
EndFunc   ;==>_SQLite_Bind_Blob

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_Bind_Numeric
; Description ...: Binds a numeric parameter as INT, INT64 or DOUBLE to a prepared statement
; Syntax.........: _SQLite_Bind($hQuery, $Param, $nValue)
; Parameters ....: $hQuery - prepared SQL statement returned by _SQLite_Query()
;                  $Param - parameter number or name
;                  $nValue - numeric value to be bound as INT, INT64 or DOUBLE
; Return values .: On Success - $SQLITE_OK
;                  On Failure - Error code set by SQLite
;                  @error Value(s):    1 - SQLite returned an error code (see return value)
;                  2 - sqlite3_bind_*() call failed
; Author ........: jchd
; ===============================================================================================================================
Func _SQLite_Bind_Numeric($hQuery, $Param, $nValue)
    If __SQLite_hChk($hQuery, 7, False) Then Return SetError(@error, 0, $SQLITE_MISUSE)
	Local $iParam
	If IsNumber($Param) Then
		$iParam = Int($Param)
	Else
		$iParam = __SQLite_ParameterIndex($hQuery, String($Param))
	EndIf
	If $iParam <= 0 Then Return(SetError(1, 0, $SQLITE_RANGE))
	$nValue = Number($nValue)
	Local $type
	Switch VarGetType($nValue)
		Case 'int32'
			$type = "int"
		Case 'int64'
			$type = "int64"
		Case 'double'
			$type = 'double'
	EndSwitch
	Local $res = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_bind_" & $type, _
									"ptr", $hQuery, _
									"int", $iParam, _
									$type, $nValue)
	If @error Then Return(SetError(2, 0, -1))
	If $res[0] Then  Return(SetError(1, 0, $res[0]))
	Return($SQLITE_OK)
EndFunc   ;==>_SQLite_Bind_Numeric

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_Bind_Null
; Description ...: Binds a NULL parameter to a prepared statement
; Syntax.........: _SQLite_Bind($hQuery, $Param)
; Parameters ....: $hQuery - prepared SQL statement returned by _SQLite_Query()
;                  $Param - parameter number or name
; Return values .: On Success - $SQLITE_OK
;                  On Failure - Error code set by SQLite
;                  @error Value(s):    1 - SQLite returned an error code (see return value)
;                  2 - sqlite3_bind_null() call failed
; Author ........: jchd
; ===============================================================================================================================
Func _SQLite_Bind_Null($hQuery, $Param)
    If __SQLite_hChk($hQuery, 7, False) Then Return SetError(@error, 0, $SQLITE_MISUSE)
	Local $iParam
	If IsNumber($Param) Then
		$iParam = Int($Param)
	Else
		$iParam = __SQLite_ParameterIndex($hQuery, String($Param))
	EndIf
	If $iParam <= 0 Then Return(SetError(1, 0, $SQLITE_RANGE))
	Local $res = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_bind_null", _
									"ptr", $hQuery, _
									"int", $iParam)
	If @error Then Return(SetError(2, 0, -1))
	If $res[0] Then  Return(SetError(1, 0, $res[0]))
	Return($SQLITE_OK)
EndFunc   ;==>_SQLite_Bind_Null

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_Bind_String
; Description ...: Binds a BLOB parameter to a prepared statement
; Syntax.........: _SQLite_Bind($hQuery, $Param, $sData)
; Parameters ....: $hQuery - prepared SQL statement returned by _SQLite_Query()
;                  $Param - parameter number or name
;                  $sData - string data to be bound as Text16
; Return values .: On Success - $SQLITE_OK
;                  On Failure - Error code set by SQLite
;                  @error Value(s):    1 - SQLite returned an error code (see return value)
;                  2 - $sData is not a string
;                  3 - sqlite3_bind_text16() call failed
; Author ........: jchd
; ===============================================================================================================================
Func _SQLite_Bind_String($hQuery, $Param, $sData)
    If __SQLite_hChk($hQuery, 7, False) Then Return SetError(@error, 0, $SQLITE_MISUSE)
	Local $iParam
	If IsNumber($Param) Then
		$iParam = Int($Param)
	Else
		$iParam = __SQLite_ParameterIndex($hQuery, String($Param))
	EndIf
	If $iParam <= 0 Then Return(SetError(1, 0, $SQLITE_RANGE))
	If Not IsString($sData) Then Return(SetError(2, 0, -1))
	Local $res = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_bind_text16", _
									"ptr", $hQuery, _
									"int", $iParam, _
									"wstr", $sData, _
									"int", -1, _
									"ptr", $SQLITE_TRANSIENT)
	If @error Then Return(SetError(3, 0, -1))
	If $res[0] Then  Return(SetError(1, 0, $res[0]))
	Return($SQLITE_OK)
EndFunc   ;==>_SQLite_Bind_String

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_Bind_ZeroBlob
; Description ...: Binds a zero-filled BLOB parameter to a prepared statement
; Syntax.........: _SQLite_Bind($hQuery, $Param, $iSize)
; Parameters ....: $hQuery - prepared SQL statement returned by _SQLite_Query()
;                  $Param - parameter number or name
;                  $iSize - requested size of the Blob
; Return values .: On Success - $SQLITE_OK
;                  On Failure - Error code set by SQLite
;                  @error Value(s):    1 - SQLite returned an error code (see return value)
;                  2 - sqlite3_bind_zeroblob() call failed
; Author ........: jchd
; ===============================================================================================================================
Func _SQLite_Bind_ZeroBlob($hQuery, $Param, $iSize)
    If __SQLite_hChk($hQuery, 7, False) Then Return SetError(@error, 0, $SQLITE_MISUSE)
	Local $iParam
	If IsNumber($Param) Then
		$iParam = Int($Param)
	Else
		$iParam = __SQLite_ParameterIndex($hQuery, String($Param))
	EndIf
	If $iParam <= 0 Then Return(SetError(1, 0, $SQLITE_RANGE))
	Local $res = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_bind_zeroblob", _
									"ptr", $hQuery, _
									"int", $iParam, _
									"int", $iSize)
	If @error Then Return(SetError(2, 0, -1))
	If $res[0] Then  Return(SetError(1, 0, $res[0]))
	Return($SQLITE_OK)
EndFunc   ;==>_SQLite_Bind_ZeroBlob

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_ClearBindings
; Description ...: Clears all binding to a prepared statement
; Syntax.........: _SQLite_ClearBindings($hQuery)
; Parameters ....: $hQuery - prepared SQL statement returned by _SQLite_Query()
; Return values .: On Success - $SQLITE_OK
;                  On Failure - Error code set by SQLite
;                  @error Value(s):    1 - SQLite returned an error code (see return value)
;                  2 - sqlite3_clear_bindings() call failed
; Author ........: jchd
; ===============================================================================================================================
Func _SQLite_ClearBindings($hQuery)
    If __SQLite_hChk($hQuery, 7, False) Then Return SetError(@error, 0, $SQLITE_MISUSE)
	Local $res = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_clear_bindings", _
									"ptr", $hQuery)
	If @error Then Return(SetError(2, 0, -1))
	If $res[0] Then  Return(SetError(1, 0, $res[0]))
	Return($SQLITE_OK)
EndFunc  ;==>_SQLite_ClearBindings

; #FUNCTION# ====================================================================================================================
; Name...........: _SQLite_Step
; Description ...: Performs one evaluation of a prepared statement
; Syntax.........: _SQLite_Step($hQuery)
; Parameters ....: $hQuery - prepared SQL statement returned by _SQLite_Query()
; Return values .: On Success - $SQLITE_DONE
;                  On Failure - Error code set by SQLite
;                  @error Value(s):    1 - SQLite returned an error code (see return value)
;                  2 - sqlite3_step() call failed
; Author ........: jchd
; ===============================================================================================================================
Func _SQLite_Step($hQuery)
    If __SQLite_hChk($hQuery, 7, False) Then Return SetError(@error, 0, $SQLITE_MISUSE)
	Local $res = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_step", _
									"ptr", $hQuery)
	If @error Then Return(SetError(2, 0, -1))
	If $res[0] <> $SQLITE_DONE Then  Return(SetError(1, 0, $res[0]))
	Return($res[0])
EndFunc  ;==>_SQLite_Step