Page 1 of 1

NASM - Using SQLite (Querying data from a table)

#1 GunnerInc  Icon User is offline

  • "Hurry up and wait"
  • member icon




Reputation: 858
  • View blog
  • Posts: 2,279
  • Joined: 28-March 11

Post icon  Posted 30 April 2013 - 08:43 PM

SQLite is a fast and nifty database engine.

Quote

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.


In this tutorial, we will learn how to query single values, column names, and all table data. I will not cover SQL Syntax, there are many great books on SQLite - get one. I recommend The Definitive Guide to SQLite, I learned a great deal from it.

First thing you need, are the SQLite Binaries for your OS; grab them from the SQLite Download Page. While you are there, download the documentation.

%include    "externs.inc"
%include    "data.inc"
%include    "sqlite3.inc"

%define     NULL 0

global main  
 
SECTION .text 
main: 
    
    push    NULL                           
    push    SQLITE_OPEN_READONLY           
    push    ppDb                            
    push    szDB                           
    call    sqlite3_open_v2                 
    add     esp, 4 * 4
    test    eax, eax
    jz      .DBOpened
    call    ShowSQLErr 
    jmp     .Done

.DBOpened:    
    push    dword [stdout]
    push    szWelcome
    call    fputs
    add     esp, 4 * 2
    jmp     .GetSelection
    
.ShowMenu:
    push    dword [stdout]
    push    szMenu
    call    fputs
    add     esp, 4 * 2
    
.GetSelection:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    
    push    eax

    call    ClearInputBuffer 
    call    CharToLower
    add     esp, 4 * 1

.DoSelection:
    cmp     eax, "a"
    je      .a
    cmp     eax, "b"
    je      .b
    cmp     eax, "q"
    je      .Done
    jmp     .GetSelection
   
.a: 
    ;~ call    DisplayPhonetic
    call    DisplayPhonetic2
    jmp     .ShowMenu

.b:
    ;~ call    DisplayTable
    call    DisplayTable2
    jmp     .ShowMenu
    
.Done:
    push    dword [ppDb]
    call    sqlite3_close
    add     esp, 4 * 1
 
    call    exit

;  #########################################
;  Name       : DisplayTable
;  Arguments  : none
;  Description: Displays column names and table values using a callback
;  Returns    : Nothing
;
DisplayTable:
    push    ebp
    mov     ebp, esp
    sub     esp, 4
    
.Prompt:    
    push    dword [stdout]
    push    szEnterChar
    call    fputs
    add     esp, 4 * 2 

.GetSelection:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    push    eax
    call    ClearInputBuffer 
    pop     eax
    cmp     al, "0"
    jb      .DoAlpha
    cmp     al, "9"
    ja      .DoAlpha
      
.DoNumeric:
    push    sz0
    push    szTblNumeric
    push    szColQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 4], eax
    
    push    NULL
    push    20
    push    ShowColumns
    push    eax
    push    dword [ppDb]
    call    sqlite3_exec
    add     esp, 4 * 5

    push    dword [ebp - 4]
    call    sqlite3_free
    add     esp, 4

    push    szTblNumeric
    push    szTblQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 2   
    mov     dword [ebp - 4], eax
        
    push    NULL
    push    16
    push    ShowColumns
    push    eax
    push    dword [ppDb] 
    call    sqlite3_exec
    add     esp, 4 * 5

    push    dword [ebp - 4]
    call    sqlite3_free
    add     esp, 4    
    jmp     .Done
    
.DoAlpha:
    push    szA
    push    szTblAlpha
    push    szColQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 4], eax
    
    push    NULL
    push    20
    push    ShowColumns
    push    eax
    push    dword [ppDb]
    call    sqlite3_exec
    add     esp, 4 * 5

    push    dword [ebp - 4]
    call    sqlite3_free
    add     esp, 4

    push    szTblAlpha
    push    szTblQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 2   
    mov     dword [ebp - 4], eax
             
    push    NULL
    push    16
    push    ShowColumns
    push    eax
    push    dword [ppDb]
    call    sqlite3_exec
    add     esp, 4 * 5

    push    dword [ebp - 4]
    call    sqlite3_free
    add     esp, 4
        
