/* 
**
** Program Name:  mysql-functions.i
**  Description:  maps mysql C api mapping
**           By:  Matthew Lang   matt@moredirect.com
**  This is free to use, but please let me know if you use it
**  I provide no guarantees that this code will work.  use at your own risk
**  bug fixes and suggestions?  send them to the above address
**
** Ver      Req#    Desc
** -------- ------- ----------------------------------------------------------
**      1.1         Name Placed into sccs
**      1.2         Initial writing
**      1.3         free pointer
**      1.4         allocate pointer, also create wrapper query function
                    to log updates
                    add function to return column names
                    add more generic functions
**      1.5         log function broke when use sql "replace into".
**      1.6         ability to turn off logging
**      1.7         fix pointer to mysql_row for select
**      1.8         when logging to update_log, don't log to binary log
**      1.9         handle null fields on selects
**      1.10        don't log queries
*/


/********************** public functions *************************/

define variable mysql-row    as character extent 200.
define variable db_log_query as logical  no-undo init no.
 
function db_close returns integer (input mysql as memptr):

    run mysql_close(mysql).
    set-size(mysql) = 0.
    return 1.
end.

function db_connect returns integer (input-output mysql as memptr):

    define variable mysql_conn as memptr. 
    define variable mysql-host as character no-undo format "x(12)".
    define variable mysql-db   as character no-undo.
    define variable mysql-port as integer   no-undo.
    define variable mysql-user as character no-undo.
    define variable mysql-pass as character no-undo.

    set-size(mysql) = 952.
    set-size(mysql_conn) = 952. 
    
     assign mysql-host = os-getenv("MYSQLHOST")
            mysql-db   = os-getenv("MYSQLDB")
            mysql-port = integer(os-getenv("MYSQLPORT"))
            mysql-user = os-getenv("MYSQLUSER")
            mysql-pass = os-getenv("MYSQLPASS").

    run mysql_init(0,output mysql).

    if mysql-port = ? then
        mysql-port = 0.
    if mysql-user = ? then
        mysql-user = "root".
    if mysql-pass = ? then
        mysql-pass = "".

    run mysql_real_connect(input-output mysql,mysql-host,mysql-user,
                      mysql-pass,mysql-db,mysql-port,"",0,output mysql_conn).
                     
    set-size(mysql_conn) = 0.
    return 1.
end.

function db_last_error returns character (input mysql as memptr).
    define variable err-msg as character no-undo.
    define variable mysql_err as memptr no-undo.
    
    set-size(mysql_err) = 1000.
    run mysql_error(mysql,output mysql_err).
    err-msg = replace(get-string(mysql_err,1),"'","\\'").
    set-size(mysql_err) = 0.
    return err-msg.
end.                        


function db_fetch_into_array returns integer (input mysql_res as memptr):

    define variable num-fields as integer.
    define variable i          as integer.
    define variable mysql_row  as memptr.
    define variable mysql_element as memptr.
    define variable row_lengths   as memptr.
    define variable stat as integer.
    define variable field-lengths as integer no-undo extent 200.

    set-size(mysql_row) = 800.

    run mysql_num_fields(mysql_res,output num-fields).
    
    run mysql_fetch_row(mysql_res,output mysql_row).
    if get-pointer-value(mysql_row) = 0 then do:
        stat = -1.
        return stat.
    end.
    
    run mysql_fetch_lengths(mysql_res,output row_lengths).
    
    do i = 1 to num-fields:
        field-lengths[i] = get-long(row_lengths,i * 4 - 3).
    end.

    set-size(mysql_element) = 2000.

    do i = 1 to num-fields:
        set-pointer-value(mysql_element) = get-long(mysql_row, i * 4 - 3).
        if field-lengths[i] > 0 then
            mysql-row[i] = get-string(mysql_element,1,field-lengths[i]).
        else
            mysql-row[i] = "".
    end.
    set-size(mysql_element) = 0.
    set-size(mysql_row) = 0.
    set-size(row_lengths) = 0.

    return 1.
end.

function db_free_result returns integer (mysql_res as memptr):

    run mysql_free_result(mysql_res).
    set-size(mysql_res) = 0.
    return 1.
end.

function db_query returns integer(mysql as memptr,sql-query as character):

    define variable stat as integer.

    run mysql_query(mysql,sql-query,output stat).
    if sql-query begins "select" or db_log_query = no then
        return stat.

    define variable sql        as character no-undo.
    define variable log-sql    as character no-undo.
    define variable query-stat as integer   no-undo.
    define variable cur-table  as character no-undo.
    define variable err-msg    as character no-undo.
    define variable mysql_err  as memptr    no-undo.
    define variable mysql2     as memptr    no-undo.

    if sql-query begins "insert" or sql-query begins "delete" or
       sql-query begins "replace" then
        cur-table = entry(3,sql-query," ").
    else 
        cur-table = entry(2,sql-query," ").
    
    assign cur-table = entry(1,cur-table,"(")
           log-sql = replace(sql-query,"\\","") 
           log-sql   = replace(replace(log-sql,"'","\\'"),'"','\\"').
           
    if stat <> 0 then do:
        set-size(mysql_err) = 1000.
        run mysql_error(mysql,output mysql_err).
        err-msg = replace(get-string(mysql_err,1),"'","\\'").
        set-size(mysql_err) = 0.
    end.
                 
    sql = "SET SQL_LOG_BIN=0".
    db_connect(mysql2).
    run mysql_query(mysql2,sql,output query-stat).
    
    sql = "insert into utility.update_log" +
                "(user_id,sql,error,program_name,table_name) values('" +
                userid(ldbname(1)) + "','" +  /* will have to fix this */
                log-sql    + "','" +
                err-msg       + "','" +
                program-name(1)    + "','" +
                cur-table          + "')".

    run mysql_query(mysql2,sql,output query-stat).
    db_close(mysql2).
    return stat.
