Jump to content


Photo

New OLE-DB extension for connecting to MS SQL


  • Please log in to reply
30 replies to this topic

#1 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 30 August 2006 - 05:40 AM

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.n...ckage_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 :)

#2 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 August 2006 - 12:05 AM

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.

#3 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 31 August 2006 - 04:16 AM

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.


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

#4 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 August 2006 - 04:45 AM

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

#5 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 31 August 2006 - 05:24 PM

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


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.

#6 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 August 2006 - 06:18 PM

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.

#7 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 August 2006 - 06:50 PM

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.

#8 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 31 August 2006 - 07:31 PM

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.


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.


#9 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 01 September 2006 - 03:52 AM

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.

#10 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 01 September 2006 - 04:46 AM

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.


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.

#11 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 02 September 2006 - 03:03 AM

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

#12 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 02 September 2006 - 06:42 AM

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.


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.

#13 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 03 September 2006 - 04:31 AM

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.n...ckage_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.

#14 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 05 September 2006 - 03:26 AM

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.n...ckage_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.


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.

#15 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 05 September 2006 - 11:15 PM

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.

#16 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 05 September 2006 - 11:25 PM

Same error across the board.


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?

#17 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 06 September 2006 - 03:12 AM

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.


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.

#18 oops

oops
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 11 September 2006 - 07:10 AM

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...
Warning: oledb_bind() [function.oledb-bind]: Unsupported type 

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





#19 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 11 September 2006 - 01:41 PM

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...
Warning: oledb_bind() [function.oledb-bind]: Unsupported type 

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





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

#20 chernyshevsky

chernyshevsky
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 12 September 2006 - 12:18 AM

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


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.n...ckage_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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users