need to create v1.7.x plugin for pulling data from an oci8 database into template

Question

I've used oci8 databases in CodeIgniter applications in the past, but before I have never connected to a database in EE plugins I have written in the past.

I imagine I could use the database class to connect and query the oracle (oci8) database to return the data to the template.

I have always set up the database settings in the db config file of my CodeIgniter instance. I am unsure of what to do when trying to set the db connection within the plugin code. I need a push start. Any help is appreciated. Thanks.

Answer 1

I have multiple functions in the plugin I created, but I'll just include one, for simplicity.

For usage in the template:

{exp:banner_courses:list_all_courses term_code="201340"}

This returns all courses within the term of 201340

You'll notice it calls a private function get_course_listing "$this->get_course_listing()", that retrieves all courses that relate to the subject. That isn't really important.

The meat and potatoes of getting yourself up and running with retrieving data from an oracle database is in the construct

<?php

$plugin_info = array();

class banner_courses {

  var $return_data;

  function __construct() {
    $this->db = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host.name)(PORT=1521))(CONNECT_DATA=(SID=instance.name)))';
    $this->conn = oci_pconnect('username', 'password', $this->db);
  }


  function list_all_courses()
  {
    global $TMPL, $FNS;

    $term_code = ( ! $TMPL->fetch_param('term_code')) ? FALSE :  $TMPL->fetch_param('term_code');

    if(!$term_code)
            return $this->return_data = '';

        $subject_query = oci_parse($this->conn, "SELECT DISTINCT SUBJECT_DESC, SUBJ_CODE FROM szvcourses WHERE TERM_CODE = :term_code ORDER BY SUBJECT_DESC");

        oci_bind_by_name($subject_query, ":term_code", $term_code, 6);

        oci_execute($subject_query);
        oci_fetch_all($subject_query, $subjects);

        $i = 0;
        $count = count($subjects['SUBJECT_DESC']);

        $course_listing = '';

        while($i < $count):
            $course_listing .= $this->get_course_listing($term_code, $subjects['SUBJ_CODE'][$i]);
            $i++;
        endwhile;

        return $this->return_data = $course_listing;
  }

}
?>