SQL query with headers

[Jewtus]

I've been modeling everything in my tool in SQLite (while I waited for IT to get moving on putting up an actually DB instance) and there is a major difference between the SQL queries and the SQLite queries... SQLite returns the header names. This is important to me because I have some list views where I use the header names to generate the columns. I also have all of my loops setup as base 1 instead of base 0. Here is my SQLite getrecords function:

Func GetRecords($DB,$Query)
    Local $aResult, $iRows, $iColumns
    $iRval = _SQLite_GetTable2d($DB, $Query &";", $aResult, $iRows, $iColumns)
    If $iRval = $SQLITE_OK Then
        Return $aResult
    ElseIf $iRval = 5 Then
        MsgBox(0, "Error..." & $iRval, "Temporary error. Please click OK button to continue")
    Else
        MsgBox(0, "SQLite Error: " & $iRval, _SQLite_ErrMsg($sqlDB))
    EndIf
EndFunc

and this is my SQL function

Func _GetRecords($connection,$query)
    $sqlRs = ObjCreate("ADODB.Recordset")
    ConsoleWrite($query&@CRLF)
    $sqlRs.open ($query,$connection)
        If $sqlRs.EOF = True Then
            SetError(1)
            $sqlRs.Close
        Else
            $result = $sqlRs.GetRows
            If UBound($result)>0 Then
                Return $result
                $sqlRs.Close
            Else
                SetError(1)
                $sqlRs.Close
            EndIf
        EndIf
EndFunc

Sort of taking the query and doing string splitting to get the header name from the query itself, does anyone know of a good way of adapting my code?

[Jewtus]

This seems to work, but it doesn't handle any cast, convert, etc and I'm concerned it might cause performance issues.

Func _GetRecords($connection,$query)
    $sHeaders=StringReplace($query,"Select ","")
    $sHeaders=StringLeft($sHeaders,StringInStr($sHeaders,"from")-1)
    $aHeaders=StringSplit($sHeaders,",",2)
    For $z=0 to UBound($aHeaders)-1
        $aHeaders[$z]=StringStripWS(StringStripWS($aHeaders[$z],1),2)
        If StringInStr($aHeaders[$z]," as ") <> 0 Then
            $aHeaders[$z]=StringTrimLeft($aHeaders[$z],StringInStr($aHeaders[$z]," as ")+3)
        EndIf
    Next
    _ArrayTranspose($aHeaders)
    $sqlRs = ObjCreate("ADODB.Recordset")
    ConsoleWrite($query&@CRLF)
    $sqlRs.open ($query,$connection)
        If $sqlRs.EOF = True Then
            Return $aHeaders
        Else
            $result = $sqlRs.GetRows
            If UBound($result)>0 Then
                _ArrayInsert($result,0,_ArrayToString($aHeaders))
                Return $result
                $sqlRs.Close
            Else
                SetError(1)
                $sqlRs.Close
            EndIf
        EndIf
EndFunc

 

[jchd]

You can use this UDF modeled like the SQLite UDF. _AdoSQL_GetTable2d is what you want.

AdoSQL.au3