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
