Sign in to follow this  
Followers 0
laffo16

SQLite _SQLite_GetTable2d Problemo

3 posts in this topic

_SQLite_Startup()
_SQLite_Open("")
_SQLite_Exec(-1, "Create Table RaceData (Name,Bodds numeric(10),Bwgt, Poo);")
_SQLite_Exec(-1, "INSERT into RaceData (Name,Bodds,Bwgt,Poo) values('string',25,1,1);")
_SQLite_Exec(-1, "INSERT into RaceData (Name,Bodds,Bwgt,Poo) values('string',4.6,2,2);")
_SQLite_Exec(-1, "INSERT into RaceData (Name,Bodds,Bwgt,Poo) values('string',2.3,3,3);")
Local $iRows, $iColumns, $temp_arr
_SQLite_GetTable2d(-1, "SELECT Name,Bodds,Bwgt,Poo From RaceData ORDER BY ROWID DESC;", $temp_arr, $iRows, $iColumns)
;$temp_arr[1][1] = Number($temp_arr[1][1])
;$temp_arr[2][1] = Number($temp_arr[2][1])
;$temp_arr[3][1] = Number($temp_arr[3][1])
_ArraySort($temp_arr, 0, 1, 0, 4, 1)
_ArrayDisplay($temp_arr, "")
MsgBox(0, "", IsNumber($temp_arr[1][1]))
Exit

I have been have differculty _arraysort() 2d arrays which have been fed out from _SQLite_GetTable2d, the problem is the values passed

specificaly the ones which the sort is upon should be int's but are actually strings. No matter how i create the table and insert the data,

i cannot have _SQLite_GetTable2d feed out the values as int's. Is this a problem with my sql or just how it is?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

@laffo16...Try this

#include <SQLite.au3>

_SQLite_Startup()
_SQLite_Open("")
_SQLite_Exec(-1, "Create temp Table RaceData (Name,Bodds numeric(10),Bwgt, Poo);")
_SQLite_Exec(-1, "INSERT into RaceData (Name,Bodds,Bwgt,Poo) values('string',25,1,1);")
_SQLite_Exec(-1, "INSERT into RaceData (Name,Bodds,Bwgt,Poo) values('string',4.6,2,2);")
_SQLite_Exec(-1, "INSERT into RaceData (Name,Bodds,Bwgt,Poo) values('string',2.3,3,3);")
Local $iRows, $iColumns, $temp_arr
_SQLite_GetTable2d(-1, "SELECT Name,Bodds,Bwgt,Poo From RaceData ORDER BY ROWID DESC;", $temp_arr, $iRows, $iColumns)

ConsoleWrite("Before..." & @CRLF)
_SQLite_Display2DResult($temp_arr)
_ArraySortClib($temp_arr, 0, True, 0, 0, 1)
ConsoleWrite(@CRLF & "After..." & @CRLF)
_SQLite_Display2DResult($temp_arr)

