How to make use of an external database abstraction layer

Scheduler Javascript
#1

Hi there,

I’m using dhtmlxScheduler with a PHP application which makes use of a modified ADODB database abstraction layer. Now I don’t want to limit my application to only mysql by using something like

$res=mysql_connect($mysql_server,$mysql_user,$mysql_pass);
mysql_select_db($mysql_db);

To use my application with dhtmlxScheduler on different databases I have to replace all the mysql functions.

In my example below I am able to do to make (theoretically) INSERT and UPDATE calls to any database by connecting to the DB by using Global $CFG variable and my application’s own database manipulation functions (e.g. execute_sql).

That code would work on MS SQL, MySQL, Postgres, SQLLite and Oracle without changing anything if only for the mysql_connect mysql_select parts.

<?php require_once('../../config.php'); include ('config.php'); include ('codebase/scheduler_connector.php'); $res=mysql_connect($mysql_server,$mysql_user,$mysql_pass); mysql_select_db($mysql_db); $scheduler = new schedulerConnector($res); $scheduler->enable_log("log.txt",true); function myUpdate($action) { Global $CFG; $start = strtotime($action->get_value('sessdate')); $finish = strtotime($action->get_value('sessionend')); $duration = ($finish - $start); $description = $action->get_value('description'); $subject = $action->get_value('subject'); $teacher = $action->get_value('teacher'); $sessiontitle = $action->get_value('sessiontitle'); $courseid = $action->get_value('courseid'); execute_sql("UPDATE {$CFG->prefix}attendance_sessions SET courseid = '{$courseid}', sessdate = '{$start}', duration = '{$duration}', sessionend = '{$finish}', description = '{$action->get_value('description')}', teacher = '{$action->get_value('teacher')}', subject = '{$action->get_value('subject')}', sessiontitle = '{$action->get_value('sessiontitle')}' WHERE id='{$action->get_id()}'"); $action->success(); } function myInsert($action) { Global $CFG; $start = strtotime($action->get_value('sessdate')); $finish = strtotime($action->get_value('sessionend')); $duration = ($finish - $start); $description = $action->get_value('description'); $subject = $action->get_value('subject'); $teacher = $action->get_value('teacher'); $sessiontitle = $action->get_value('sessiontitle'); $courseid = $action->get_value('courseid'); execute_sql("INSERT INTO {$CFG->prefix}attendance_sessions (courseid, sessdate, duration, sessionend,description, teacher, subject, sessiontitle) VALUES ('{$courseid}', '{$start}', '{$duration}', '{$finish}', '{$description}', '{$teacher}', '{$subject}', '{$sessiontitle}')"); $action->success(); } $scheduler->event->attach("beforeUpdate","myUpdate"); $scheduler->event->attach("beforeInsert","myInsert"); $scheduler->render_sql("SELECT s.id,from_unixtime(s.sessdate) AS sessdate,from_unixtime(s.sessionend) AS sessionend,s.description,su.subject,t.teacher,s.sessiontitle,c.shortname as courseid FROM {$CFG->prefix}attendance_sessions s LEFT JOIN {$CFG->prefix}course c ON s.courseid = c.id LEFT JOIN {$CFG->prefix}groups g ON s.groupid = g.id LEFT JOIN {$CFG->prefix}attendance_teachers t ON s.teacher = t.id LEFT JOIN {$CFG->prefix}attendance_subjects su ON s.subject = su.id","id","sessdate,sessionend,description,subject,teacher,sessiontitle,courseid"); ?>

Please, how can I get rid of the mysql specific code?

Here is how my database config.php file works:

<?php unset ( $CFG ) ; $CFG->dbtype = 'mysql' ; $CFG->dbhost = 'localhost' ; $CFG->dbname = 'dbname' ; $CFG->dbuser = 'username' ; $CFG->dbpass = 'secret'; $CFG->dbpersist = false; $CFG->prefix = 'tbl_'; $CFG->wwwroot = 'http://www.example.com' ; $CFG->dirroot = '/home/admin/public_html/example.com' ; $CFG->dataroot = '/home/admin/public_html/example.com/uploaddata' ; $CFG->admin = 'admin' ; $CFG->directorypermissions = 00777 ; $CFG->unicodedb = true ; require_once ( "$CFG->dirroot/lib/setup.php" ) ; ?>

Thanks a lot,

Barry

#2

a) Connector class need to receive the connection object and DB type , so you can generated connection by any possible code and just specify necessary DB type as second parameter

    $res = oci_connect($oci_dbuser,$oci_dbpass,$oci_dbname);
    $scheduler = new schedulerConnector($conn,"Oracle");

or

    $res = oci_connect($oci_dbuser,$oci_dbpass,$oci_dbname);
    $scheduler = new schedulerConnector($conn,"Oracle");

