Add new functions to SQLite UDF

[anonymous]

Please add new functions to SQLite UDF (or modify existent):
_SQLite_GetTable2d() and _SQLite_GetTable() to

• get Only DataRows (without header) zero based array and array with count in zeroth element

[TicketCleanup]

Automatic ticket cleanup.

[Melba23]

You can create simple wrapper functions using _ArrayDelete to remove the Header row and UBound to determine the count. No need to change the existing UDF functions and certainly no requirement to add new ones.

M23

[anonymous]

I do not think _ArrayDelete() work very fast removing 0-th 1-st elements on big arrays. Also why do double work especially if it can be easily avoided?
Changes not so big and function become fast and flexible.

existent functions can get new Parameter - $iReturnArray = 0/1/2
0 - return array as is (default)
1 - return 0-based array
2 - return 1-based array with count 0th element

Func _SQLite_GetTable2d_OnlyDataRows($hDB, $sSQL, ByRef $aResult, ByRef $iRows, ByRef $iColumns)
	If __SQLite_hChk($hDB, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE)
	$aResult = 0
	Local $hQuery
	Local $r = _SQLite_Query($hDB, $sSQL, $hQuery)
	If @error Then Return SetError(2, @error, $r)
	If $r <> $SQLITE_OK Then
		__SQLite_ReportError($hDB, "_SQLite_GetTable2d", $sSQL)
		_SQLite_QueryFinalize($hQuery)
		Return SetError(-1, 0, $r)
	EndIf
	$iRows = 0
	Local $iRval_Step, $iError
	While True
		$iRval_Step = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_step", "ptr", $hQuery)
		If @error Then
			$iError = @error
			_SQLite_QueryFinalize($hQuery)
			Return SetError(3, $iError, $SQLITE_MISUSE) ; DllCall error
		EndIf
		Switch $iRval_Step[0]
			Case $SQLITE_ROW
				$iRows += 1
			Case $SQLITE_DONE
				ExitLoop
			Case Else
				_SQLite_QueryFinalize($hQuery)
				Return SetError(3, $iError, $iRval_Step[0])
		EndSwitch
	WEnd
	Local $iRet = _SQLite_QueryReset($hQuery)
	If @error Then
		$iError = @error
		_SQLite_QueryFinalize($hQuery)
		Return SetError(4, $iError, $iRet)
	EndIf
	Local $aDataRow
	$r = _SQLite_FetchNames($hQuery, $aDataRow)
	If @error Then
		$iError = @error
		_SQLite_QueryFinalize($hQuery)
		Return SetError(5, $iError, $r)
	EndIf
	$iColumns = UBound($aDataRow)
	If $iColumns <= 0 Then
		_SQLite_QueryFinalize($hQuery)
		Return SetError(-1, 0, $SQLITE_DONE)
	EndIf
	Dim $aResult[$iRows][$iColumns]
	If $iRows > 0 Then
		For $i = 0 To $iRows - 1
			$r = _SQLite_FetchData($hQuery, $aDataRow, 0, 0, $iColumns)
			If @error Then
				$iError = @error
				_SQLite_QueryFinalize($hQuery)
				Return SetError(6, $iError, $r)
			EndIf
			For $j = 0 To $iColumns - 1
				$aResult[$i][$j] = $aDataRow[$j]
			Next
		Next
	EndIf
	Return (_SQLite_QueryFinalize($hQuery))
EndFunc   ;==>_SQLite_GetTable2d

[Melba23]

That code is not the current _SQLite_GetTable2d function - and where is this new $iReturnArray parameter? Or do you expect someone else to do all the work?

M23

[jchd18]

This request isn't deviant at all. 99.99% of the time in real-world applications one only need data from queries, not headers.
I don't know why headers made their way in the *_GetTable2d function of this UDF but this is a mistake. Indeed headers in resulting array row 0 should be optional and defaulted to False. Having to run rings to place the headers there is a useless slowdown in most use cases and the ADO counterpart is severely impacted since we can't use the .GetRows method efficiently.

I personally always use a similar function called _SQLite_GetData being *_GetTable2d without the headers.

I'd like this request to remain open for now, just because it makes much more sense than what we currently have.

[J-Paul Mesnage]

As suggest by jchd18 I will add a simplified version _SQLite_GetTableData2D() just returning data in a 2D array

[J-Paul Mesnage]

Added by revision [12011] in version: 3.3.15.1