dBase connection

Yii 1.1 Installation and Setup
21

NaX,

thank you very much for your help.

can you give me a conclusion ?

what should I do to setup ODBC-dBase connection ?

  • in the main.php

  • in framework\db\schema\ folder

  • need to create myDbConnection.php ?

  • in CDbConnection.php

22

I assume you getting errors. If you are you should post the error it would help.

Anyways, I tried to implement an alternative driver. In my case a mysql clone and I found one problem.

Try the following, just remember I dont use dbase, I tried this using mysql. You will have to alter the code to work with dbase.

Create protected/components/OdbcConnection.php using the following code. (AKA myDbConnection.php)


<?php


class OdbcConnection extends CDbConnection

{


  public $altSchema = '';


  public $driverMap=array(

    'pgsql'=>'CPgsqlSchema',    // PostgreSQL

    'mysqli'=>'CMysqlSchema',   // MySQL

    'mysql'=>'CMysqlSchema',    // MySQL

    'sqlite'=>'CSqliteSchema',  // sqlite 3

    'sqlite2'=>'CSqliteSchema', // sqlite 2

    'mssql'=>'CMssqlSchema',    // Mssql driver on windows hosts

    'dblib'=>'CMssqlSchema',    // dblib drivers on linux (and maybe others os) hosts

    'sqlsrv'=>'CMssqlSchema',   // Mssql

    'oci'=>'COciSchema',        // Oracle driver

    'odbc'=>'COdbcSchema',      // Generic ODBC driver schema, this will be used if altSchema is not set

  );


  public function getDriverName()

  {

    if ($this->altSchema)

      return strtolower($this->altSchema);


    if (($pos=strpos($this->connectionString, ':')) !== false)

      return strtolower(substr($this->connectionString, 0, $pos));

  }

}

?>

Create framework/db/schema/odbc/COdbcSchema.php using something like the following. Please note this will not work out of the box. You will need to implement/override methods/function from CDbSchema.php. I copied the contents of CMysqlSchema.php just to get it working.


<?php


class COdbcSchema extends CDbSchema

{

}

?>

Edit: framework/YiiBase.php and add the following to $_coreClasses. This is something I just learned about and could have been causing you trouble.

Ref: http://code.google.com/p/yii/source/browse/tags/1.1.10/framework/YiiBase.php#632


'COdbcSchema' => '/db/schema/odbc/COdbcSchema.php',

I got it working using the following in my main.php settings.


<?php

		'db'=>array(

			'class'=>'OdbcConnection',

			'connectionString' => 'mysql:host=localhost;dbname=test',

			'emulatePrepare' => true,

			'username' => 'test',

			'password' => '12345',

			'charset' => 'utf8',

			'tablePrefix' => 'tbl_',

			'altSchema' => 'odbc',

		),

?>

Now the OdbcConnection and altSchema stuff would be optional. I needed it because my connectionString used mysql but I need it to use my odbc schema. If you had to support multiple different odbc database schemas you would need the OdbcConnection and altSchema. I described the idea in previous posts, but there is nothing stopping you from calling it framework/db/schema/dbase/* and using altSchema to force it to use your dbase schema. Its up to you.

When it comes to the contents of framework/db/schema/odbc/* you are on your own. The idea is that you override core base classes and change what needs to be different. like we did with OdbcConnection.php and CDbConnection.php. It looks like by default you will need at a mim COdbcSchema.php with a loadTable() method because that is an abstract function in CDbSchema. Have a look at some of the other schemas. You will see depending on their requirements they override different base classes. EG: CDbCommandBuilder, CDbColumnSchema, CDbTableSchema.

There is lot more you will need to do to get this working. This is about as far as I can go. The rest is up to you.

It would be easier to us a supported database, but if you really need to use dbase this is how I would start.

Good luck.

23

thank you thank you!

this is CDbException error from http://localhost/…index.php/gii/model

CDbCommand failed to execute the SQL statement: SQLSTATE[42S02]: Base table or view not found: -1305 [Microsoft][ODBC dBase Driver] The Microsoft Jet database engine could not find the object ‘MYTABLE’. Make sure the object exists and that you spell its name and the path name correctly. (SQLExecute[-1305] at ext\pdo_odbc\odbc_stmt.c:254). The SQL statement executed was: SELECT * FROM MYTABLE

can I make sure db connection is OK before building schema for ODBC-dBase ?

24

I dont really now what the error is about but it looks like it is working. It mentions the ODBC dBase Driver.

Maybe create a test controller and action or modify the default site controller and us it to do simple queries using Yii::app()->db->createCommand().

SEE: http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder

You can also try create your own connections to the db.

This doc page has some good examples: http://www.yiiframework.com/doc/api/1.1/CDbConnection

EG:




$connection=new OdbcConnection($dsn,$username,$password);

$connection->active=true;


$command=$connection->createCommand($sqlStatement);

$command->execute();   // a non-query SQL statement execution

// or execute an SQL query and fetch the result set

$reader=$command->query();


// each $row is an array representing a row of data

foreach($reader as $row) ...