or

    $dbh = new PDO('pgsql:host=localhost;dbname='.$mysql_db.";user=root;password=1234");
    $grid = new GridConnector($dbh,"PDO");

The latest one, with PDO can be used without modification for all db types, only db type in connection string need to be changed

b) instead of execute_sql you can use $scheduler->sql->query - it will call the method which is appropriate to the current DB type

#3

Thank you pointing me to PDO. I’m not sure you gave the right syntax (it didn’t work so I looked PDO up on php.net). I think I have the right syntax now and seem to be connecting using PDO when I run my script on it’s own (without Scheduler) since I put an echo statement in to tell me if a connection was established. However when run as part of Scheduler it gives me “Error Type: LoadXML … etc”.

I tried to do as you said using the following code (I also tried by typing the db info straight into the connection string in different ways but it didn’t work either):

<?php
include ('../../config.php');
//  a database connection is already establised above, I could immediately use mysql_query commands now with no problems.

$hostname = $CFG->dbhost;
$username = $CFG->dbuser;
$password = $CFG->dbpass;
$dbname = $CFG->dbname;
$dbtype = $CFG->dbtype;
//include ('config.php');  // not needed, info already obtained from first include

try {
    $dbh = new PDO($dbtype.":host=".$hostname.";dbname=".$dbname, $username, $password);
    echo 'Connected to database';  // this works fine, if I run this file on it's own I get 'Connected to database'
    }
catch(PDOException $e)
    {
    echo $e->getMessage();  // exceptions are thrown
    }

include ('scheduler/codebase/scheduler_connector.php');

$scheduler = new schedulerConnector($dbh,"PDO");

//$scheduler = new schedulerConnector($res);
$scheduler->enable_log("log.txt",true);
$offset = 0; // get_timezone_offset($CFG->timezone);

function myUpdate($action){
Global $CFG;
$offset = 0;
    $start = ((strtotime($action->get_value('sessdate')))+$offset);
    $finish = ((strtotime($action->get_value('sessionend')))+$offset);
    $duration = ($finish - $start);
    $courseid = $action->get_value('courseid');

    execute_sql("UPDATE {$CFG->prefix}attendance_sessions SET courseid = '{$courseid}', sessdate = '{$start}', duration = '{$duration}', sessionend = '{$finish}', description = '{$action->get_value('description')}', teacher = '{$action->get_value('teacher')}', subject = '{$action->get_value('subject')}', sessiontitle = '{$action->get_value('sessiontitle')}' WHERE id='{$action->get_id()}'");

    $action->success();
}

function myInsert($action){
Global $CFG;
$offset = 0;
    $start = ((strtotime($action->get_value('sessdate')))+$offset);
    $finish = ((strtotime($action->get_value('sessionend')))+$offset);
    $duration = ($finish - $start);
    $description = $action->get_value('description');
    $subjectvalue = $action->get_value('subject');
    $subject = str_replace('s', '', $subjectvalue);
    $teachervalue = $action->get_value('teacher');
    $teacher = str_replace('t', '', $teachervalue);
    $sessiontitlevalue = $action->get_value('sessiontitle');
    $sessiontitle = str_replace('t', '', $sessiontitlevalue);
    $courseidvalue = $action->get_value('courseid');
    $courseid = str_replace('c', '', $courseidvalue);
    
    execute_sql("INSERT INTO {$CFG->prefix}attendance_sessions (courseid, sessdate, duration, sessionend,description, teacher, subject, sessiontitle) VALUES ('{$courseid}', '{$start}', '{$duration}', '{$finish}', '{$description}', '{$teacher}', '{$subject}', '{$sessiontitle}')");
   
    $action->success();
}

$scheduler->event->attach("beforeUpdate","myUpdate");
$scheduler->event->attach("beforeInsert","myInsert");

$scheduler->render_sql("SELECT s.id,from_unixtime(s.sessdate) AS sessdate,from_unixtime(s.sessionend) AS sessionend,s.description,su.subject,t.teacher,s.sessiontitle,c.shortname as courseid FROM {$CFG->prefix}attendance_sessions s LEFT JOIN {$CFG->prefix}course c ON s.courseid = c.id LEFT JOIN {$CFG->prefix}groups g ON s.groupid = g.id LEFT JOIN {$CFG->prefix}attendance_teachers t ON s.teacher = t.id LEFT JOIN {$CFG->prefix}attendance_subjects su ON s.subject = su.id","id","sessdate,sessionend,description,subject,teacher,sessiontitle,courseid");

?>

Also, I’m not having any trouble using my own execute_sql functions. My first include (’…/…/config.php’); makes sure that all those functions will work and they do work when used as per my first post (with mysql_connect etc).

#4

Try to add the next line

    $scheduler->enable_log("some.txt",true);
    $scheduler->render_sql("

and provide the error of result log

Also, you can try to load php script directly in browser - it can show the reason of error as well.