Robert, this seems like an excellent idea. you should present in PHP-DEV, though, since this NG is primarily for "userland" PHP users, not low-level PHP developers. l0t3k "Robert Twitty" <awabob@starpower.net> wrote in message 003101c28233$9d1ece60$9b00a8c0@bobawa">news:003101c28233$9d1ece60$9b00a8c0@bobawa... > 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 Employees" ) or die; > > odbtp_prepare( "{? = call AddEmployee( ?, ?, ?, ?, ?, ? )}", $qry1 ) or > die; > odbtp_out( $qry1, 1 ); > > $qry2 = odbtp_allocate_query() or die; > odbtp_prepare( "{call SetEmployeeNotes( ?, ? )}", $qry2 ) or die; > > function AddEmployee( $qry, $ExtId, $LastName, $FirstName, $Title, > $Salary, $JobDesc ) > { > odbtp_in( $qry, 2, $ExtId ); > odbtp_in( $qry, 3, $LastName ); > odbtp_in( $qry, 4, $FirstName ); > odbtp_in( $qry, 5, $Title ); > odbtp_in( $qry, 6, $Salary ); > if( $JobDesc != ODB_DEFAULT ) > odbtp_in( $qry, 7, $JobDesc ); > else > odbtp_in( $qry, 7 ); > > odbtp_execute( $qry ) or die; > $out = odbtp_fetch_output( $qry ) or die; > return $out[1]; > } > function SetEmployeeNotes( $qry, $Id, $Notes ) > { > odbtp_in( $qry, 1, $Id ); > odbtp_in( $qry, 2, $Notes ); > odbtp_execute( $qry ) or die; > } > $Id = AddEmployee( $qry1, "111222333444555", "Twitty", "Robert", "ODBTP > Creator", ".2", ODB_DEFAULT ); > SetEmployeeNotes( $qry2, $Id, "I've added my 2 cents worth to PHP." ); > > $Id = AddEmployee( $qry1, "311322343454685", "Johnson", "Mary", > "Supervisor", 100000, "Supervises folks" ); > SetEmployeeNotes( $qry2, $Id, "She can read greek: ???????? - ? Dr. ??? > ?????????" ); > > $Id = AddEmployee( $qry1, "568899902133452", "Smith", "John", "CEO", > "38020979.46", NULL ); > SetEmployeeNotes( $qry2, $Id, "He's the guy that makes all the > money." ); > > $Id = AddEmployee( $qry1, "100000900000000", "Gordon", "Susan", > "Computer Programmer", 73144.57, "Makes web-based applications" ); > SetEmployeeNotes( $qry2, $Id, "Single quotes (') and double quotes (\") > are not a problem when you do data binding." ); > > odbtp_commit(); > ?> > <html> > <head> > <title>ODBTP Test - Initialization</title> > <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> > </head> > <body> > <?php > odbtp_query( "SELECT * FROM Employees" ) or die; > > $cols = odbtp_num_fields( $qry1 ); > > echo "<table cellpadding=2 cellspacing=0 border=1>\n"; > echo "<tr>"; > for( $col = 0; $col < $cols; $col++ ) > echo "<th> " . odbtp_field_name( $qry1, $col ) . " </th>"; > echo "</tr>\n"; > > odbtp_bind_field( $qry1, "DateEntered", ODB_CHAR ) or die; > > while( ($rec = odbtp_fetch_array($qry1)) ) > { > echo "<tr>"; > for( $col = 0; $col < $cols; $col++ ) > echo "<td><nobr> $rec[$col] </nobr></td>"; > echo "</tr>\n"; > } > echo "</table>\n"; > > odbtp_close(); > ?> > </body> > </html> > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