.Done:
    leave
    ret

;  #########################################
;  Name       : DisplayTable2
;  Arguments  : none
;  Description: Displays column names and table values
;  Returns    : Nothing
;
DisplayTable2:
    push    ebp
    mov     ebp, esp
    sub     esp, 8
    
.Prompt:    
    push    dword [stdout]
    push    szEnterChar
    call    fputs
    add     esp, 4 * 2 

.GetSelection:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    push    eax
    call    ClearInputBuffer 
    pop     eax
    cmp     al, "0"
    jb      .DoAlpha
    cmp     al, "9"
    ja      .DoAlpha
      
.DoNumeric:
    push    szTblNumeric
    push    szTblQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 2   
    mov     dword [ebp - 4], eax
    
    jmp     .PrepareIt 
    
.DoAlpha:    
    push    szTblAlpha
    push    szTblQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 2   
    mov     dword [ebp - 4], eax

.PrepareIt:    
    push    NULL
    lea     ecx, [ebp - 8]
    push    ecx
    push    -1
    push    eax
    push    dword [ppDb]
    call    sqlite3_prepare_v2
    add     esp, 4 * 5

    push    dword [ebp - 4]
    call    sqlite3_free
    add     esp, 4

.DoColumnQuery:
    xor     esi, esi
    
.NextColumn:
    push    esi
    push    dword [ebp - 8]
    call    sqlite3_column_origin_name
    add     esp, 4 * 2
    
    push    eax 
    push    szColNames
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 3
    
    inc     esi
    cmp     esi, 2
    jne     .NextColumn

    push    dword [stdout]
    push    szLF
    call    fputs
    add     esp, 4 * 2 
    
.NextRow:    
    push    dword [ebp - 8]
    call    sqlite3_step
    add     esp, 4 * 1
    cmp     eax, SQLITE_ROW
    jne     .Cleanup

    xor     esi, esi
.NextRowValue:
    push    esi
    push    dword [ebp - 8]
    call    sqlite3_column_text
    add     esp, 4 * 2

    push    eax 
    push    szColNames
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 3
    
    inc     esi
    cmp     esi, 2
    jne     .NextRowValue
    
    push    dword [stdout]
    push    szLF
    call    fputs
    add     esp, 4 * 2 
    
    jmp     .NextRow
    
.Cleanup:
    push    dword [ebp - 8]
    call    sqlite3_finalize
    add     esp, 4 * 1
        
.Done:
    leave
    ret
            
;  #########################################
;  Name       : DisplayPhonetic
;  Arguments  : none
;  Description: Looks up entered character and returns phonetic from database using a callback
;  Returns    : Nothing
;
DisplayPhonetic:
    push    ebp
    mov     ebp, esp
    sub     esp, 8

.Prompt:    
    push    dword [stdout]
    push    szEnterChar
    call    fputs
    add     esp, 4 * 2 
 
.GetSelection:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    
    push    eax

    call    ClearInputBuffer 
    call    CharToUpper
    add     esp, 4 * 1

    lea     esi, [ebp - 4]
    mov     byte [esi], al
    mov     byte [esi + 1], 0
    
    cmp     al, "0"
    jb      .DoAlpha
    cmp     al, "9"
    ja      .DoAlpha

.DoNumeric:
    push    esi
    push    szTblNumeric
    push    szQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax
    jmp     .DoQuery
    
.DoAlpha:
    push    esi
    push    szTblAlpha
    push    szQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax

.DoQuery:
    push    NULL
    push    esi
    push    ShowQuery
    push    eax
    push    dword [ppDb]
    call    sqlite3_exec
    add     esp, 4 * 5

    push    dword [ebp - 8]
    call    sqlite3_free
    add     esp, 4

    push    dword [stdout]
    push    szAgain
    call    fputs
    add     esp, 4 * 2 

.GetSelection2:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    
    push    eax

    call    ClearInputBuffer 
    call    CharToLower
    add     esp, 4 * 1
    
    cmp     eax, "y"
    je      .Prompt
    cmp     eax, "q"
    je      main.Done
    
    leave
    ret

