Using Zend_DB to connect to MSSQL Server using ODBC

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Using Zend_DB to connect to MSSQL Server using ODBC

Haddad Said
HI,

I am trying to use Zend_DB to connect to Microsoft SQL Server 2005 (Express Edition) running on a Windows 2003 server using ODBC.

I have a System Wide DSN configured with the name 'foo', and I have succesfully connected to it using PDO with the following piece of code


$db = new PDO('odbc:foo', $login, $passwd);

$res = $db->query('SELECT TOP 10 * FROM bar');

echo "<pre>" . print_r($res->fetchAll(), 1) . "</pre>";


However I don't know exactly how to do it using the Zend Framework as there is no class Zend_Db_Adapter_Pdo_Odbc, and I dont want to use Zend_Db_Adapter_Pdo_Mssql since the driver uses an old API which doesnt seem to allow you using unicode characters with it.

Can someone please provide a bit of help?


Thanks


Haddad
Reply | Threaded
Open this post in threaded view
|

Re: Using Zend_DB to connect to MSSQL Server using ODBC

Stanislav Malyshev
> However I don't know exactly how to do it using the Zend Framework as
> there is no class Zend_Db_Adapter_Pdo_Odbc, and I dont want to use
> Zend_Db_Adapter_Pdo_Mssql since the driver uses an old API which doesnt
> seem to allow you using unicode characters with it.

As far as I understand there's currently no adapter for ODBC. I think
there should be.

--
Stanislav Malyshev, Zend Products Engineer
[hidden email]  http://www.zend.com/

Reply | Threaded
Open this post in threaded view
|

Re: Using Zend_DB to connect to MSSQL Server using ODBC

Bill Karwin from Zend
Stanislav Malyshev wrote:
>> However I don't know exactly how to do it using the Zend Framework as
>> there is no class Zend_Db_Adapter_Pdo_Odbc, and I dont want to use
>> Zend_Db_Adapter_Pdo_Mssql since the driver uses an old API which
>> doesnt seem to allow you using unicode characters with it.
>
> As far as I understand there's currently no adapter for ODBC. I think
> there should be.
>
Sure, now there's a feature request for an ODBC adapter for Zend_Db.  I
made the description extremely brief.  If you have more specific
requirements, please add comments to the following JIRA issue:
http://framework.zend.com/issues/browse/ZF-905

For example, should this be based on PDO_ODBC?  Or the native ODBC
extension in PHP?

For what it's worth Haddad, so far I have not been able to connect
Zend_Db to MS SQL Server 2005 Express Edition either.

Regards,
Bill Karwin
Reply | Threaded
Open this post in threaded view
|

Re: Using Zend_DB to connect to MSSQL Server using ODBC

Stanislav Malyshev
> For example, should this be based on PDO_ODBC?  Or the native ODBC
> extension in PHP?

Well, if we could have both it'd be nice ;) I'm not sure actually which
one is more important - that depends on what is more widely used and
what different features each of them has, etc.
But I'm convinced we should have at least one of them for 1.0.
--
Stanislav Malyshev, Zend Products Engineer
[hidden email]  http://www.zend.com/

Reply | Threaded
Open this post in threaded view
|

Re: Using Zend_DB to connect to MSSQL Server using ODBC

akrabat
In reply to this post by Bill Karwin from Zend
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bill Karwin wrote:

> For what it's worth Haddad, so far I have not been able to connect
> Zend_Db to MS SQL Server 2005 Express Edition either.

It works fine for me, using Zend_Db_Adapter_Pdo_Mssql. I had to install
SQL Server 2005 Express edition with no instance though.

Regards,

Rob...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFF0kbI421+qn4cITwRAlnLAJ9ik3th35DZ2EYjdNeeT5hP85oT4ACeKIly
RTPtWz0sIbXtRRQJcrKNtfU=
=k3A0
-----END PGP SIGNATURE-----
Reply | Threaded
Open this post in threaded view
|

Re: Using Zend_DB to connect to MSSQL Server using ODBC

Haddad Said
Hi Rob,


On 13/02/07, Rob Allen <[hidden email]> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Bill Karwin wrote:
>
> > For what it's worth Haddad, so far I have not been able to connect
> > Zend_Db to MS SQL Server 2005 Express Edition either.
>
> It works fine for me, using Zend_Db_Adapter_Pdo_Mssql. I had to install
> SQL Server 2005 Express edition with no instance though.
>
>

Can you please give me a sample of code you used to achieve a
connection? I am kind of new to both pro_mssql and Zend_db.

Also do you have any problems using unicode with that adapater, the
last time I used mssql_query() I ran into an error "Unicode data in
Unicode-only collation or ntext data cannot be sent to clients using
DB-Library (such as ISQL) or ODBC version 3.7 or earlier", which, when
I looked deeper, turns out that Microsoft have upgraded their API and
although the old API is supported for now, it is restricted in some
ways.



Thanks



Haddad
Reply | Threaded
Open this post in threaded view
|

Re: Using Zend_DB to connect to MSSQL Server using ODBC

Haddad Said
In reply to this post by Bill Karwin from Zend
Hi Bill,

On 13/02/07, Bill Karwin <[hidden email]> wrote:
>
> For example, should this be based on PDO_ODBC?  Or the native ODBC
> extension in PHP?

I would prefer it to be based on the native ODBC extension in PHP, as
installing the PDO extensions was not smooth on my linux machine,
although it was much more easier on a windows box.

I would also imagine Zend_Db's dependence on PDO has caused some
problems to a few people already



Haddad
Reply | Threaded
Open this post in threaded view
|

Re: Using Zend_DB to connect to MSSQL Server using ODBC

akrabat
In reply to this post by Haddad Said
Haddad Said wrote:
>
> Can you please give me a sample of code you used to achieve a
> connection? I am kind of new to both pro_mssql and Zend_db.

it's extremely simple:

$db = Zend_Db::factory($config->db->adapter,
        $config->db->config->asArray());

Where config.ini contains:
[db]
db.adapter = PDO_MSSQL
db.config.host = localhost
db.config.username = u12345
db.config.password = 12345
db.config.dbname = db12345


I did a standard install of SQL Server 2005 Express, turn on the
"advanced" options during install and selected to use the "default"
instance rather than a named one. I also picked Mixed Mode
authentication in the install program. After install, I used the Surface
Area tool to enable TCPIP & Named Pipes connections.


That was about it.

Regards,

Rob...