Using SQL in a program

To use an SQL statement in a program it must first be compiled or prepared using KI_PREPARE. This produces an internal datastructure called a query plan which tells KCML how to perform the request but does not actually do the request. That requires a later KI_EXECUTE. The prepare phase can be expensive as KCML will deploy a number of optimization techniques to determine the most efficient way to perform the query. By using parameters to replace constants a query plan can be reused with different values without recompilation. Parameters are represented by the ? token, are positional and are counted from left to right. e.g.

SELECT * FROM TESTTAB WHERE COST=? OR COLOR=?

Before issuing the KI_EXECUTE each parameter needs to be defined and mapped to a KCML variable using KI_BIND_PARAM.

KI_EXECUTE for a SELECT statement produces a result set which may contain zero or more rows. These can be returned to the program in order using a succession of KI_FETCH calls to retrieve each row in turn either into a row buffer or into KCML variables bound to columns with KI_BIND_COL. To discover how KCML will layout the buffer use KI_DESCRIBE_COL to find the details of each column after the statement has been prepared.

Here is a sample program which allows the user to type some SQL and display the results in a grid.

REM This example uses data awareness to populate the result set grid.
DIM sqlstr$512, buf$1024, conn=0, status, database$24, handle
DIM MAXROWS=12
REM open initial database
IF ('OpenDatabase(BYREF conn, BYREF database$, BYREF handle))
    REM database selected so let user exec SQL
    WHILE TRUE DO
        IF (Form1.Open() == FALSE)
            BREAK
        END IF
    WEND
    CALL KI_FREE_HANDLE handle TO status
    CALL KI_DISCONNECT conn TO status
ELSE
    REM no database selected
END IF
CALL KI_FREE_CONNECT conn TO status
END
DEFFORM Form1()
{.form,.form$,.Style=0x50c000c4,.Width=419,.Height=237,.Text$="SQL Demo",.Id=1024,.Menu=.mnuControl1},{.editControl1,.kcmldbedit$,.Style=0x50810080,.Left=40,.Top=11,.Width=336,.Height=17,.Id=1000,.EditGroup=.grpInput,.Label$="SQL"},{.gridControl1,.KCMLgrid$,.Style=0x50010030,.Left=41,.Top=83,.Width=359,.Height=140,.Id=1002,.Rows=2,.Cols=2,.ColWidth=108,.FixedRows=1,.DataSource=rowbuf$},{.txtControl2,.static$,.Style=0x50000000,.Left=6,.Top=84,.Width=27,.Height=18,.Text$="Output",.Id=1003},{.grpInput,.editgroup$,.Left=3,.Top=-14,.Width=407,.Height=61,.Id=1001},{.mnuControl1,.menu$,.Id=1005,.file={.Flag=16,.Text$="File"},.filedisplaytables={.Text$="Display Tables ..."},.changedatabase={.Text$="Change database"},.exit={.Flag=128,.Text$="Exit",.Picture=.exit},.Item={.Flag=128,.Text$=" "}},{.cmdButton1,.button$,.Style=0x50010001,.Left=389,.Top=12,.Width=18,.Height=14,.Text$="Go!",.Id=1006},{.Database,.status$,.Style=0x50000000,.Width=100,.Text$="Database"},{.Status,.status$,.Style=0x50000000,.Width=550}    DEFEVENT Form1.Enter()
        .Database.Text$ = database$
        IF (database$ == " ") THEN .editControl1.Enabled = FALSE
        .editControl1.Text$ = sqlstr$
    END EVENT
    DEFEVENT Form1.mnuControl1.exit.Select()
        REM Menu exit 
        .form.Terminate(0)
    END EVENT
    DEFEVENT Form1.mnuControl1.changedatabase.Select()
        IF ('OpenDatabase(BYREF conn, BYREF database$, BYREF handle))
            .Database.Text$ = database$
        END IF
        IF (database$ <> " ") THEN .editControl1.Enabled = TRUE
    END EVENT
    DEFEVENT Form1.mnuControl1.filedisplaytables.Select()
        'DoDisplayTables()
    END EVENT
    DEFEVENT Form1.cmdButton1.Click()
        REM Go button
        'sql_query(handle, .editControl1.Text$)
        .editControl1.SetFocus()
    END EVENT
FORM END

DEFSUB 'OpenDatabase(BYREF conn, BYREF database$, BYREF handle)
    LOCAL DIM h, dbrow$128
    REM prompt
    DEFFORM ChangeDB()
{.form,.form$,.Style=0x50c000c4,.Width=216,.Height=94,.Text$="Change Database",.Id=1024},{.editControl1,.kcmldbedit$,.Style=0x50810082,.Left=19,.Top=18,.Width=107,.Height=14,.Id=1000},{.ok,.button$,.Style=0x50010001,.Left=160,.Top=6,.Width=50,.Height=14,.Text$="OK",.__Anchor=5,.Id=1},{.cancel,.button$,.Style=0x50010000,.Left=160,.Top=23,.Width=50,.Height=14,.Text$="Cancel",.__Anchor=5,.Id=2}        DEFEVENT ChangeDB.Enter()
            .editControl1.Text$ = database$
        END EVENT
        DEFEVENT ChangeDB.editControl1.DropDown()
            REM fill dropdown with available databases using default connection
            .editControl1.ValidateSelChange = TRUE
            CALL KI_ALLOC_HANDLE -1, 1 TO h, status
            IF (status == 0)
                ..Delete()
                CALL KI_DATABASES h TO status
                WHILE (status == 0) DO
                    CALL KI_FETCH h, SYM(dbrow$) TO status
                    IF (status == 0)
                        REM tab separates name from description
                        i = POS(dbrow$ == HEX(09))
                        ..Add((i ? STR(dbrow$,, i - 1) : dbrow$))
                    END IF
                WEND
                CALL KI_CLOSE h TO status
                CALL KI_FREE_HANDLE h TO status
                .editControl1.DropDownFilled = TRUE
            END IF
        END EVENT
        DEFEVENT ChangeDB.ok.Click()
            database$ = .editControl1.Text$
        END EVENT
    FORM END
    REM quit if no selection
    IF (NOT ChangeDB.Open() OR database$ == " ")
        REM cancelled or undefined
        RETURN FALSE
    END IF
    IF (conn)
        REM already connected, drop any handle
        CALL KI_FREE_HANDLE handle TO status
        REM and disconnect
        CALL KI_DISCONNECT conn TO status
        IF (status)
            'error("disconnecting connection", status)
            RETURN FALSE
        END IF
    END IF
    REM open a new connection
    CALL KI_ALLOC_CONNECT 0 TO conn, status
    IF (status)
        'error("opening connection handle", status)
        RETURN FALSE
    END IF
    REM connect to new database
    CALL KI_CONNECT "KDB", conn, database$, "", "" TO status
    IF (status)
        'error("opening database", status)
        RETURN FALSE
    END IF
    REM open a new rowset handle
    CALL KI_ALLOC_HANDLE 2, conn TO handle, status
    IF (status)
        'error("allocating handle", status)
        RETURN FALSE
    END IF
    RETURN TRUE
END SUB

DEFSUB 'sql_query(handle, sqlstr$)
    LOCAL DIM status, colcount, i, r, n, name$40, sqltype$, packfmt$8, offset, buf$100, rowbuf$
    LOCAL DIM reclen, rowcount, .a$, len
    REM Prepare statement and find number of columns in result set
    CALL KI_PREPARE handle, sqlstr$ TO status, colcount
    IF (status)
        CALL KI_ERROR_TEXT handle TO buf$
        'error(buf$, status)
        CALL KI_CLOSE handle
        RETURN
    END IF
    REM Find the column names
    IF (colcount == 0)
        .Status.Text$ = "No columns in result set"
    ELSE
        REM set number of grid columns
        .gridControl1.Cols = colcount
        .gridControl1.Rows = MAXROWS
        REM set column width
        FOR i = 1 TO colcount
            REM move to grid column i
            .gridControl1.MoveCell(0, i)
            REM get schema for rowset column i
            CALL KI_DESCRIBE_COL handle, i TO name$, sqltype$, packfmt$, offset, status
            REM set column heading
            .gridControl1.Cell.Heading$ = name$
            REM make data aware
            .gridControl1.Cell.SetDataField(offset, packfmt$)
            REM default width to header text width
            .gridControl1.Cell.ColSize = &.Header
            REM alignment and width
            SELECT CASE STR(sqltype$,, 3)
            CASE "CHA"
                .gridControl1.Cell.Alignment = &.Left
                .gridControl1.Cell.ColSize = &.Default
                .a$ = (1, packfmt$)
                len = LEN(.a$)
                IF (len > LEN(name$))
                    .gridControl1.Cell.ColWidth = LEN(.a$) * 4
                END IF
            CASE "BIT"
                .gridControl1.Cell.Alignment = &.Center
            CASE "INT"
                .gridControl1.Cell.Alignment = &.Right
            CASE "DAT"
                .gridControl1.Cell.Alignment = &.Center
            CASE "TIM"
                .gridControl1.Cell.Alignment = &.Center
            CASE "NUM"
                .gridControl1.Cell.Alignment = &.Right
            END SELECT

        NEXT i
    END IF
    REM execute the query and get a result set
    CALL KI_EXECUTE handle TO status, rowcount, reclen
    IF (status <> 0)
        REM no results found        
        .Status.Text$ = "No results found"
    ELSE
        .Status.Text$ = "SQL query succeeded"
        REM setup suitable row buffer
        MAT REDIM rowbuf$reclen
        status = 0
        r = 2
        REM fill the grid with the rows
        WHILE status == 0 DO
            CALL KI_FETCH handle, SYM(rowbuf$) TO status
            IF (status)
                CALL KI_ERROR_TEXT handle TO buf$
                'error(buf$, status)
                BREAK
            END IF
            REM move to new grid row and fill it in
            .gridControl1.MoveCell(r, 1)
            .gridControl1.DataAwareRow()
            IF (++r >= MAXROWS - 2)
                status = 1
            END IF
        WEND
    END IF
    CALL KI_CLOSE handle
    RETURN
END SUB

DEFFORM DisplayTables()
{.form,.form$,.Style=0x50c000c4,.Width=317,.Height=116,.Text$="Tables",.Id=1024},{.ok,.button$,.Style=0x50010001,.Left=261,.Top=6,.Width=50,.Height=14,.Text$="OK",.__Anchor=5,.Id=1},{.cancel,.button$,.Style=0x50010000,.Left=261,.Top=23,.Width=50,.Height=14,.Text$="Cancel",.__Anchor=5,.Id=2},{.editControl1,.kcmldbedit$,.Style=0x50810082,.Left=34,.Top=21,.Width=171,.Height=17,.Id=1000}    DEFEVENT DisplayTables.editControl1.DropDown()
        LOCAL DIM h, status, tblrow$100
        REM fill grid with available tables using current connection
        CALL KI_ALLOC_HANDLE -1, conn TO h, status
        IF (status == 0)
            ..Delete()
            CALL KI_TABLES h TO status
            WHILE (status == 0) DO
                CALL KI_FETCH h, SYM(tblrow$) TO status
                IF (status == 0)
                    ..Add(tblrow$)
                END IF
            WEND
            CALL KI_CLOSE h TO status
            CALL KI_FREE_HANDLE h TO status
            ..DropDownFilled = TRUE
        END IF
    END EVENT
    DEFEVENT DisplayTables.editControl1.Validate()
    END EVENT
FORM END DisplayTables
DEFSUB 'DoDisplayTables()
    IF (conn)
        DisplayTables.Open()
    END IF
END SUB

DEFSUB 'error(msg$, s)
    LOCAL DIM e$64, buf$500
    CALL KI_ERROR s, SYM(e$)
    buf$ = msg$ & HEX(0D0A) & e$
    'MessageBox(0, buf$, "ERROR", _MB_ICONEXCLAMATION)
END SUB

$DECLARE 'MessageBox(INT(),STR(),STR(),INT())