;  #########################################
;  Name       : DisplayPhonetic2
;  Arguments  : none
;  Description: Looks up entered character and returns phonetic from database
;  Returns    : Nothing
;
DisplayPhonetic2:
    push    ebp
    mov     ebp, esp
    sub     esp, 12

.Prompt:    
    push    dword [stdout]
    push    szEnterChar
    call    fputs
    add     esp, 4 * 2 
 
.GetSelection:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    
    push    eax

    call    ClearInputBuffer 
    call    CharToUpper
    add     esp, 4 * 1

    lea     esi, [ebp - 4]
    mov     byte [esi], al
    mov     byte [esi + 1], 0
    
    cmp     al, "0"
    jb      .DoAlpha
    cmp     al, "9"
    ja      .DoAlpha

.DoNumeric:
    push    esi
    push    szTblNumeric
    push    szQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax
    jmp     .DoQuery
    
.DoAlpha:
    push    esi
    push    szTblAlpha
    push    szQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax

.DoQuery:
    push    NULL
    lea     ecx, [ebp - 12]
    push    ecx
    push    -1
    push    eax
    push    dword [ppDb]
    call    sqlite3_prepare_v2
    add     esp, 4 * 5

    push    dword [ebp - 8]
    call    sqlite3_free
    add     esp, 4
    
    push    dword [ebp - 12]
    call    sqlite3_step
    add     esp, 4 * 1
    
    push    0
    push    dword [ebp - 12]
    call    sqlite3_column_text
    add     esp, 4 * 2

    push    eax
    push    esi
    push    szResult
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax
        
    push    dword [stdout]
    push    eax
    call    fputs
    add     esp, 4 * 2

    push    dword [ebp - 8]
    call    sqlite3_free
    add     esp, 4
    
    push    dword [ebp - 12]
    call    sqlite3_finalize
    add     esp, 4 * 1

    push    dword [stdout]
    push    szAgain
    call    fputs
    add     esp, 4 * 2 

.GetSelection2:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    
    push    eax

    call    ClearInputBuffer 
    call    CharToLower
    add     esp, 4 * 1
    
    cmp     eax, "y"
    je      .Prompt
    cmp     eax, "q"
    je      main.Done
    
    leave
    ret
    
;~ void *optional_param, int argc, char **argv, char **column
ShowQuery:
    push    ebp
    mov     ebp, esp  
    sub     esp, 4
    push    esi
    push    edi
    push    ebx   
    
    mov     edi, [ebp + 16]
    mov     edi, [edi]

    push    edi
    push    dword[ebp + 8]
    push    szResult
    call    sqlite3_mprintf    
    add     esp, 4 * 3  
    mov     dword [ebp - 4], eax
    
    push    dword [stdout]
    push    eax
    call    fputs
    add     esp, 4 * 2 
         
    push    dword [ebp - 4]
    call    sqlite3_free
    add     esp, 4

    pop     ebx
    pop     edi
    pop     esi
    mov     esp, ebp
    pop     ebp
    mov     eax, 0 
    ret
    
;~ void *optional_param, int argc, char **argv, char **column   
ShowColumns:
    push    ebp
    mov     ebp, esp  
    push    esi
    push    edi
    push    ebx
    
    xor     edi, edi 
    mov     ebx, dword [ebp + 8]
    mov     esi, [ebp + ebx]
.NextCol:      
    mov     eax, [esi+edi*4]  

    push    eax 
    push    szColNames
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 3
    
    inc     edi
    cmp     edi, dword [ebp + 12] 
    jge     .NoMore
    jmp     .NextCol
    
.NoMore:    
    push    dword [stdout]
    push    szLF
    call    fputs
    add     esp, 4 * 2
    
    pop     ebx
    pop     edi
    pop     esi
    mov     esp, ebp
    pop     ebp
    xor     eax, eax
    ret     
    
;  #########################################
;  Name       : ShowSQLErr
;  Arguments  : none
;  Description: Prints to terminal, error message string.
;  Returns    : Nothing
;    