end.

function db_use_result returns memptr (input mysql as memptr):

    define variable mysql_res as memptr.
    set-size(mysql_res) = 84.
    
    run mysql_use_result(mysql,output mysql_res).
    return mysql_res.

end.

/********************** end public functions *******************/

procedure mysql-fetch-into-array:
    
    define variable num-fields as integer.
    define variable i          as integer.
    define variable mysql_row  as memptr.
    define variable mysql_element as memptr.
    define variable row_lengths as memptr.
    define variable field-lengths as integer no-undo extent 200.

    define input    parameter mysql_res as memptr.
    define output   parameter stat as integer.

    set-size(mysql_row) = 800.
    set-size(row_lengths) = 1000.

    run mysql_num_fields(mysql_res,output num-fields).
    
    run mysql_fetch_row(mysql_res,output mysql_row).
    if get-pointer-value(mysql_row) = 0 then do:
        stat = -1.
        leave.
    end.

    run mysql_fetch_lengths(mysql_res,output row_lengths).

    do i = 1 to num-fields:
        field-lengths[i] = get-long(row_lengths,i * 4 - 3).
    end.
                
    set-size(mysql_element) = 2000.

    do i = 1 to num-fields:
        set-pointer-value(mysql_element) = get-long(mysql_row, i * 4 - 3).
        mysql-row[i] = get-string(mysql_element,1,field-lengths[i]).
    end.
    set-size(mysql_element) = 0.
    set-size(mysql_row) = 0.
    set-size(row_lengths) = 0.
end.

procedure mysql-fetch-fields-into-array:
    
    /* note: this function needs to be fixed, see 
            mysql-fetch-into-array to fix */
            
    define variable num-fields as integer.
    define variable i          as integer.
    define variable mysql_field  as memptr.
    define variable mysql_element as memptr.
    define variable used-length as integer.
    define variable tmp-long as integer.
    
    define input    parameter mysql_res as memptr.
    define output   parameter stat as integer.

    set-size(mysql_field) = 100.

    run mysql_num_fields(mysql_res,output num-fields).

    set-size(mysql_element) = 2000.

    used-length = 0.
    do i = 1 to num-fields:
        run mysql_fetch_field(mysql_res,output mysql_field).
        tmp-long = get-long(mysql_field,1).
        set-pointer-value(mysql_element) = tmp-long.
        mysql-row[i] = get-string(mysql_element,1).
    end.
    set-size(mysql_element) = 0.
    set-size(mysql_field) = 0.
end.


procedure mysql_fetch_field external "libmysqlclient.so" persistent:
    define input  parameter mysql_res as memptr.
    define return parameter mysql_field as memptr.
end procedure.

procedure mysql_init external "libmysqlclient.so" persistent:

    define input parameter nothing as long.
    define return parameter mysql as memptr.

end.

procedure mysql_real_connect external "libmysqlclient.so" persistent: 

    define input-output parameter mysql       as memptr.
    define input parameter host        as character.
    define input parameter mysql-user  as character.
    define input parameter passwd      as character.
    define input parameter db          as character. 
    define input parameter port        as unsigned-short.
    define input parameter socket      as character.
    define input parameter client_flag as short.
    define return parameter stuff      as memptr.
end.

procedure mysql_error external "libmysqlclient.so" persistent:

    define input parameter mysql       as memptr.
    define return parameter my-err     as memptr.
end.

procedure mysql_close external "libmysqlclient.so" persistent:

    define input parameter mysql as memptr.

end procedure.

procedure mysql_query external "libmysqlclient.so" persistent:

    define input parameter mysql as memptr.
    define input parameter sql-query as character.
    define return parameter stat as short.

end procedure.

procedure mysql_use_result external "libmysqlclient.so" persistent:

    define input parameter mysql as memptr.
    define return parameter mysql_res as memptr.

end.

procedure mysql_fetch_row external "libmysqlclient.so" persistent:

    define input parameter mysql_res as memptr.
    define return parameter mysql_row as memptr.

end .

procedure mysql_num_fields external "libmysqlclient.so" persistent:

    define input parameter mysql_res as memptr.
    define return parameter num-rows as short.
    
end.

procedure mysql_fetch_lengths external "libmysqlclient.so" persistent:

    define input parameter mysql_res as memptr.
    define return parameter row_lengths as memptr.

end.

procedure mysql_free_result external "libmysqlclient.so" persistent:

    define input parameter mysql_res as memptr.

end.