Modify

Opened 4 years ago

Closed 19 months ago

Last modified 10 months ago

#3149 closed Feature Request (Completed)

Add new functions to SQLite UDF

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

Description

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 4 years ago by Jpm

  • Type changed from Bug to Feature Request

comment:2 Changed 4 years ago by TicketCleanup

  • Version 3.3.14.0 deleted

Automatic ticket cleanup.

comment:3 Changed 4 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.

M23

comment:4 Changed 4 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)
		_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


comment:5 Changed 4 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?

M23

comment:6 Changed 4 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 4 years ago by guinness

  • Component changed from AutoIt to Standard UDFs

comment:8 Changed 19 months 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 19 months ago by Jpm

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

Added by revision [12011] in version: 3.3.15.1

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.