ShowSQLErr: 
    push    dword[ppDb]
    call    sqlite3_errmsg
    add     esp, 4 * 1     
    push    eax
    
    push    dword[ppDb]
    call    sqlite3_extended_errcode
    add     esp, 4 * 1
    mov     ecx, eax
    
    push    eax 
    push    szFmtError
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 4

    ret

;  #########################################
;  Name       : CharToLower
;  Arguments  : [esp + 4] = char to convert
;  Description: Converts To lowecase by setting bit 5
;  Returns    : eax = Converted character
;
CharToLower:
    mov     eax, [esp + 4]
    cmp     eax, "A"
    jb     .Skip
    cmp     eax, "Z"
    ja      .Skip
    xor     eax, 32 
.Skip:
    ret 

;  #########################################
;  Name       : CharToUpper
;  Arguments  : [esp + 4] = char to convert
;  Description: Converts To uppercase by clearing bit 5
;  Returns    : eax = Converted character
;
CharToUpper:
    mov     eax, [esp + 4]
    cmp     eax, "a"
    jb     .Skip
    cmp     eax, "z"
    ja      .Skip
    xor     eax, 32 
.Skip:
    ret 
        
;  #########################################
;  Name       : ClearInputBuffer
;  Arguments  : none
;  Description: Clears characters from input buffer
;  Returns    : Nothing
;
ClearInputBuffer:
.Clear:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    cmp     eax, 10
    je      .Done 
    cmp     eax, -1
    je      .Done 
    jmp     .Clear
.Done:
    ret



The only error checking I have is on opening the database to show how to retrieve the error code and string. In a production app, you would have more error checking.

First thing we do is Open A New Database Connection:
int sqlite3_open_v2(const char *filename, sqlite3 **ppDb, int flags, const char *zVfs);
    push    NULL                           
    push    SQLITE_OPEN_READONLY           
    push    ppDb                            
    push    szDB                           
    call    sqlite3_open_v2                 
    add     esp, 4 * 4
    test    eax, eax
    jz      .DBOpened
    call    ShowSQLErr 
    jmp     .Done

We pass the path and filename of our database szDB, an address to store our database connection handle ppDb, and in this sample we tell the engine to open the database as read only SQLITE_OPEN_READONLY. If the database was opened successfully, the function returns SQLITE_OK - 0, otherwise it returns an error. Even if an error occurs while opening a database, a database connection handle is returned in ppDb and should be released with:
Closing A Database Connection
int sqlite3_close(sqlite3*);
    push    dword [ppDb]
    call    sqlite3_close
    add     esp, 4 * 1


Querying values
There are a few ways to query values from a database.
1. Create the object - Compile an SQL Statement with sqlite3_prepare_v2
2. Run the SQL - Evaluate An SQL Statement with sqlite3_step
3. Destroy the object once done, Destroy A Prepared Statement Object with sqlite3_finalize

Or, we can use the One-Step Query Execution Interface which is a "convenience wrapper" around the above 3 functions called sqlite3_exec

Or, we can use "Parameterized SQL".
1. Create the object - Compile an SQL Statement with sqlite3_prepare_v2
2. Bind values - Binding Values To Prepared Statements with one of the sqlite3_bind_* functions.
3. Run the SQL - Evaluate An SQL Statement with sqlite3_step
4. Reset the statement - Reset A Prepared Statement Object with sqlite3_reset, go back to #2 and repeat if needed.
5. Destroy the object once done, Destroy A Prepared Statement Object with sqlite3_finalize

I will show how to use 2 methods. Parameterized SQL will be the next tutorial.

One-Step Query Execution Interface
DisplayPhonetic:
    push    ebp
    mov     ebp, esp
    sub     esp, 8

.Prompt:    
    push    dword [stdout]
    push    szEnterChar
    call    fputs
    add     esp, 4 * 2 
 
