1 (edited by ostertagi 11/13/2013 12:13:41 pm)

Topic: mysqli usage

hello yall,
i was wondering why FA keeps using mysql_connect and other old functions, the $godebug=1 keeps reminding me that these fucntions are indeed deprecated and will be no longer supported by future php release?!!
At the same time i had a look at webERP source code which actually support mysqli, mariadb and even postrgres.
Anyway, i rewrited connect_db.inc, 15 min of work, i even borrowed the DB_escape_string() (thanks guys) from webERP, and FA works just fine. well here's the code, feel free to try it or modify it and i'll be very glad if you have any suggestions/comments or improvements ...

<?php

function set_global_connection($company = -1) {
    global $db, $transaction_level, $db_connections;

    cancel_transaction(); // cancel all aborted transactions if any
    $transaction_level = 0;

    if ($company == -1)
        $company = $_SESSION["wa_current_user"]->company;

    $_SESSION["wa_current_user"]->cur_con = $company;

    $connection = $db_connections[$company];
    $db = mysqli_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"]);
    mysqli_select_db($db, $connection["dbname"]);
    return $db;
}

$db_duplicate_error_code = 1062;

//DB wrapper functions to change only once for whole application

function db_query($sql, $err_msg = null) {
    global $db, $show_sql, $sql_trail, $select_trail, $go_debug, $sql_queries, $Ajax,
    $db_connections, $db_last_inserted_id;

    // set current db prefix
    $cur_prefix = $db_connections[$_SESSION["wa_current_user"]->cur_con]['tbpref'];
    $sql = str_replace(TB_PREF, $cur_prefix, $sql);

    if ($show_sql) {
        $Ajax->activate('footer_debug');
        $sql_queries .= "<pre>$sql</pre>\n<hr>";
    }

    //$result = mysql_query($sql, $db);
    $result = mysqli_query($db, $sql);

    if ($sql_trail) {
        $db_last_inserted_id = mysql_insert_id($db); // preserve in case trail insert is done
        if ($select_trail || (strstr($sql, 'SELECT') === false)) {
            mysqli_query($db, "INSERT INTO " . $cur_prefix . "sql_trail
                (`sql`, `result`, `msg`)
                VALUES(" . db_escape($sql) . "," . ($result ? 1 : 0) . ",
                " . db_escape($err_msg) . ")");
        }
    }

    if ($err_msg != null || $go_debug) {
        $exit = $err_msg != null;
        if (function_exists('xdebug_call_file'))
            check_db_error('<br>At file ' . xdebug_call_file() . ':' . xdebug_call_line() . ':<br>' . $err_msg, $sql, $exit);
        else
            check_db_error($err_msg, $sql, $exit);
    }
    return $result;
}

function db_fetch_row($result) {
    return mysqli_fetch_row($result);
}

function db_fetch_assoc($result) {
    return mysqli_fetch_assoc($result);
}

function db_fetch($result) {
    return mysqli_fetch_array($result);
}

function db_seek(&$result, $record) {
    return mysqli_data_seek($result, $record);
}

function db_free_result($result) {
    if ($result)
        mysqli_free_result($result);
}

function db_num_rows($result) {
    return mysqli_num_rows($result);
}

function db_num_fields($result) {
    return mysqli_num_fields($result);
}

function db_escape($value = "", $nullify = false) {
    $value = @html_entity_decode($value, ENT_QUOTES, $_SESSION['language']->encoding);
    $value = @htmlspecialchars($value, ENT_QUOTES, $_SESSION['language']->encoding);

    //reset default if second parameter is skipped
    $nullify = ($nullify === null) ? (false) : ($nullify);

    //check for null/unset/empty strings
    if ((!isset($value)) || (is_null($value)) || ($value === "")) {
        $value = ($nullify) ? ("NULL") : ("''");
    } else {
        if (is_string($value)) {
            //value is a string and should be quoted; determine best method based on available extensions
            if (function_exists('mysql_real_escape_string')) {
                $value = "'" . DB_escape_string($value) . "'";
            } else {
                $value = "'" . DB_escape_string($value) . "'";
            }
        } else if (!is_numeric($value)) {
            //value is not a string nor numeric
            display_error("ERROR: incorrect data type send to sql query");
            echo '<br><br>';
            exit();
        }
    }
    return $value;
}

function db_error_no() {
    global $db;
    return mysqli_connect_errno();
}

function db_error_msg($conn) {
    return mysqli_error($conn);
}

function db_insert_id() {
    global $db_last_inserted_id, $sql_trail, $db;
    return $sql_trail ? $db_last_inserted_id : mysqli_insert_id($db);
}

function db_num_affected_rows() {
    global $db;
    return mysqli_affected_rows($db);
}

function db_field_name($result, $n) {
    return mysqli_field_name($result, $n);
}

function db_create_db($connection) {
    $db = mysqli_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"]);
    if (!mysqli_select_db($db, $connection["dbname"])) {
        $sql = "CREATE DATABASE IF NOT EXISTS " . $connection["dbname"] . "";
        if (!mysqli_query($db, $sql) || !mysqli_select_db($db, $connection["dbname"]))
            return 0;
    }
    return $db;
}

function db_drop_db($connection) {
    if ($connection["tbpref"] == "") {
        $sql = "DROP DATABASE IF EXISTS " . $connection["dbname"] . "";
        return mysqli_query($db, $sql);
    } else {
        $res = db_query("show table status");
        $all_tables = array();
        while ($row = db_fetch($res))
            $all_tables[] = $row;
        // get table structures
        foreach ($all_tables as $table) {
            if (strpos($table['Name'], $connection["tbpref"]) === 0) {
                db_query("DROP TABLE `" . $table['Name'] . "`");
            }
        }
        //deleting the tables, how??
        return true;
    }
}

function db_close($dbase = null) {
    global $db;

    if (!$dbase)
        $dbase = $db;
    return mysqli_close($dbase);
}

function DB_escape_string($String) {
    global $db;
    return mysqli_real_escape_string($db, htmlspecialchars($String, ENT_COMPAT, 'utf-8', false));
}

?>
abdelghani

Re: mysqli usage

I think FA is supposed to work with PHP 4. I might be wrong but I think mysqli comes only with PHP 5.
Also, why rewrites something which work fine if there are no real benefit?

If one was rewriting it, which choosing  msqli against PDO or even a proper ORM (like doctrine or equivalent ?)

/Elax

Re: mysqli usage

you sure have a point. but actually i was just reading FA's code so i can figured out how things are working.
i intend to use FA as an ERP in my company and i certainly will need to do some heavy costumization, and yes i might choose a more advanced orm.
the example i posted was made in 15 min (not my best shot).
Anyway, i'll share the code once it's made and fully tested.
thanx

abdelghani

Re: mysqli usage

@ostertagi,
Thank you for your work. I really need it.
To be honest, I am wondering too since it might not that difficult to do simple update to newer technology.
But i do understand that some core update like database connection means another thorough test.
I will report here if there still another problem.

here is update for certain function. I have tested this function:

function db_field_name($result, $n) {
    //return mysqli_field_name($result, $n);
    $fieldinfo = mysqli_fetch_field_direct($result, $n);
    return $fieldinfo->name;
}

source

Re: mysqli usage

thanx, i missed this one!!

abdelghani

Re: mysqli usage

@ ostertagi

god promise this was my new post for today with Question " Does FA Support Mysqli and Postgresql " and guess what i found my answer on First Post of it..

Gr8 work dude ,  u r right Weberp provide many Db Ext.  let see may be new few next version of FA we can see all Ext. smile

thanks onceagain also can you please let me know u r email id .. as i need some more help from u  r side smile

7 (edited by ostertagi 11/24/2013 02:41:40 pm)

Re: mysqli usage

ostertagi@yahoo.fr

abdelghani

Re: mysqli usage

i was experiencing some bug while saving non utf8 strings, after some tests i modified the DB_escape_string :

 
function DB_escape_string($String) {
    global $db;
//    return mysqli_real_escape_string($db, htmlspecialchars($String, ENT_COMPAT, 'utf-8', false));
    return mysqli_real_escape_string($db, $String);
}
abdelghani

Re: mysqli usage

MySQLi vs. PDO_mysql

Any reason for capitalising the DB in DB_escape_string() function name?

Re: mysqli usage

Obviously pdo wins. but since, i'm a java developer it's hard for me to talk about poo while there is no item class, account class and so on.
if i was to implement an orm in FA, i would certainly rewrite all xxxxx_db.inc and use a lot of pojo-like object to handle data in a more appropriate way.
i dont know if this is relevant in php (i'm still a newbie: couple of mounths since i've started php), but until there is a significant upgrade to FA core, using mysqli is just fine by me.
here is the last update to my connect_db.inc (with no DB_escape_string() this time):

<?php

/*
 * using mysqli_connect instead of mysql_connect
 */

function set_global_connection($company = -1) {
    global $db, $transaction_level, $db_connections;

    cancel_transaction();
    // cancel all aborted transactions if any
    $transaction_level = 0;

    if ($company == -1) {
        $company = $_SESSION["wa_current_user"]->company;
    }

    $_SESSION["wa_current_user"]->cur_con = $company;

    $connection = $db_connections[$company];
    $db = mysqli_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"]);
    mysqli_select_db($db, $connection["dbname"]);
    return $db;
}

$db_duplicate_error_code = 1062;

//DB wrapper functions to change only once for whole application

function db_query($sql, $err_msg = null) {
    global $db, $show_sql, $sql_trail, $select_trail, $go_debug, $sql_queries, $Ajax, $db_connections, $db_last_inserted_id;

    // set current db prefix
    $cur_prefix = $db_connections[$_SESSION["wa_current_user"]->cur_con]['tbpref'];
    $sql = str_replace(TB_PREF, $cur_prefix, $sql);

    if ($show_sql) {
        $Ajax->activate('footer_debug');
        $sql_queries .= "<pre>$sql</pre>\n<hr>";
    }

    $result = mysqli_query($db, $sql);

    if ($sql_trail) {
        $db_last_inserted_id = mysqli_insert_id($db);
        // preserve in case trail insert is done
        if ($select_trail || (strstr($sql, 'SELECT') === false)) {
            mysqli_query($db, "INSERT INTO " . $cur_prefix . "sql_trail
                (`sql`, `result`, `msg`)
                VALUES(" . db_escape($sql) . "," . ($result ? 1 : 0) . ",
                " . db_escape($err_msg) . ")");
        }
    }

    if ($err_msg != null || $go_debug) {
        $exit = $err_msg != null;
        if (function_exists('xdebug_call_file'))
            check_db_error('<br>At file ' . xdebug_call_file() . ':' . xdebug_call_line() . ':<br>' . $err_msg, $sql, $exit);
        else
            check_db_error($err_msg, $sql, $exit);
    }
    return $result;
}

function db_fetch_row($result) {
    return mysqli_fetch_row($result);
}

function db_fetch_assoc($result) {
    return mysqli_fetch_assoc($result);
}

function db_fetch($result) {
    return mysqli_fetch_array($result);
}

function db_seek(&$result, $record) {
    return mysqli_data_seek($result, $record);
}

function db_free_result($result) {
    if ($result) {
        mysqli_free_result($result);
    }
}

function db_num_rows($result) {
    return mysqli_num_rows($result);
}

function db_num_fields($result) {
    return mysqli_num_fields($result);
}

function db_escape($value = "", $nullify = false) {
    global $db;
    $value = @html_entity_decode($value, ENT_QUOTES, $_SESSION['language']->encoding);
    $value = @htmlspecialchars($value, ENT_QUOTES, $_SESSION['language']->encoding);

    //reset default if second parameter is skipped
    $nullify = ($nullify === null) ? (false) : ($nullify);

    //check for null/unset/empty strings
    if ((!isset($value)) || (is_null($value)) || ($value === "")) {
        $value = ($nullify) ? ("NULL") : ("''");
    } else {
        if (is_string($value)) {

            $value = "'" . mysqli_real_escape_string($db, $value) . "'";
        } else if (!is_numeric($value)) {
            //value is not a string nor numeric
            display_error("ERROR: incorrect data type send to sql query");
            echo '<br><br>';
            exit();
        }
    }
    return $value;
}

function db_error_no() {
    return mysqli_connect_errno();
}

function db_error_msg($db) {
    return mysqli_error($db);
}

function db_insert_id() {
    global $db_last_inserted_id, $sql_trail, $db;
    return $sql_trail ? $db_last_inserted_id : mysqli_insert_id($db);
}

function db_num_affected_rows() {
    global $db;
    return mysqli_affected_rows($db);
}

function db_field_name($result, $n) {
    //return mysqli_field_name($result, $n);
    $fieldinfo = mysqli_fetch_field_direct($result, $n);
    return $fieldinfo->name;
}

function db_create_db($connection) {
    $db = mysqli_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"]);
    if (!mysqli_select_db($db, $connection["dbname"])) {
        $sql = "CREATE DATABASE IF NOT EXISTS " . $connection["dbname"] . "";
        if (!mysqli_query($db, $sql) || !mysqli_select_db($db, $connection["dbname"])) {
            return 0;
        }
    }
    return $db;
}

function db_drop_db($connection) {

    if ($connection["tbpref"] == "") {
        $sql = "DROP DATABASE IF EXISTS " . $connection["dbname"] . "";
        return mysqli_query($db, $sql);
    } else {
        $res = db_query("show table status");
        $all_tables = array();
        while ($row = db_fetch($res)) {
            $all_tables[] = $row;
        }
        // get table structures
        foreach ($all_tables as $table) {
            if (strpos($table['Name'], $connection["tbpref"]) === 0) {
                db_query("DROP TABLE `" . $table['Name'] . "`");
            }
        }
        //deleting the tables, how??
        return true;
    }
}

function db_close($dbase = null) {
    global $db;

    if (!$dbase) {
        $dbase = $db;
    }
    return mysqli_close($dbase);
}

?>
abdelghani

Re: mysqli usage

yes, I will be happy if we can go with PDO. I hope PDO will be implemented in next release, or next big release.

for now, my urgent need is to minimize deprecated function so we can safely follow the mysql update.

Re: mysqli usage

@ ostertagi , Dude i also shifted from MYSQL -> MYSQLI

But did u saw cancel_transaction(); thats Transactions

as we are using MySqli  hence in mysqli there  is default

mysqli_rollback and mysqli_commit  ...etc Transactions

Hence do we need to work out under includes/db/sql_functions.inc  for same ? ..

Please let me know ... also How an we try this as TRY {} CATCH {} ... which i have asked under : https://frontaccounting.com/punbb/viewtopic.php?id=4726

Re: mysqli usage

I dont see what's wrong, your data are stored normally with no loss and you're able to follow all transactions correctly.
if you really need to manage begin, commit and rollback here's a beginning of the answer:

http://stackoverflow.com/questions/9584833/mysql-autoincrement-value-jumps-by-2-when-i-add-a-transcaction-im-my-php-code

check the last answer.

abdelghani

Re: mysqli usage

We have now implemented driver for mysqli in the 2.4 environment, althoug not ready yet.

We are still testing this. During testing we found some bad SQL statements in the core, although they went fine under the mysql driver. These errors are fixed and surely more to come during testing.

Will be committed to 2.4 unstabe as soon as we have tested it.

Joe

15 (edited by tester1 11/01/2014 12:14:16 pm)

Re: mysqli usage

Hi,

After using above Mysqli my table sorting by asc/desc  is not working

which is because of

function db_field_name($result, $n)
{
    //return mysql_field_name($result, $n);
    $fieldinfo = mysqli_fetch_field_direct($result, $n);
    return $fieldinfo->name;
    
    
}

getting error:

"//-----

DATABASE ERROR : Error browsing database: SELECT id,name,datetime FROM ".TB_PREF."test_table  ORDER BY LIMIT 0, 10
error code : 1064
error message : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1
sql that failed was : SELECT id,name,datetime FROM ".TB_PREF."test_table ORDER BY LIMIT 0, 10

---//"

and if i click on sorting asc or desc then

"//-----

DATABASE ERROR : Error browsing database: SELECT id,name,datetime FROM ".TB_PREF."test_table   ORDER BY ,  LIMIT 0, 10
error code : 1064
error message : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1
sql that failed was : SELECT id,name,datetime FROM ".TB_PREF."test_table ORDER BY LIMIT 0, 10

---//"



see ORDER BY , 

i think field name is not coming here ...
chk : https://frontaccounting.com/punbb/viewtopic.php?pid=21292



i had also post the query here :  https://frontaccounting.com/punbb/viewtopic.php?id=4967

and after long time i found the prb .. i dont know its in that function or not but when i replace all mysql to mysqli as per above code my asc desc stop working .. any idea ???

Re: mysqli usage

We have implemented the mysqli driver routines in release 2.4. We do not have the resources to discuss ev. Mysqli routines during release 2.3.

When we are shipping the 2.4 we can take those discussions again.

Joe

Re: mysqli usage

but please help , u can see above update thats

and if i click on sorting asc or desc then

"//-----

DATABASE ERROR : Error browsing database: SELECT id,name,datetime FROM ".TB_PREF."test_table   ORDER BY ,  LIMIT 0, 10
error code : 1064
error message : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1
sql that failed was : SELECT id,name,datetime FROM ".TB_PREF."test_table ORDER BY LIMIT 0, 10

---//"



see ORDER BY , 

i think field name is not coming here ...

as the i have already shifted from MYSQL TO MYSQLI as per above code ... but only sorting is not working... it would be great if u look in it pls..

also by when 2.4 will be out ... else i would be to rework on many pages ...

pls help

Re: mysqli usage

Try replacing `mysql_real_escape_string` by `db_escape` in includes/db_pager.inc.