dBase connection

ericwuu · February 29, 2012, 10:24pm

does anybody know how to (or where can I get information to ) setup db connection with dBase (.DBF) ?

thank you!

Eric (first day on Yii)

Da_Sourcerer · March 1, 2012, 9:19am

Wow. dBase is old. And AFAIK Borland is no longer. Yii is using PHP Data Objects (PDO) exclusively to managed database connections. SInce PDO does not come with a dBase driver, you’ll need to bypass that. There is a separate set of instructions for dBase. Another way would be to look for a plugin for MySQL. Not sure if there is one allowing access to dBase files. But I recall one for DB2 (Note: That’s not dBase II but IBM’s DB2!). Good luck

NaX · March 1, 2012, 11:39am

Is it maybe possible to use odbc - http://php.net/manual/en/ref.pdo-odbc.php

I have no experience in dBase so I can’t help more than that.

If possible I would look at switching to something else like SQLite, PostgreSQL or MySQL

Good luck.

ericwuu · March 1, 2012, 5:01pm

yeap dBase is old but it’s not dead. ( www.dbase.com )

I have been using php with dBase using ODBC driver , no problem.

just don’t know how to make it work in Yii …

CDbconnection.php

public $driverMap=array(

	'pgsql'=&gt;'CPgsqlSchema',    // PostgreSQL


	'mysqli'=&gt;'CMysqlSchema',   // MySQL


	'mysql'=&gt;'CMysqlSchema',    // MySQL


	'sqlite'=&gt;'CSqliteSchema',  // sqlite 3


	'sqlite2'=&gt;'CSqliteSchema', // sqlite 2


	'mssql'=&gt;'CMssqlSchema',    // Mssql driver on windows hosts


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


	'sqlsrv'=&gt;'CMssqlSchema',   // Mssql


	'oci'=&gt;'COciSchema',        // Oracle driver


);

Da_Sourcerer · March 1, 2012, 5:12pm

You can’t. Pure and simple. As I stated above: Yii is using PDO for all db-connectivity. Since there is no PDO driver for dBase available, you’ll have to bypass Yii’s databas abstraction layer completely.

ericwuu · March 1, 2012, 8:06pm

( I have ODBC named APPSHARED using Microsoft dBase Driver in Windows 7)

I can use PDO to access my dBase tables succesfully.

<?php

$dbh= new PDO(‘odbc:APPSHARED’, ‘’, ‘’);

$stmt = $dbh->prepare("select * from myTable");

$stmt->execute();

