Re: ODBTP, a possible solution for MS-SQL and other databases

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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>&nbsp;" . odbtp_field_name( $qry1, $col ) .
"&nbsp;</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>&nbsp;$rec[$col]&nbsp;</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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux