?
Solved

Connecting to MS SQL Server via PEAR

Posted on 2006-05-24
17
Medium Priority
?
1,651 Views
Last Modified: 2013-12-12
I have a connection class that I am trying to use to connect to SQL server on the local network via PEAR. The web code has not changed; however, one of the drives in the SQL server machine died and SQL server had to be reinstalled. Apparently, there is some setting in SQL server that is missing from the new install that was present in the old. I have dbo privs set for the user that I am trying to connect with, but when I execute the web script, it will not connect. There is no record of the attempted connection in the SQL Server logs and there is nothing between the machines that would block the request (both the web server and database server are plugged into the same switch in the server rack).

Below is my PHP code:

File 1 (connection class - ABC.class.php)
<code>
<?
include("DB.php");

class ABC
{
      function db_connect($database)
            {
                  $dsn = array(
                        'phptype'  => 'mssql',
                        'username' => 'TestUser',
                        'password' => 'TestUser',
                        'hostspec' => '192.168.0.153:1433',
                        'database' => $database
                  );

                    $db =& DB::connect($dsn);
                  
                  if (PEAR::isError($db)) {
                        die('Standard Message: ' . $db->getMessage() . '<br>' .
                              'Standard Code: ' . $db->getCode() . '<br>' .
                              'DBMS/User Message: ' . $db->getUserInfo() . '<br>' .
                              'DBMS/Debug Message: ' . $db->getDebugInfo() . '<br>');
                  }
                        
                  return $db;            
            }
}
?>
</code>

File 2 (Script that uses the class)
<code>
<?
include("ABC.class.php");
$abc = new ABC;
?>

<html>
<body>

<?
     $abc->db_connect("TestDatabase");
?>

</body>
</html>
</code>

The output from the script is:

Standard Message: DB Error: connect failed
Standard Code: -24
DBMS/User Message: [DB Error: connect failed] ** Array
DBMS/Debug Message: [DB Error: connect failed] ** Array

Again, I believe this to be an issue with SQL server because the PHP code has not changed, but if there is anything else that I can do in my PHP code to get more descriptive error messages out of SQL Server, please make any suggestions.

0
Comment
Question by:lukeinjax
  • 9
  • 7
17 Comments
 
LVL 15

Expert Comment

by:m1tk4
ID: 16757478
do a

die(print_r($db,true))

instead of

die('Standard Message: ' . $db->getMessage() . '<br>' .
                         'Standard Code: ' . $db->getCode() . '<br>' .
                         'DBMS/User Message: ' . $db->getUserInfo() . '<br>' .
                         'DBMS/Debug Message: ' . $db->getDebugInfo() . '<br>');

0
 

Author Comment

by:lukeinjax
ID: 16757781
m1tk4,

I originally had the "die(print_r($db,true))" in there, but it didn't seem to give much useful info. Below is its output. Please let me know if you can get anything out of it...

DB_Error Object ( [error_message_prefix] => [mode] => 1 [level] => 1024 [code] => -24 [message] => DB Error: connect failed [userinfo] => [DB Error: connect failed] ** Array [backtrace] => Array ( [0] => Array ( [file] => C:\php5\PEAR\DB.php [line] => 888 [function] => PEAR_Error [class] => PEAR_Error [type] => ->  [args] => Array ( [0] => DB Error: connect failed [1] => -24 [2] => 1 [3] => 1024 [4] => [DB Error: connect failed] ) ) [1] => Array ( [file] => C:\php5\PEAR\PEAR.php [line] => 540 [function] => DB_Error [class] => DB_Error [type] => ->  [args] => Array ( [0] => -24 [1] => 1 [2] => 1024 [3] => [DB Error: connect failed] ) ) [2] => Array ( [file] => C:\php5\PEAR\DB\common.php [line] => 1849 [function] => raiseError [class] => PEAR [type] => ->  [args] => Array ( [0] => [1] => -24 [2] => [3] => [4] => [DB Error: connect failed] [5] => DB_Error [6] => 1 ) ) [3] => Array ( [file] => C:\php5\PEAR\DB\mssql.php [line] => 205 [function] => raiseError [class] => DB_common [type] => ->  [args] => Array ( [0] => -24 [1] => [2] => [3] => [4] => ) ) [4] => Array ( [file] => C:\php5\PEAR\DB.php [line] => 556 [function] => connect [class] => DB_mssql [type] => ->  [args] => Array ( [0] => Array ( [phptype] => mssql [dbsyntax] => mssql [username] => TestUser [password] => TestUser [protocol] => [hostspec] => 192.168.0.153:1433 [port] => [socket] => [database] => TestDatabase ) [1] => ) ) [5] => Array ( [file] => C:\www\html\Test\ABC.class.php [line] => 23 [function] => connect [class] => DB [type] => ->  [args] => Array ( [0] => Array ( [phptype] => mssql [username] => TestUser [password] => TestUser [hostspec] => 192.168.0.153:1433 [database] => TestDatabase ) ) ) [6] => Array ( [file] => C:\www\html\Test\test_db.php [line] => 26 [function] => db_connect [class] => ABC [type] => ->  [args] => Array ( [0] => TestDatabase ) ) ) [callback] => )
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16760862
Does the server use SQL Authentication or Windows Authentication of both?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:lukeinjax
ID: 16761304
TestUser uses SQL authentication only.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16768949
You can be quick and dirty and add this line of code to the mssql.php file in the PEAR/DB directory.