.GetSelection:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    
    push    eax

    call    ClearInputBuffer 
    call    CharToUpper
    add     esp, 4 * 1

    lea     esi, [ebp - 4]
    mov     byte [esi], al
    mov     byte [esi + 1], 0
    
    cmp     al, "0"
    jb      .DoAlpha
    cmp     al, "9"
    ja      .DoAlpha

.DoNumeric:
    push    esi
    push    szTblNumeric
    push    szQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax
    jmp     .DoQuery
    
.DoAlpha:
    push    esi
    push    szTblAlpha
    push    szQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax

.DoQuery:
    push    NULL
    push    esi
    push    ShowQuery
    push    eax
    push    dword [ppDb]
    call    sqlite3_exec
    add     esp, 4 * 5

    push    dword [ebp - 8]
    call    sqlite3_free
    add     esp, 4


Lines 1 - 25 will get the users input, store into a temporary buffer on the stack, then 27 - 30 we check to see if input is either a number or letter so we can build our query. When the user enters a letter or number, we want to get the Phonic of that character from the table, we will use "SELECT Phonic FROM '%q' WHERE (Character = '%q');" as the statement, and use sqlite3_mprintf to replace the first '%q' with the table name, and the second '%q' with the entered character:

char *sqlite3_mprintf(const char*,...);
    push    esi
    push    szTblNumeric
    push    szQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax
    jmp     .DoQuery

The first parameter is a pointer to our query to modify. (szQuery)
The second parameter is a pointer to the table name. (szTblNumeric)
The third parameter is a pointer to the value to query. (esi)

This will return a pointer to the modified string, which we will save to free later. After this call, the pointer in eax will contain, if the user enters a 2:
SELECT Phonic FROM Numeric WHERE (Character = 2);

Now we can run the query with sqlite3_exec
int sqlite3_exec(sqlite3*, const char *sql, int (*callback)(void*,int,char**,char**), void *, char **errmsg);
    push    NULL
    push    esi
    push    ShowQuery
    push    eax
    push    dword [ppDb]
    call    sqlite3_exec
    add     esp, 4 * 5

First parameter is a handle to an open database (dword [ppDb])
Second parameter is a pointer to the SQL to be evaluated (eax), this was returned from sqlite3_mprintf.
Third parameter is the address of the callback function (ShowQuery)
Fourth parameter is a value to pass to the callback (esi), this is the character the user entered.
Fifth parameter is a pointer to a buffer to hold the error message (unused here)

Once sqlite3_exec is done, we free the pointer returned from sqlite3_mprintf with sqlite3_free
void sqlite3_free(void*);
    push    dword [ebp - 8]
    call    sqlite3_free
    add     esp, 4

ShowQuery Callback Function
void *optional_param, int argc, char **argv, char **column
ShowQuery:
    push    ebp
    mov     ebp, esp  
    sub     esp, 4
    push    esi
    push    edi
    push    ebx   
    
    mov     edi, [ebp + 16]
    mov     edi, [edi]

    push    edi
    push    dword[ebp + 8]
    push    szResult
    call    sqlite3_mprintf    
    add     esp, 4 * 3  
    mov     dword [ebp - 4], eax
    
    push    dword [stdout]
    push    eax
    call    fputs
    add     esp, 4 * 2 
         
    push    dword [ebp - 4]
    call    sqlite3_free
    add     esp, 4

    pop     ebx
    pop     edi
    pop     esi
    mov     esp, ebp
    pop     ebp
    mov     eax, 0 
    ret

    push    ebp
    mov     ebp, esp  
    sub     esp, 4
    push    esi
    push    edi
    push    ebx 

We set up a stack frame, and reserve space for a DWORD sub esp, 4, this will hold the pointer returned from sqlite3_mprintf .
This is a callback function so we must save the registers esi, edi, and ebx.
    mov     edi, [ebp + 16]
    mov     edi, [edi]

The callback gives us the value returned as a pointer to a pointer (char **argv), so we must dereference the pointer to get the pointer to the string.

If the user enters A, I want the output to be: A = Alpha, so we will use sqlite3_mprintf again.
    push    edi
    push    dword[ebp + 8]
    push    szResult
    call    sqlite3_mprintf    
    add     esp, 4 * 3  
    mov     dword [ebp - 4], eax

