New OLE-DB extension for connecting to MS SQL

[chernyshevsky]

In the past month I've been polishing off my new OLE-DB PHP extension. It now works quite well so I reckon it's time to share it with a wider audience. The extension's been compiled for PHP 4.3.x, 4.4.x, 5.0.x, and 5.1.x. You can download it here:

http://sourceforge.net/project/showfiles.php?group_id=171247&package_id=198554

What is OLE-DB? If you have used ADO (COM version, not the PHP library) before, OLE-DB is what ADO actually uses to communicate with a database. It's like ODBC in a way, but more powerful. Besides RBDM, you can also use OLE-DB to retrieve data from Indexing Service and Active Directory.

But since this is a MSSQL forum, I'll stick to that ;D. Here are some advantages to using this extension with MSSQL:

* Works with SQL Server 2005 and SQL Server Express (which do not come with DBLIB)
* Ability to retrieve ntext/nvarchar columns as UTF-8 and various character sets
* Not limited to 255 characters when retrieving varchar
* Faster than the old DBLIB-based extension
* Much faster than ODBC
* Less overhead than than COM + ADO
* OLE-DB connection pooling is more efficient than persistent connections

And I'm actively maintaining this (I use MSSQL myself), so if you find a bug, it'll be fixed in a timely fashion.

The extension has an option to create mssql_xxxxx function aliases. If your existing code makes use of old extension, it will likely work without modifications.

Anyway, let me know if you run into any problems :)

[argoSquirrel]

You sir, are a genius.

I was having serious problems with mssql_connect connecting and I just plugged this in and BAM, it worked.  I thank you ever so greatly.

[chernyshevsky]