while ($row = $stmt->fetch()) {

   print_r(&#036;row);

}

unset($dbh); unset($stmt);

?>

my question is how to setup in the framework’s main.php

	'db'=&gt;array(


		'connectionString' =&gt; ?


		'emulatePrepare' =&gt; true,


		'username' =&gt; '',


		'password' =&gt; '',


		'charset' =&gt; 'utf8',


	),

maybe need to setup something in CDbconnection.php too ?

noe · March 1, 2012, 8:24pm

I think you can’t use db-connectivity for dBase. But you can create a component using PHP extension for dBase from PECL (see http://www.php.net/manual/en/book.dbase.php) and use it to read, insert, update or delete records.

Good luck,

S.

NaX · March 2, 2012, 6:54am

I don’t know how possible this is but, I think you can extend Yii to support ODBC.

From what I can tell you will need to extend CDbConnection to added a schema to $driverMap.

http://code.google.com/p/yii/source/browse/tags/1.1.10/framework/db/CDbConnection.php

Then you will have to create a new schema for odbc_dbase

EG: http://code.google.com/p/yii/source/browse/tags/1.1.10/framework/db/schema/sqlite/CSqliteSchema.php

I also found this issue about something similar, FYI: There is a link at the bottom to the migrated Github issue.

http://code.google.com/p/yii/issues/detail?id=3074&can=1&q=odbc&colspec=ID%20Type%20Status%20Priority%20Milestone%20Owner%20Stars%20Summary

Good luck.

ericwuu · March 5, 2012, 2:21pm

No ODBC database schema in Yii?

NaX · March 5, 2012, 3:03pm

From what I can see there is no ODBC schema. You will have to create one using one of the existing schema’s like sqlite as a starting base.

Da_Sourcerer · March 5, 2012, 3:11pm

There will hardly be one since ODBC is an interface, not a DB per se.

ericwuu · March 5, 2012, 4:48pm

I am new to MVC, I choose Yii to start learning the framework tech. and find out that Yii does not support odbc , however I know I can use PDO to connect to my dBase data.

Should I continue with Yii or looking for other framework ?

(siwtch to other database is not an option)

isn’t odbc popular? why Yii can not support it ?

Da_Sourcerer · March 5, 2012, 5:42pm

This has little to do with ODBC being “unpopular”. It’s a rather rare pairing, most people would prefer MySQL or a similar modern RDBMS with a call-level interface that’s tighter integrated into PDO. that’s why Yii/dBase won#t work out-of-the-box.

NaX · March 6, 2012, 7:29am

It is my understanding that ODBC is normally used in situations where there is no native database driver available. Because ODBC is a generic database interface it has been suggested that it is a little slower than native drivers.

I don’t think it would be to difficult to create a odbc based dbase schema. From my quick evaluation it looks possible.

EG: Create

db/schema/dbase/CDbaseColumnSchema.php

db/schema/dbase/CDbaseCommandBuilder.php

db/schema/dbase/CDbaseSchema.php

Then hack/extend $driverMap in db/CDbConnection.php to support your new schema.

And if everything works you could then setup your connection using something like this.




'db'=>array(

    'connectionString'=>'dbase:DSN=APPSHARED',

    'tablePrefix'=>'tbl_',

),

This is all just what I would investigate. Is their a reason you stuck with dbase?

ericwuu · March 6, 2012, 7:00pm

I created those files ( copied from sqlite , replace "sqlite" with "dbase" on the php filename and its content)

db/schema/dbase/CDbaseColumnSchema.php

db/schema/dbase/CDbaseCommandBuilder.php

db/schema/dbase/CDbaseSchema.php

I’ve added

		'dbase'=&gt;'CDbasechema',        // dBase

for $driverMap

I run index.php/gii/crud to test the connection.

and I got

      Error 500 CDbConnection failed to open the DB connection

//

the $columnTypes in CDbaseSchema.php :

public &#036;columnTypes=array( 


    'pk' =&gt; 'Numeric(10) NOT NULL PRIMARY KEY',


    'string' =&gt; 'Character',


    'text' =&gt; 'Character',


    'integer' =&gt; 'Numeric(10)',


    'float' =&gt; 'Numeric',


    'decimal' =&gt; 'Numeric',


    'datetime' =&gt; 'TIMESTAMP',


    'timestamp' =&gt; 'TIMESTAMP',


    'time' =&gt; 'Character(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />',


    'date' =&gt; 'Character(10)',


    'binary' =&gt; 'Binary',


    'boolean' =&gt; 'Logical',


'money' =&gt; 'Numeric(19,4)',


);

// I got a new job it uses dBase developed desktop and web applications.

NaX · March 7, 2012, 12:17pm

Ok, I had another quick look at this.

I see you can specify the class that should be used for the connection. so you can create your own extension with a class that extends from cDbConnection and overrides the required elements. This would be a better idea than hacking CDbConnection directly.


'db'=>array(

    'class'=>'myDbConnection',

    'connectionString'=>'odbc:APPSHARED',

    'tablePrefix'=>'tbl_',

    'alt_schema' => 'odbc_dbase' // or just dbase

),

The first attempt looks like it did not work because connectionString is passed directly to PDO, so this has to be correct.

So you could rename your schema files to be odbc and try again.

EG: db/schema/dbase/COdbcColumnSchema.php

The one problem is that the schema of odbc schemas could be different for each database type. So adding something like the alt_schema option you could override CDbConnection::getSchema() to look for alt_schema, or what every you decide to call it, and then change schema to the correct odbc database specific schema.

I hope that helps.

ericwuu · March 7, 2012, 3:25pm

Thank you very much, NaX!

it helps a LOT!

but I need more …

what should be modified in myDbConnection.php (after copied from cDbConnection.php) ?

how to modify CDbConnection::getSchema() to look for alt_schema ?

public function getSchema()

{

}

NaX · March 8, 2012, 7:27am

You need to bear with me. I am also just starting to learn Yii.

Firstly you can try to get a single odbc schema working without worrying about a db specific schemas.

When it comes to your custom connection class. My understanding is that using getters and setters (Magic Methods), public properties are automatically set when your class is initialised by Yii. If a property specific set function exits EG: setMyProperty() or setalt_schema(). Maybe we should change it to use CamelCase so setAltSchema(). Then this set function will be used to set the property.

You can see how this magic method works here: http://www.yiiframework.com/doc/api/1.1/CComponent#__set-detail

I would suggest something like the following for you custom class.

FYI: I changed my mind and decided it would be better to maybe try override CDbConnection::getDriverName();


<?php

class myDbConnection.php 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

    'odbc_dbase'=>'COdbcDbaseSchema', // ODBC dbase driver schema

  );


  public function getDriverName()

  {

    if ($this->altSchema)

      return strtolower($this->altSchema);

    

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

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

  }

}

?>

Lastly I now think subSchema is more appropriate than altSchema.

I hope that all made sense.

ericwuu · March 8, 2012, 5:11pm

thank you again for your help.

i am confused.

So I got the myDbConnection.php — ok.

should I also create

framework\db\schema\odbc\COdbcColumnSchema.php

framework\db\schema\odbc\COdbcCommandBuilder.php

framework\db\schema\odbc\COdbcSchema.php

and create

framework\db\schema\odbcdbase\COdbcDbaseColumnSchema.php

framework\db\schema\odbcdbase\COdbcDbaseCommandBuilder.php

framework\db\schema\odbcdbase\COdbcDbaseSchema.php

?

and, in the main.php , setup like this ?

‘db’=>array(

‘class’=>‘myDbConnection’,

‘connectionString’=>‘odbc:APPSHARED’,

‘tablePrefix’=>‘tbl_’,

‘alt_schema’ => ‘odbcdbase’

),

NaX · March 9, 2012, 7:05am

At a minimum you will need to create the framework\db\schema\odbcdbase\* files. What actually needs to be in the schema files I cant help you with this as I don’t have any experience with dbase.

If you want to be technically correct you should create both odbc and odbcdbase files and odbcdbase should extend from the relevant odbc schema files only overriding what is different to that db. This would be ideal in my opinion because then later you can support other odbc schemas if required.

The odbc schema files would be simple standard SQL only that would be common to most databases. The odbcdbase schema would then override anything that is not applicable or different.

I would focus on getting it working first. Getting it to successfully connecting and running a queries and worry about technical details later.

Good luck.