First parameter is the display string to modify (szResult) = "%q = %q", 10, 0
Second parameter is the pointer to the character entered, we passed to sqlite3_exec as the fourth parameter - (dword[ebp + 8]) - *optional_param,
Third parameter is the Phonic value returned from the engine (edi)

Then we print the value to the terminal:
    push    dword [stdout]
    push    eax
    call    fputs
    add     esp, 4 * 2 

and free the pointer from sqlite3_mprintf
    push    dword [ebp - 4]
    call    sqlite3_free
    add     esp, 4

We return 0 in eax from the callback to say everything is ok and continue the callback if needed, any other value returned, the callback aborts.

The above code returns a single value from the database, what if we want to display column names and the values? Well, it is all in the SQL Statement we use. We will call sqlite3_exec twice, once for the column names and the other for the values.

Setup the "Get columns" statement:
    push    sz0
    push    szTblNumeric
    push    szColQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 4], eax

First param - szColQuery = SELECT * FROM '%q' WHERE (Character = '%q');
Second param - Table name - szTblNumeric
Third param - First value in table - sz0

"Execute" it:
    push    NULL
    push    20
    push    ShowColumns
    push    eax
    push    dword [ppDb]
    call    sqlite3_exec
    add     esp, 4 * 5

First param - db handle
Second param - SQL Statement
Third param - Callback proc address
Fourth param - the offset of the column names param on the stack (char **column)
Fifth param - unused.

Setup table values statement - SELECT * FROM '%q'; where %q is the table name.
We will change the fourth param in exec from 20 to 16 (char **argv)
ShowColumns Callback
void *optional_param, int argc, char **argv, char **column
ShowColumns:
    push    ebp
    mov     ebp, esp  
    push    esi
    push    edi
    push    ebx
    
    xor     edi, edi 
    mov     ebx, dword [ebp + 8]
    mov     esi, [ebp + ebx]
.NextCol:      
    mov     eax, [esi+edi*4]  

    push    eax 
    push    szColNames
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 3
    
    inc     edi
    cmp     edi, dword [ebp + 12] 
    jge     .NoMore
    jmp     .NextCol
    
.NoMore:    
    push    dword [stdout]
    push    szLF
    call    fputs
    add     esp, 4 * 2
    
    pop     ebx
    pop     edi
    pop     esi
    mov     esp, ebp
    pop     ebp
    xor     eax, eax
    ret     

The ShowColumns Callback will be called once for each row in the table, columns count as a row.
In our database, we have two columns - Character/Numeric and Phonic, int argc = dword [ebp + 12] will contain 2.
    xor     edi, edi 

edi will be our index into the values array
    mov     ebx, dword [ebp + 8]
    mov     esi, [ebp + ebx]

First we get the value we passed to exec - *optional_param - mov ebx, dword [ebp + 8], then we get the pointer to the pointer mov esi, [ebp + ebx], we will dereference it in our loop.
.NextCol:      
    mov     eax, [esi+edi*4]  

    push    eax 
    push    szColNames
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 3
    
    inc     edi
    cmp     edi, dword [ebp + 12] 
    jge     .NoMore
    jmp     .NextCol

This loop prints the value in the first column, loops back and prints the value in the second column and exits (when edi = 2), we return 0 in eax and the db engine will call our callback for each row until done.

Querying the long way
Well, it is longer meaning you need to call the functions that sqlite3_exec calls, on your own. The upside to this is that you have more control over the data returned.

I will modify DisplayPhonetic, and DisplayTable. We will call the modified procs, DisplayPhonetic2 and DisplayTable2.

DisplayPhonetic2:
    push    ebp
    mov     ebp, esp
    sub     esp, 12

.Prompt:    
    push    dword [stdout]
    push    szEnterChar
    call    fputs
    add     esp, 4 * 2 
 
.GetSelection:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    
    push    eax

    call    ClearInputBuffer 
    call    CharToUpper
    add     esp, 4 * 1

    lea     esi, [ebp - 4]
    mov     byte [esi], al
    mov     byte [esi + 1], 0
    
    cmp     al, "0"
    jb      .DoAlpha
    cmp     al, "9"
    ja      .DoAlpha