;#
;   _ArraySortClib() v3, by Siao
;        Sort 1D/2D array using qsort() from C runtime library (msvcrt.dll, shipped with Windows since Win98 at least)
;   Parameters:
;        $Array - the array to be sorted, ByRef
;        $iMode - sort mode, can be one of the following:
;              0 = numerical, using double precision float compare
;              1 = string compare, case insensitive (default)
;              2 = string compare, case sensitive
;        $fDescend - sort direction. True = descending, False = ascending (default)
;        $iStart - index of starting element (default 0 = $array[0])
;        $iEnd - index of ending element (default 0 = Ubound($array)-1)
;        $iColumn - index of column to sort by (default 0 = first column)
;        $iWidth - max string length of array element to compare (ignored for now)
;   Return values:
;        -1 = dll error
;        0 = sort impossible because
;              @error 0 = nothing to sort
;              @error 1 = invalid array
;              @error 2 = invalid param
;        1 = success
;#
Func _ArraySortClib(ByRef $Array, $iMode = 1, $fDescend = False, $iStart = 0, $iEnd = 0, $iColumn = 0, $iWidth = 128)
    Local $iArrayDims = UBound($Array, 0)
    If @error Or $iArrayDims > 2 Then Return SetError(1, 0, 0)
    Local $iArraySize = UBound($Array, 1), $iColumnMax = UBound($Array, 2)
    If $iArraySize < 2 Then Return 0
    If $iEnd < 1 Or $iEnd > $iArraySize - 1 Then $iEnd = $iArraySize - 1
    If ($iEnd - $iStart < 2) Then Return SetError(2, 0, 0)
    If $iArrayDims = 2 Then
        If ($iColumnMax - $iColumn < 0) Then Return SetError(2, 0, 0)
    EndIf
    If $iWidth < 1 Then Return SetError(2, 0, 0)
    $iWidth = Ceiling($iWidth / 8) * 8
    Local $sMem, $tSource, $pSource, $i, $j, $iCount, $fNumeric, $fCase, $aRet, $hMsvcrt, $sStrCmp, $tFloatCmp, $pCmp, $tIndexer
    Switch $iMode
        Case 0
            $fNumeric = True
        Case 1
            $fCase = False
        Case 2
            $fCase = True
        Case Else
            Return SetError(2, 0, 0)
    EndSwitch
   ;; initialize sorting proc
    If $fNumeric Then
        $tFloatCmp = DllStructCreate("byte[36]")
        DllStructSetData($tFloatCmp, 1, Binary("0x8B4C24048B542408DD01DC1ADFE0F6C440750D80E441740433C048C333C040C333C0C3"))
        $pCmp = DllStructGetPtr($tFloatCmp)
    Else
        $sStrCmp = "_strcmpi";case insensitive
        If $fCase Then $sStrCmp = "strcmp";case sensitive
        $aRet = DllCall('kernel32.dll', 'hwnd', 'LoadLibraryA', 'str', 'msvcrt.dll')
        $hMsvcrt = $aRet[0]
        $aRet = DllCall('kernel32.dll', 'ptr', 'GetProcAddress', 'ptr', $hMsvcrt, 'str', $sStrCmp)
        DllCall('kernel32.dll', 'hwnd', 'FreeLibrary', 'hwnd', $hMsvcrt)
        If $aRet[0] = 0 Then Return -1
        $pCmp = $aRet[0]
    EndIf
   ;; initialize memory
    $sMem = ""
    If $fNumeric Then
        For $i = 1 To $iArraySize
            $sMem &= "double;int[4];"
        Next
        $iWidth = 24
    Else
        For $i = 1 To $iArraySize
            $sMem &= "char[" & $iWidth & "];int[4];"
        Next
        $iWidth += 16
    EndIf
    $sMem = StringTrimRight($sMem, 1)
    $tSource = DllStructCreate($sMem)
   ;; fill memory
    If $iArrayDims = 1 Then
        For $i = 0 To $iArraySize - 1
            DllStructSetData($tSource, $i * 2 + 1, $Array[$i])
        Next
    Else
        For $i = 0 To $iArraySize - 1
            DllStructSetData($tSource, $i * 2 + 1, $Array[$i][$iColumn])
        Next
    EndIf
   ;; enumerate elements
    $tIndexer = DllStructCreate("byte[32]")
    DllStructSetData($tIndexer, 1, Binary("0x33C08B7424048B4C2408037424108B5C240C890603F3403BC17CF7C3"))
    DllCall('user32.dll', 'uint', 'CallWindowProc', 'ptr', DllStructGetPtr($tIndexer), 'ptr', DllStructGetPtr($tSource, 2), 'int', $iArraySize, 'int', $iWidth, 'int', 4)
    $pSource = DllStructGetPtr($tSource, $iStart * 2 + 1);;pointer to search starting element
    $iCount = $iEnd - $iStart + 1;;count of elements to search
   ;;sort
    DllCall('msvcrt.dll', 'none:cdecl', 'qsort', 'ptr', $pSource, 'int', $iCount, 'int', $iWidth, 'ptr', $pCmp)
   ;; read back the result
    Local $aTmp = $Array, $iRef
    If $iArrayDims = 1 Then; 1D
        If $fDescend Then
            For $i = $iStart To $iEnd
                $iRef = DllStructGetData($tSource, $i * 2 + 2, 2)
                $Array[$iEnd + $iStart - $i] = $aTmp[$iRef]
            Next
        Else; ascending
            For $i = $iStart To $iEnd
                $iRef = DllStructGetData($tSource, $i * 2 + 2, 2)
                $Array[$i] = $aTmp[$iRef]
            Next
        EndIf
    Else; 2D
        If $fDescend Then
            For $i = $iStart To $iEnd
                $iRef = DllStructGetData($tSource, $i * 2 + 2, 2)
                For $j = 0 To $iColumnMax - 1
                    $Array[$iEnd + $iStart - $i][$j] = $aTmp[$iRef][$j]
                Next
            Next
        Else; ascending
            For $i = $iStart To $iEnd
                $iRef = DllStructGetData($tSource, $i * 2 + 2, 2)
                For $j = 0 To $iColumnMax - 1
                    $Array[$i][$j] = $aTmp[$iRef][$j]
                Next
            Next
        EndIf
    EndIf
    Return 1
EndFunc  ;==>_ArraySortClib
Edited by DjDeep00

Share this post


Link to post
Share on other sites

thanks for your help DjDeep00, this is one kickass arraysort lol

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0