Opened 8 years ago

Closed 6 years ago

Last modified 5 years ago

#3149 closed Feature Request (Completed)

Add new functions to SQLite UDF

Reported by: anonymous Owned by: Jpm
Milestone: Component: Standard UDFs
Version: Severity: None
Keywords: Cc:


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

Attachments (0)

Change History (9)

comment:1 Changed 8 years ago by Jpm

  • Type changed from Bug to Feature Request

comment:2 Changed 8 years ago by TicketCleanup

  • Version deleted

Automatic ticket cleanup.

comment:3 Changed 8 years ago by 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.


comment:4 Changed 8 years ago by 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)
		Return SetError(-1, 0, $r)
	$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
			Return SetError(3, $iError, $SQLITE_MISUSE) ; DllCall error
		Switch $iRval_Step[0]
				$iRows += 1
			Case Else
				Return SetError(3, $iError, $iRval_Step[0])
	Local $iRet = _SQLite_QueryReset($hQuery)
	If @error Then
		$iError = @error
		Return SetError(4, $iError, $iRet)
	Local $aDataRow
	$r = _SQLite_FetchNames($hQuery, $aDataRow)
	If @error Then
		$iError = @error
		Return SetError(5, $iError, $r)
	$iColumns = UBound($aDataRow)
	If $iColumns <= 0 Then
		Return SetError(-1, 0, $SQLITE_DONE)
	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
				Return SetError(6, $iError, $r)
			For $j = 0 To $iColumns - 1
				$aResult[$i][$j] = $aDataRow[$j]
	Return (_SQLite_QueryFinalize($hQuery))
EndFunc   ;==>_SQLite_GetTable2d

comment:5 Changed 8 years ago by 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?


comment:6 Changed 8 years ago by 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.

comment:7 Changed 8 years ago by guinness

  • Component changed from AutoIt to Standard UDFs

comment:8 Changed 6 years ago by Jpm

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

comment:9 Changed 6 years ago by Jpm

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

Added by revision [12011] in version:

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

as closed The owner will remain Jpm.

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

Note: See TracTickets for help on using tickets.