.DoNumeric:
    push    esi
    push    szTblNumeric
    push    szQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax
    jmp     .DoQuery
    
.DoAlpha:
    push    esi
    push    szTblAlpha
    push    szQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax

.DoQuery:
    push    NULL
    lea     ecx, [ebp - 12]
    push    ecx
    push    -1
    push    eax
    push    dword [ppDb]
    call    sqlite3_prepare_v2
    add     esp, 4 * 5

    push    dword [ebp - 8]
    call    sqlite3_free
    add     esp, 4
    
    push    dword [ebp - 12]
    call    sqlite3_step
    add     esp, 4 * 1
    
    push    0
    push    dword [ebp - 12]
    call    sqlite3_column_text
    add     esp, 4 * 2

    push    eax
    push    esi
    push    szResult
    call    sqlite3_mprintf    
    add     esp, 4 * 3   
    mov     dword [ebp - 8], eax
        
    push    dword [stdout]
    push    eax
    call    fputs
    add     esp, 4 * 2

    push    dword [ebp - 8]
    call    sqlite3_free
    add     esp, 4
    
    push    dword [ebp - 12]
    call    sqlite3_finalize
    add     esp, 4 * 1

    push    dword [stdout]
    push    szAgain
    call    fputs
    add     esp, 4 * 2 

.GetSelection2:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    
    push    eax

    call    ClearInputBuffer 
    call    CharToLower
    add     esp, 4 * 1
    
    cmp     eax, "y"
    je      .Prompt
    cmp     eax, "q"
    je      main.Done
    
    leave
    ret


Up until .DoQuery, everything is the same; well except, I need space on the stack for one more variable to hold the statement handle. So we change sub esp, 8 to sub esp, 12

We will use the same SQL Statements we used earlier.

Before we execute a query, we must Compile the SQL Statement
int sqlite3_prepare_v2(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail);
    push    NULL
    lea     ecx, [ebp - 12]
    push    ecx
    push    -1
    push    eax
    push    dword [ppDb]
    call    sqlite3_prepare_v2
    add     esp, 4 * 5

First param is the handle to our database.
Second param is a pointer to the SQL Statement to process - eax (returned from sqlite3_mprintf)
Third param is the max length of the SQL Statement, we use -1 to make the engine read up to the NULL terminator. There is a small performance gain in passing the Statement length + NULL.
Fourth param is an address to store the statement handle (ebp - 12)
Fifth param is a pointer to the unused portion of the SQL Statement, unused here.

Next we tell the engine to Evaluate the SQL Statement
int sqlite3_step(sqlite3_stmt*);
    push    dword [ebp - 12]
    call    sqlite3_step
    add     esp, 4 * 1

Takes one parameter - the statement handle from sqlite3_prepare_v2

Now, we can get the Result Values From A Query
In this example, we will use:
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
    push    0
    push    dword [ebp - 12]
    call    sqlite3_column_text
    add     esp, 4 * 2

First param is the statement handle
Second param is the index of the column for which data should be returned. Leftmost column = 0

We format the output with sqlite3_mprintf, print the result with fputs and when done,
Destroy A Prepared Statement Object
int sqlite3_finalize(sqlite3_stmt *pStmt);
    push    dword [ebp - 12]
    call    sqlite3_finalize
    add     esp, 4 * 1

One parameter - statement handle.

Getting Column Names and all row values:
DisplayTable2:
    push    ebp
    mov     ebp, esp
    sub     esp, 8
    
.Prompt:    
    push    dword [stdout]
    push    szEnterChar
    call    fputs
    add     esp, 4 * 2 

.GetSelection:
    push    dword [stdin]
    call    fgetc
    add     esp, 4 * 1
    push    eax
    call    ClearInputBuffer 
    pop     eax
    cmp     al, "0"
    jb      .DoAlpha
    cmp     al, "9"
    ja      .DoAlpha
      