Look for ...

        if (!$this->connection) {
            return $this->raiseError(DB_ERROR_CONNECT_FAILED,
                                     null, null, null,
                                     @mssql_get_last_message());
        }


and replace with ...

        if (!$this->connection) {

var_export(mssql_get_last_message());

            return $this->raiseError(DB_ERROR_CONNECT_FAILED,
                                     null, null, null,
                                     @mssql_get_last_message());
        }


Sure, its a nasty quick fix, but gets the REAL error a LOT quicker.

DON'T FORGET TO REMOVE THAT LINE ONCE YOU'VE GOT A RESPONSE!!!
0
 

Author Comment

by:lukeinjax
ID: 16771071
RQuading,

I tried your suggestion, but all it gave me was a set of empty quotes ('') at the beginning of my previous output. What were you hoping that this would return?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16788044
I was expecting the proper error. As you got nothing, I am not sure how/why the system things you got an error.
0
 

Author Comment

by:lukeinjax
ID: 16807011
Any other suggestions? I really need to get this figured out...
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16807076
Ok. Another debug. Can you revert to the original file and remove the @ symbols in mssql.php

Does this produce the same output?

OOI. You ARE supplying a correct username and password? Case sensitive password?
0
 

Author Comment

by:lukeinjax
ID: 16807145
RQuading,

Thanks for your suggestions.

I removed the @ symbols from the connect function and received:

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: 192.168.0.153:1433 in C:\php5\PEAR\DB\mssql.php on line 201

Still a very vague message... I'm stumped...
0
 

Author Comment

by:lukeinjax
ID: 16807162
Yes, the username and password are correct. I can connect via odbc with no issue using the same username/password.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16807236
In the ODBC setup, what connection mechanism is being used?

TCPIP
Named Pipes
Multiprotocol
...


Is the server named or IP'd?

Is the "Dynamically determine port" checked or not?

0
 

Author Comment

by:lukeinjax
ID: 16817211
I figured out the issue with the server that I originally inquired about... kind of. I ended up creating a domain account for Apache to start with that had the necessary access to SQL Server and set the mssql.secure_connection = on in my php.ini. This way I can connect via NT authentication. I also removed the reference to the port since there is only one (un-named) instance on this machine.

My problem now is connecting to another server that uses a named SQL instance. I have tried specifying a port number in the DSN array as the port key value, by putting in in the hostspec with a comma (192.168.0.125,1433), a colon (192.168.0.125:1433), and as an instance name (192.168.0.125\MYSERVER), but none of these work. Does anyone have any experience with connecting to a named SQL Server instance?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16817234
Try

192.168.0.125\\MYSERVER (most likely to work as \ has to be escaped in windows).

or

192.168.0.125/MYSERVER (Maybe PHP is clever enough to realise what is meant here).
0
 

Author Comment

by:lukeinjax
ID: 16817245
RE: above comment - I am experiencing the exact same issue as I was before:

Standard Message: DB Error: connect failed
Standard Code: -24
DBMS/User Message: [DB Error: connect failed] ** Array
DBMS/Debug Message: [DB Error: connect failed] ** Array

I have tried all of the same suggestions from above, but cannot connect.
0
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 1000 total points
ID: 16817362
Take a look at
http://www.builderau.com.au/architect/database/soa/Access_Microsoft_SQL_Server_2000_using_PHP/0,39024547,20283164,00.htm

specifically ..



From Matt Overington:
02/02/2006, 10:13 AM

Comments from the Author (Jean-Luc David):

A lot has changed in two years including the database and PHP API. Here are comments from the PHP site that may be useful:

http://ca.php.net/function.mssql-connect

The recently released SQL Server 2005 Express will work with MSSQL functions. Here are some caveats:

1) The version of ntwdblib.dll supplied with PHP will not work with Server 2005 (Win32 only). You must use a newer version. The version supplied with SQL Sever 2000 (2000.80.194.0) seems to work well.
2) You must set up mssql_connect to reference the server AND the instance name, eg:
mssql_connect("MyPC\SQLEXPRESS","MyUser","MyPwd");

Although the MSSQL functions work, you might want to consider using the new 'pdo_odbc' (PHP 5.1) with SQL Server 2005 Express for any new code you are writing.

Another suggestion is making sure the user has installed the "Microsoft SQL Server Native Client" feature pack (which includes ODBC drivers for SQL Server 2005). It should be included on the SQL Server 2005 DVD. Otherwise, users can download the drivers from:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc

Pubs and Northwind (the databases referenced in the article) are not installed by default with SQL Server 2005. You have to install them separately via this download:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034

Final suggestion, the user should walk through the process of connecting to a new database one step at a time. Use the odbc_connect return code as an indicator whether the connection was actually successful, then try a very basic queries, ect. It makes it easier to troubleshoot rather than copy/pasting code and trying to figure out where it went wrong.
0
 

Author Comment

by:lukeinjax
ID: 16832918
I ended up using ODBC instead of PEAR. Got tired of fighting it. Points go to RQuading for all the suggestions. Thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question