Re: [PHP-DB] ODBTP, a possible solution for MS-SQL and other databases
Sounds interesting. Perhaps toss it into PEAR/PECL and see if anybody is interested in pitching in with maintenance. You never know, someone might pop up and add a bunch of improvements that will benefit you directly. -Rasmus On Sat, 2 Nov 2002, Robert Twitty wrote: Hello I have been using PHP for about 9 months, and have chosen it as my primary scripting language for web applications. I have and still use ASP and JSP. IMHO, PHP is superior and easier to use than those languages except in one area that is important to me, which is the ability to access MS SQL Server 7.0 / 2000 databases from a UNIX box. Out of the box PHP provides great support for MySQL and PostgreSQL, and at this time I have no desire to use them because I do not believe that they are ready for prime time. The open source solution that is always recommended for UNIX-based PHP / MS-SQL connectivity is freeTDS, and unfortunately I found it to be quite lacking in its capabilities and useless in certain situations. Another alternative was to use a commercial ODBC driver management system on UNIX. Sadly, it was not in the budget for this endeavor, and the PHP odbc extensions could use some work in terms of ease of use. Because I was determined to use PHP (I really dislike using JSP / JDBC on UNIX, and IIS / ASP is out of the question), I decided to create my own solution. Since I have a substantial amount of experience in programming directly with the Win32 ODBC API and TCP/IP, I decided to create a service that runs on a Win32 platform that can communicate with any platform via TCP/IP. The service uses a home grown protocol that allows a client to access any database that the service can see via the ODBC drivers that are installed on the computer which it resides. In other words, it allows a PHP client on UNIX to access a database using the ODBC drivers installed on a Windows NT / 2000 server. It is nothing more than a middle man service for Win32 ODBC. The name of the service is called ODBTP (Open Database Transport Protocol), and no there is not a RFC for this protocol. Thus far, I have successfully accessed MS-SQL, Oracle and Sybase databases via ODBTP. ODBTP consists of a Windows NT / 2000 service application, an ODBTP client library that can be used to create Win32 or UNIX clients, and a PHP extension module that was created with the library. ODBTP has the following features: * Multi-client servicing * True connection pooling (not persistent connections) * Client reserved connections (virtual connections for stateless web clients) * Supports all data types, including nvarchar, ntext, varchar(255), char(255), datetime, and bigint. * No big-endian / little-endian problems. * Server-side data binding. * Stored procedure execution, parameter passing (including NULL's) and output retrieval. * Transactions, i.e., supports commits and rollbacks under any transaction isolation level. * UNICODE data is processed using UTF-8 encoding (important since PHP strings are not UNICODE) * Can retrieve query results sent in XML format. * Verbose error reporting, all ODBC error messages are sent to client. * No discovered memory leaks or buffer overflow possibilities. * Designed to be as easy as possible to use with PHP I am new to this mailing list, and it appears that PHP is predominantly used for MySQL and PostgreSQL, and thus I am not sure if ODBTP is of any interest to most people on this list. My original intent was not to release ODBTP to the public (I really don't have the time to maintain freeware), but if there is a substantial interest I will release it to the public. I am curious to see how well it performs in other environments. -- bob The following is a table, stored procedures and a php script that uses ODBTP to initialize the table with data. CREATE TABLE dbo.Employees ( Id int IDENTITY (1, 1) NOT NULL , ExtId numeric (15,0) NOT NULL , LastName varchar (50) NOT NULL , FirstName varchar (50) NOT NULL , Title varchar (256) NOT NULL , Salary money NOT NULL , JobDesc varchar (3000) NULL , Notes ntext NULL , Active bit NOT NULL , DateEntered datetime NOT NULL , DateModified datetime NOT NULL , CONSTRAINT PKCL_Employees_Id PRIMARY KEY CLUSTERED ( Id ) ) CREATE PROCEDURE AddEmployee ExtId numeric(15,0), LastName varchar(50), FirstName varchar(50), Title varchar(256), Salary money, JobDesc varchar(3000) = 'Job not defined' AS SET NOCOUNT ON INSERT INTO Employees( ExtId, LastName, FirstName, Title, Salary, JobDesc ) VALUES( ExtId, LastName, FirstName, Title, Salary, JobDesc ) IF ERROR 0 RETURN 0 RETURN IDENTITY GO CREATE PROCEDURE SetEmployeeNotes Id int, Notes ntext AS SET NOCOUNT ON UPDATE Employees SET Notes = Notes, DateModified = getdate()
[PHP-DB] ODBTP, a possible solution for MS-SQL and other databases
Hello
I have been using PHP for about 9 months, and have chosen it as my primary
scripting language for web applications. I have and still use ASP and JSP.
IMHO, PHP is superior and easier to use than those languages except in one
area that is important to me, which is the ability to access MS SQL Server
7.0 / 2000 databases from a UNIX box. Out of the box PHP provides great
support for MySQL and PostgreSQL, and at this time I have no desire to use
them because I do not believe that they are ready for prime time. The
open source solution that is always recommended for UNIX-based PHP / MS-SQL
connectivity is freeTDS, and unfortunately I found it to be quite lacking in
its capabilities and useless in certain situations. Another alternative
was to use a commercial ODBC driver management system on UNIX. Sadly, it
was not in the budget for this endeavor, and the PHP odbc extensions could
use some work in terms of ease of use.
Because I was determined to use PHP (I really dislike using JSP / JDBC on
UNIX, and IIS / ASP is out of the question), I decided to create my own
solution. Since I have a substantial amount of experience in programming
directly with the Win32 ODBC API and TCP/IP, I decided to create a service
that runs on a Win32 platform that can communicate with any platform via
TCP/IP. The service uses a home grown protocol that allows a client to
access any database that the service can see via the ODBC drivers that are
installed on the computer which it resides. In other words, it allows a PHP
client on UNIX to access a database using the ODBC drivers installed on a
Windows NT / 2000 server. It is nothing more than a middle man service for
Win32 ODBC. The name of the service is called ODBTP (Open Database
Transport Protocol), and no there is not a RFC for this protocol. Thus
far, I have successfully accessed MS-SQL, Oracle and Sybase databases via
ODBTP.
ODBTP consists of a Windows NT / 2000 service application, an ODBTP client
library that can be used to create Win32 or UNIX clients, and a PHP
extension module that was created with the library. ODBTP has the
following features:
* Multi-client servicing
* True connection pooling (not persistent connections)
* Client reserved connections (virtual connections for stateless web
clients)
* Supports all data types, including nvarchar, ntext, varchar(255),
char(255), datetime, and bigint.
* No big-endian / little-endian problems.
* Server-side data binding.
* Stored procedure execution, parameter passing (including NULL's) and
output retrieval.
* Transactions, i.e., supports commits and rollbacks under any transaction
isolation level.
* UNICODE data is processed using UTF-8 encoding (important since PHP
strings are not UNICODE)
* Can retrieve query results sent in XML format.
* Verbose error reporting, all ODBC error messages are sent to client.
* No discovered memory leaks or buffer overflow possibilities.
* Designed to be as easy as possible to use with PHP
I am new to this mailing list, and it appears that PHP is predominantly used
for MySQL and PostgreSQL, and thus I am not sure if ODBTP is of any interest
to most people on this list. My original intent was not to release ODBTP to
the public (I really don't have the time to maintain freeware), but if
there is a substantial interest I will release it to the public. I am
curious to see how well it performs in other environments.
-- bob
The following is a table, stored procedures and a php script that uses ODBTP
to initialize the table with data.
CREATE TABLE dbo.Employees (
Id int IDENTITY (1, 1) NOT NULL ,
ExtId numeric (15,0) NOT NULL ,
LastName varchar (50) NOT NULL ,
FirstName varchar (50) NOT NULL ,
Title varchar (256) NOT NULL ,
Salary money NOT NULL ,
JobDesc varchar (3000) NULL ,
Notes ntext NULL ,
Active bit NOT NULL ,
DateEntered datetime NOT NULL ,
DateModified datetime NOT NULL ,
CONSTRAINT PKCL_Employees_Id PRIMARY KEY CLUSTERED (
Id
)
)
CREATE PROCEDURE AddEmployee
ExtId numeric(15,0),
LastName varchar(50),
FirstName varchar(50),
Title varchar(256),
Salary money,
JobDesc varchar(3000) = 'Job not defined'
AS
SET NOCOUNT ON
INSERT INTO Employees( ExtId, LastName, FirstName,
Title, Salary, JobDesc )
VALUES( ExtId, LastName, FirstName,
Title, Salary, JobDesc )
IF ERROR 0 RETURN 0
RETURN IDENTITY
GO
CREATE PROCEDURE SetEmployeeNotes
Id int,
Notes ntext
AS
SET NOCOUNT ON
UPDATE Employees SET
Notes = Notes,
DateModified = getdate()
WHERE Id = Id
GO
?php
if( !extension_loaded('odbtp') ) dl('odbtp.so');
$con = odbtp_connect( 'odbtpsvr.somewhere.com',
'DRIVER={SQL
Server};SERVER=sqlsvr.somewhere.com;UID=myuid;PWD=mypwd;DATABASE=OdbtpTest;'
) or die;
odbtp_set_attr( ODB_ATTR_TRANSACTIONS, ODB_TXN_READCOMMITTED ) or die;
$qry1 = odbtp_query( TRUNCATE TABLE