[quote author=argoSquirrel link=topic=106192.msg425077#msg425077 date=1156982739]
You sir, are a genius.

I was having serious problems with mssql_connect connecting and I just plugged this in and BAM, it worked.  I thank you ever so greatly.
[/quote]

Hmmm, that's interesting. I have no problem connecting to SQL Server Express thru the old extension once the SQL2K dll was installed.

[argoSquirrel]

oledb_bind($query, "@user_name", $user_name, SQLVARCHAR, FALSE, FALSE, 20);

Am I crazy or should that work?

Warning: oledb_bind() [function.oledb-bind]: Unsupported type in C:\wamp\www\authenticate.php on line 14

[chernyshevsky]

[quote author=argoSquirrel link=topic=106192.msg425157#msg425157 date=1156999537]
oledb_bind($query, "@user_name", $user_name, SQLVARCHAR, FALSE, FALSE, 20);

Am I crazy or should that work?

Warning: oledb_bind() [function.oledb-bind]: Unsupported type in C:\wamp\www\authenticate.php on line 14
[/quote]

It should work, provided that oledb.mssql_aliases is turned on. Otherwise SQLVARCHAR wouldn't be defined. Try OLEDB_VARCHAR instead or switch on oledb.mssql_aliases.

[argoSquirrel]

Well, I tried switching on mssql_aliases and it didn't work.  Same error as before.  I just switched it over to OLEDB_ and it worked fine.

I'm beginning to think there is just something horribly wrong with my mssql configuration.

Thanks for the help.

[argoSquirrel]

Heh, sorry to keep pestering, but would there be a reason oledb_num_rows() doesn't return when the result set is populated?  If the num_rows actually returned is 0, it will respond with 0.  If the set contains rows > 0 then it dies.

[chernyshevsky]

[quote author=argoSquirrel link=topic=106192.msg425541#msg425541 date=1157050221]
Heh, sorry to keep pestering, but would there be a reason oledb_num_rows() doesn't return when the result set is populated?  If the num_rows actually returned is 0, it will respond with 0.  If the set contains rows > 0 then it dies.
[/quote]

Your feedback is much apprepriated. What you encountered sounds like a bug. The oledb_num_rows is a problematic function, as the OLE-DB provider doesn't always know how many rows there are in a result set. The old mssql extension has that info because it buffers all the rows. I will take a look at it tonight.

[argoSquirrel]

Hmm, another thing I am not sure about.

I have an SP with multiple input parameters.  When I execute it from a query browser in enterprise manager it runs perfectly.  However, when i execute it from php it gets run multiple times.  I did some testing and figured out that it is getting run once for each oledb_bind that i do.  I'm using the same syntax as before.

For now I'll just make my SP smarter and do IF EXISTS on the table that's getting repeats, but I wonder if this is my fault and i coded it wrong or if it's a bug.

Thanks again.

[chernyshevsky]

[quote author=argoSquirrel link=topic=106192.msg425709#msg425709 date=1157082741]
Hmm, another thing I am not sure about.

I have an SP with multiple input parameters.  When I execute it from a query browser in enterprise manager it runs perfectly.  However, when i execute it from php it gets run multiple times.  I did some testing and figured out that it is getting run once for each oledb_bind that i do.  I'm using the same syntax as before.

For now I'll just make my SP smarter and do IF EXISTS on the table that's getting repeats, but I wonder if this is my fault and i coded it wrong or if it's a bug.

Thanks again.
[/quote]

I think that's a bug. My guess is that your stored procedure doesn't return a record set. Looking at the code, I notice that oledb_execute() would try to run execute a statement a second time if it fails to get a record set. This logic is obviously flawed. In the meantime you can get around this by passing true as the second parameter (skip_results). I'll recompile this tomorrow. I don't think it's running the statement per call to oledb_bind().

Can you be a bit more precise about the issue with oledb_num_rows()? By dying do you mean it crashes the server? The function will return false if it can't figure out how many rows there are. It could work better but I don't see it crashing or forcing the script to quit.

[chernyshevsky]

Shoot. The stored-proc code is more screw-up than I thought. Need to spend a bit more time on this.

[argoSquirrel]

[quote author=chernyshevsky link=topic=106192.msg425727#msg425727 date=1157085980]
Can you be a bit more precise about the issue with oledb_num_rows()? By dying do you mean it crashes the server? The function will return false if it can't figure out how many rows there are. It could work better but I don't see it crashing or forcing the script to quit.
[/quote]

Well, by dying I meant it is not really doing anything.  No value is output at all.

Other than a few things here and there, it's working great.  Thanks a bunch for doing this.

[chernyshevsky]

Okay, I have fixed both issues. You were right about the oledb_execute() running the stored-proc multiple times. I had inadvertently set the number of parameter sets to the number of parameters. Stupid mistake. I have also implemented output parameter and return variable, features I had forgot about. oledb_num_rows() now works the way it should. I had busted it earlier while getting the extension to work with Active Directory. It's still a function you shouldn't depend on: it doesn't work when the record set is large (> 1000). That's simply a nature of SQL Server--because rows could disappear while the record set is open, it simply can't know how number there are.

Here's the link again:

http://sourceforge.net/project/showfiles.php?group_id=171247&package_id=198554

Thanks a lot for your help. OLE-DB is so broad that it's very hard for me to check for issues alone.

[argoSquirrel]

[quote author=chernyshevsky link=topic=106192.msg426985#msg426985 date=1157257869]
Okay, I have fixed both issues. You were right about the oledb_execute() running the stored-proc multiple times. I had inadvertently set the number of parameter sets to the number of parameters. Stupid mistake. I have also implemented output parameter and return variable, features I had forgot about. oledb_num_rows() now works the way it should. I had busted it earlier while getting the extension to work with Active Directory. It's still a function you shouldn't depend on: it doesn't work when the record set is large (> 1000). That's simply a nature of SQL Server--because rows could disappear while the record set is open, it simply can't know how number there are.

Here's the link again:

http://sourceforge.net/project/showfiles.php?group_id=171247&package_id=198554

Thanks a lot for your help. OLE-DB is so broad that it's very hard for me to check for issues alone.
[/quote]

Yeah, NP.  I am glad to help whenever.

I'm generally using num_rows to trap errors on null sets (I know, I should return errors in my SPs, but I'm lazy) so I don't think 1000 + will ever be an issue.

[argoSquirrel]

Oooh, that was not a drop-in replacement.

Warning: oledb_execute() [function.oledb-execute]: The parameter is incorrect in C:\wamp\www\browse.php on line 52

Same error across the board.

51 $query = oledb_init("sp_getAllArtists", $conn);
52 $result = oledb_execute($query);


I'll just roll-back to .5 for the meantime.

[argoSquirrel]

[quote author=argoSquirrel link=topic=106192.msg428846#msg428846 date=1157498118]
Same error across the board.
[/quote]

Actually, it's not across the board.  It only breaks on SP calls with no input parameters.  If there is a bind it does not break.

Also, I'm a total idiot and overwrote version .5 and don't have a copy available.  You still have it lying around?

[chernyshevsky]

[quote author=argoSquirrel link=topic=106192.msg428846#msg428846 date=1157498118]
Oooh, that was not a drop-in replacement.

Warning: oledb_execute() [function.oledb-execute]: The parameter is incorrect in C:\wamp\www\browse.php on line 52

Same error across the board.

51 $query = oledb_init("sp_getAllArtists", $conn);
52 $result = oledb_execute($query);


I'll just roll-back to .5 for the meantime.
[/quote]

Ughhhh...The fact that I'm retarded makes me unhappy  :'(. Hardcoding 1 as the number of parameter sets obviously wouldn't work when there are zero bound parameters...

It's fixed. You can download the new version at the same link. Once again, thanks for your help.

[oops]

I am using this new oledb extension for creating a prepared statement..with following lines of code...
     
$oConRef= oledb_connect($Host,$DBUserName,$DBPassword);
$oConRef= oledb_select_db($DBName,$oConRef);
   
    $statement=oledb_prepare("Update table XUSER where user_type=?");
    oledb_bind($statement,'@user_type',$user_type,OLEDB_VARCHAR);
    $rdxdata=oledb_execute($statement);
      while($row = oledb_fetch_array($rdxdata))
      {
          $dd=$row['user_type'];
        echo "\n".$dd;
      }


is showing following error if i am going for a bind statement...
[b]Warning: oledb_bind() [function.oledb-bind]: Unsupported type  [/b]

could u plz correct me where m wrong.
Is there any syntax error which is creating problem..if so then what will be the correct code for this./.

I want to prepare a prepared statement for my query which is being called for diffent user types...at different- different places.so i need to bind different values for "user type " with the same query.

i am using PHP 5.0,MS SQL 2000 and oledb combination.


plz help  me out...its quite urgent.
thanks,
oops

[chernyshevsky]

[quote author=oops link=topic=106192.msg431946#msg431946 date=1157958644]
I am using this new oledb extension for creating a prepared statement..with following lines of code...
     
$oConRef= oledb_connect($Host,$DBUserName,$DBPassword);
$oConRef= oledb_select_db($DBName,$oConRef);
   
    $statement=oledb_prepare("Update table XUSER where user_type=?");
    oledb_bind($statement,'@user_type',$user_type,OLEDB_VARCHAR);
    $rdxdata=oledb_execute($statement);
      while($row = oledb_fetch_array($rdxdata))
      {
          $dd=$row['user_type'];
        echo "\n".$dd;
      }


is showing following error if i am going for a bind statement...
[b]Warning: oledb_bind() [function.oledb-bind]: Unsupported type  [/b]

could u plz correct me where m wrong.
Is there any syntax error which is creating problem..if so then what will be the correct code for this./.

I want to prepare a prepared statement for my query which is being called for diffent user types...at different- different places.so i need to bind different values for "user type " with the same query.

i am using PHP 5.0,MS SQL 2000 and oledb combination.


plz help  me out...its quite urgent.
thanks,
oops




[/quote]

The binding process appears to be broken in the PHP 5 version of the module. I will take a look at it tonight.

[chernyshevsky]

[quote author=chernyshevsky link=topic=106192.msg432059#msg432059 date=1157982067]
The binding process appears to be broken in the PHP 5 version of the module. I will take a look at it tonight.
[/quote]

Actually, it had nothing to do with PHP 5--I just broke oledb_prepare() while fixing the earlier binding issues. Now it should it works. Once again, here's the URL:

http://sourceforge.net/project/showfiles.php?group_id=171247&package_id=198554


Incidently your syntax is incorrect. To bind to a variable in a prepared statement you use a number:

$statement=oledb_prepare("SELECT * FROM table where user_type=?");
oledb_bind($statement,1,$user_type,OLEDB_VARCHAR);

1 for the first ?, 2 for the second ?, etc.

[argoSquirrel]

@oops
Why don't you just use a Stored Procedure?  You seem to be about 1 step away already and you would then get to benefit from the inherent benefits of SPs (speed, reusability, lower bandwidth).  It also allows you to better extract the business case into the DB layer, which is (arguably) a better solution.

@Cherny
[s]I seem to be hitting a limit of 255 chars on a varchar return.  I'm just doing a fetch_assoc.  This a problem?[/s]

Sorry, it's actually on the update.

[chernyshevsky]

[quote author=argoSquirrel link=topic=106192.msg432495#msg432495 date=1158031596]
@oops
Why don't you just use a Stored Procedure?  You seem to be about 1 step away already and you would then get to benefit from the inherent benefits of SPs (speed, reusability, lower bandwidth).  It also allows you to better extract the business case into the DB layer, which is (arguably) a better solution.

@Cherny
[s]I seem to be hitting a limit of 255 chars on a varchar return.  I'm just doing a fetch_assoc.  This a problem?[/s]

Sorry, it's actually on the update.
[/quote]

Hmmm, you mean when you call a stored-proc? By default, oledb_bind will chop off a string at 255 if maxlen is omitted. Here's the full definition of the function:

oledb_bind ( resource stmt, string param_name, mixed &var, int type [, int is_output [, int is_null [, int maxlen]]] )

Binding to OLEDB_TEXT does not have this limitation.

[argoSquirrel]

[quote author=chernyshevsky link=topic=106192.msg432506#msg432506 date=1158034253]
[quote author=argoSquirrel link=topic=106192.msg432495#msg432495 date=1158031596]
@oops
Why don't you just use a Stored Procedure?  You seem to be about 1 step away already and you would then get to benefit from the inherent benefits of SPs (speed, reusability, lower bandwidth).  It also allows you to better extract the business case into the DB layer, which is (arguably) a better solution.

@Cherny
[s]I seem to be hitting a limit of 255 chars on a varchar return.  I'm just doing a fetch_assoc.  This a problem?[/s]

Sorry, it's actually on the update.
[/quote]

Hmmm, you mean when you call a stored-proc? By default, oledb_bind will chop off a string at 255 if maxlen is omitted. Here's the full definition of the function:

oledb_bind ( resource stmt, string param_name, mixed &var, int type [, int is_output [, int is_null [, int maxlen]]] )

Binding to OLEDB_TEXT does not have this limitation.
[/quote]

I do have maxlen specified.  I have it set to 1000.  Does oledb_varchar support MAX instead of a integer value? 

Text seems to work fine.

[chernyshevsky]

[quote author=argoSquirrel link=topic=106192.msg432507#msg432507 date=1158034482]
I do have maxlen specified.  I have it set to 1000.  Does oledb_varchar support MAX instead of a integer value? 

Text seems to work fine.
[/quote]

Oops, the maxlen value wasn't being used. Fixed. Incidently there was another regression involving blob retrieval. Hence three fixes in 0.62:

Fixed oledb_prepare() regression.
Fixed regression with blob retrieval.
Fixed incorrect application of maxlen in oledb_bind().

[meloks]

Do you know if you can force OLE DB do endoding conversion from UCS-2 (MS SQL) to UTF-8 (on web page)?

Regards.