.DoNumeric:
    push    szTblNumeric
    push    szTblQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 2   
    mov     dword [ebp - 4], eax
    
    jmp     .PrepareIt 
    
.DoAlpha:    
    push    szTblAlpha
    push    szTblQuery
    call    sqlite3_mprintf    
    add     esp, 4 * 2   
    mov     dword [ebp - 4], eax

.PrepareIt:    
    push    NULL
    lea     ecx, [ebp - 8]
    push    ecx
    push    -1
    push    eax
    push    dword [ppDb]
    call    sqlite3_prepare_v2
    add     esp, 4 * 5

    push    dword [ebp - 4]
    call    sqlite3_free
    add     esp, 4

.DoColumnQuery:
    xor     esi, esi
    
.NextColumn:
    push    esi
    push    dword [ebp - 8]
    call    sqlite3_column_origin_name
    add     esp, 4 * 2
    
    push    eax 
    push    szColNames
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 3
    
    inc     esi
    cmp     esi, 2
    jne     .NextColumn

    push    dword [stdout]
    push    szLF
    call    fputs
    add     esp, 4 * 2 
    
.NextRow:    
    push    dword [ebp - 8]
    call    sqlite3_step
    add     esp, 4 * 1
    cmp     eax, SQLITE_ROW
    jne     .Cleanup

    xor     esi, esi
.NextRowValue:
    push    esi
    push    dword [ebp - 8]
    call    sqlite3_column_text
    add     esp, 4 * 2

    push    eax 
    push    szColNames
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 3
    
    inc     esi
    cmp     esi, 2
    jne     .NextRowValue
    
    push    dword [stdout]
    push    szLF
    call    fputs
    add     esp, 4 * 2 
    
    jmp     .NextRow
    
.Cleanup:
    push    dword [ebp - 8]
    call    sqlite3_finalize
    add     esp, 4 * 1
        
.Done:
    leave
    ret


Up until .DoColumnQuery we create the SQL Statement depending on what the user enters, and compile the statement.

Next we can query the Column Names by getting the Source Of Data In A Query Result
const char *sqlite3_column_origin_name(sqlite3_stmt*,int);
.DoColumnQuery:
    xor     esi, esi
    
.NextColumn:
    push    esi
    push    dword [ebp - 8]
    call    sqlite3_column_origin_name
    add     esp, 4 * 2
    
    push    eax 
    push    szColNames
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 3
    
    inc     esi
    cmp     esi, 2
    jne     .NextColumn

    push    dword [stdout]
    push    szLF
    call    fputs
    add     esp, 4 * 2

First param to sqlite3_column_origin_name is the statement handle
Second param is the index of the column to get the name of.

There are 2 columns in our database so we set esi to 0 on the first iteration and increment to 1 to get the second column.

Now we can get all of the row values:
.NextRow:    
    push    dword [ebp - 8]
    call    sqlite3_step
    add     esp, 4 * 1
    cmp     eax, SQLITE_ROW
    jne     .Cleanup

    xor     esi, esi
.NextRowValue:
    push    esi
    push    dword [ebp - 8]
    call    sqlite3_column_text
    add     esp, 4 * 2

    push    eax 
    push    szColNames
    push    dword [stdout]
    call    fprintf
    add     esp, 4 * 3
    
    inc     esi
    cmp     esi, 2
    jne     .NextRowValue
    
    push    dword [stdout]
    push    szLF
    call    fputs
    add     esp, 4 * 2 
    
    jmp     .NextRow
    
.Cleanup:
    push    dword [ebp - 8]
    call    sqlite3_finalize
    add     esp, 4 * 1


The outer loop .NextRow, we call sqlite3_step to get the next row while it returns SQLITE_ROW, when it returns another value, there are no more rows and we are done.

The inner loop .NextRowValue, we call sqlite3_column_text 2 times to get the values in the row, once for each column. Print the result and repeat the outer loop. Once done, we destroy the prepared statement.

To test the callback way, uncomment/comment the proper call under label .a and .b (lines 56 and 61)

Attached Image

Attached File(s)



Is This A Good Question/Topic? 1
  • +

Page 1 of 1