Modify

#3811 closed Feature Request (Completed)

_SQLite_FetchData should correctly read column data according to its type

Reported by: matwachich@… Owned by: Jpm
Milestone: 3.3.15.4 Component: Standard UDFs
Version: Severity: None
Keywords: Cc:

Description

The main problem here is that _SQLite_FetchData is reading integer types as strings.

Here is an example code showing the problem (even when isDev column is set to 0, when read it becomes "0" and is interpreted as TRUE), and the solution (a corrected version of the function):

#NoTrayIcon
#include <Crypt.au3>
#include <SQLite.au3>

_SQLite_Startup()
_SQLite_Open()

_SQLite_Exec(-1, 'CREATE TABLE test (name TEXT, age INT, rdnFlt FLOAT, isDev BOOL, md5 BLOB);')
_SQLite_Exec(-1, 'INSERT INTO test VALUES ("person 01", 30, 0.5, 0, ' & _SQLite_Encode(_Crypt_HashData("person 01", $CALG_MD5)) & '), ("person 02", 35, 1.5, 1, ' & _SQLite_Encode(_Crypt_HashData("person 02", $CALG_MD5)) & ');')

Dim $aResult, $iRows, $iCols
_SQLite_GetTable2d(-1, 'SELECT * FROM test', $aResult, $iRows, $iCols)

For $i = 1 To $iRows
	ConsoleWrite("Row " & $i & ": ")
	For $j = 0 To $iCols - 1
		ConsoleWrite($aResult[$i][$j] & " [" & VarGetType($aResult[$i][$j]) & "], ")
	Next
	ConsoleWrite("||| isDev: " & ($aResult[$i][3] ? "Yes" : "No") & @CRLF)
Next

; =============================
; now with a corrected function

ConsoleWrite(@CRLF & ">>> With the corrected function:" & @CRLF)

Dim $hQuery, $aRow, $i = 1
_SQLite_Query(-1, 'SELECT * FROM test', $hQuery)
While __mySqlite_FetchData($hQuery, $aRow) = $SQLITE_OK
	ConsoleWrite("Row " & $i & ": ")
	For $j = 0 To UBound($aRow) - 1
		ConsoleWrite($aRow[$j] & " [" & VarGetType($aRow[$j]) & "], ")
	Next
	ConsoleWrite("||| isDev: " & ($aRow[3] ? "Yes" : "No") & @CRLF)
	$i += 1
WEnd

; ==============================================================================

Func __mySqlite_FetchData($hQuery, ByRef $aRow, $bDoNotFinalize = False)
	; step to the next row
	Local $aRet = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_step", "ptr", $hQuery)
	If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error

	; check if we reached the end of the query
	If $aRet[0] <> $SQLITE_ROW Then
		If Not $bDoNotFinalize And $aRet[0] = $SQLITE_DONE Then _SQLite_QueryFinalize($hQuery)
		Return SetError(-1, 0, $aRet[0])
	EndIf

	; count columns
	Local $iColumns = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_data_count", "ptr", $hQuery)
	If @error Then Return SetError(2, @error, $SQLITE_MISUSE) ; DllCall error
	If $iColumns[0] <= 0 Then Return SetError(-1, 0, $SQLITE_DONE)
	$iColumns = $iColumns[0]

	; read data according to column type
	Dim $aRow[$iColumns]
	Local $pBlob, $iBlobSize
	For $i = 0 To $iColumns - 1
		$aRet = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_type", "ptr", $hQuery, "int", $i)
		If @error Then Return SetError(4, @error, $SQLITE_MISUSE) ; DllCall error

		Switch $aRet[0]
			Case $SQLITE_TYPE_NULL
				$aRow[$i] = Null
			Case $SQLITE_TYPE_INTEGER
				$aRet = DllCall($__g_hDll_SQLite, "int64:cdecl", "sqlite3_column_int64", "ptr", $hQuery, "int", $i)
				If @error Then Return SetError(8, 0, $SQLITE_MISUSE) ; DllCall error
				$aRow[$i] = $aRet[0]
			Case $SQLITE_TYPE_FLOAT
				$aRet = DllCall($__g_hDll_SQLite, "double:cdecl", "sqlite3_column_double", "ptr", $hQuery, "int", $i)
				If @error Then Return SetError(9, 0, $SQLITE_MISUSE) ; DllCall error
				$aRow[$i] = $aRet[0]
			Case $SQLITE_TYPE_BLOB
				$pBlob = DllCall($__g_hDll_SQLite, "ptr:cdecl", "sqlite3_column_blob", "ptr", $hQuery, "int", $i)
				If @error Then Return SetError(6, @error, $SQLITE_MISUSE) ; DllCall error
				$iBlobSize = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_bytes", "ptr", $hQuery, "int", $i)
				If @error Then Return SetError(5, @error, $SQLITE_MISUSE) ; DllCall error
				$aRow[$i] = Binary(DllStructGetData(DllStructCreate("byte[" & $iBlobSize[0] & "]", $pBlob[0]), 1))
			Case $SQLITE_TYPE_TEXT
				$aRet = DllCall($__g_hDll_SQLite, "wstr:cdecl", "sqlite3_column_text16", "ptr", $hQuery, "int", $i)
				If @error Then Return SetError(3, @error, $SQLITE_MISUSE) ; DllCall error
				$aRow[$i] = $aRet[0]
		EndSwitch
	Next

	; done
	Return $SQLITE_OK
EndFunc

Here is console output:

Row 1: person 01 [String], 30 [String], 0.5 [String], 0 [String], 0x8030B779B60EF1407533A8947D0EE209 [Binary], ||| isDev: Yes
Row 2: person 02 [String], 35 [String], 1.5 [String], 1 [String], 0x87AEFAE13874D477BA336DE19B4FF16E [Binary], ||| isDev: Yes

>>> With the corrected function:
Row 1: person 01 [String], 30 [Int64], 0.5 [Double], 0 [Int64], 0x8030B779B60EF1407533A8947D0EE209 [Binary], ||| isDev: No
Row 2: person 02 [String], 35 [Int64], 1.5 [Double], 1 [Int64], 0x87AEFAE13874D477BA336DE19B4FF16E [Binary], ||| isDev: Yes

Attachments (0)

Change History (5)

comment:1 Changed 15 months ago by Jpm

Hi,
I don't thing it is a bug but more an improvement as a lot of script can be in error if we change as you suggest.
For the time being I am trying to add a new parameter to _SQLite_GetTable2D and _SQLite_FetchData so that the decision to do it or not will be a scrippt decision.
I will investigate if I can do it a the query or open time. it looks nmore difficult to implement.

another solution can be a startup easy to implement for the global script

comment:2 Changed 15 months ago by Jpm

  • Type changed from Bug to Feature Request

comment:3 Changed 15 months ago by Jpm

In fact I will use the parameter in _SQLite_Startup to force the whole script and If not possible the scripter will use a new parameter in _SQLite_FetchData() in the suitable places

comment:4 Changed 15 months ago by TicketCleanup

  • Version 3.3.14.5 deleted

Automatic ticket cleanup.

comment:5 Changed 15 months ago by Jpm

  • Milestone set to 3.3.15.4
  • Owner set to Jpm
  • Resolution set to Completed
  • Status changed from new to closed

Added by revision [12497] in version: 3.3.15.4

Guidelines for posting comments:

  • You cannot re-open a ticket but you may still leave a comment if you have additional information to add.
  • In-depth discussions should take place on the forum.

For more information see the full version of the ticket guidelines here.

Add Comment

Modify Ticket

Action
as closed The owner will remain Jpm.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.