Connecting to MS SQL database from bash (using unixODBC)

P

ph0enix

I need to connect to a MS SQL database from a bash script so I installed unixODBC 2.2.14 and I don't know what to do next. Can you help?

Thank you! :D

J.
 
You can't, AKAIK. Use perl / python / php to connect to server. Bash can be only used when you have native UNIX client available.
 
Install php ms sql module from /usr/ports/lang/php5-extensions/:
Code: 
portsnap fetch update
cd /usr/ports/lang/php5-extensions/
make config
Make sure you select MSSQL database support. Also, select other modules if you want. Once done install the same:
Code: 
make install clean
Another option is only install php5-mssql
Code: 
cd /usr/ports/databases/php5-mssql 
make install clean
OR binary package:
Code: 
pkg_add -r -v php5-mssql
See php help pages about connections and functions related to mssql connectivity via PHP5 - http://www.php.net/function.mssql-connect
 
Thanks! I compiled MS SQL support in php5-extensions but I'm unable to connect to the SQL server (2005) from PHP(5.2.9) using the following code:

Code: 
<?php
$myServer = "10.x.x.x";
$myUser = "username";
$myPass = "password";
$myDB = "master";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT id, info, status ";
$query .= "FROM sysobjects ";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";

//display the results
while($row = mssql_fetch_array($result))
{
  echo "<li>" . $row["id"] . $row["info"] . $row["status"] . "</li>";
}
//close the connection
mssql_close($dbhandle);
?>

The message it gives me is:
Code: 
Couldn't connect to SQL Server on 10.x.x.x

Here are my PHP mssql settings specified in php.ini:
Code: 
mssql.allow_persistent = On
mssql.max_persistent = -1
mssql.max_links = -1
mssql.min_error_severity = 10
mssql.min_message_severity = 10
mssql.compatability_mode = Off
mssql.secure_connection = Off

..and the output of "php -m":

Code: 
[PHP Modules]
calendar
ctype
curl
date
dom
filter
ftp
hash
iconv
imap
json
libxml
mssql
mysql
odbc
openssl
pcre
PDO
pdo_sqlite
posix
Reflection
session
SimpleXML
soap
SPL
SQLite
standard
tokenizer
xml
xmlreader
xmlwriter
zip

[Zend Modules]

Occasionally the SQL server logs a message like:

Code: 
06/15/2009 12:42:53,Logon,Unknown,The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 10.x.x.x]

or:

Code: 
06/15/2009 12:09:56,Logon,Unknown,Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 10.x.x.x]

...when I execute the PHP code on the FreeBSD system. I thought maybe there is something wrong with the configuration of the SQL server but I tried running the above PHP code on a Linux box (connecting to the same server) and it works fine. I checked the mssql settings in php.ini on the Linux box and they're the same as on the FreeBSD system. Do you know what could be wrong?

Thanks! :)

J.
 
Did you try using SQL Profiler to start a trace to see if there is any difference in what the Linux and FreeBSD machine is sending to the SQL server.

Is there anything between the FreeBSD and SQL server that isn't present in the Linux machine route?

I don't see anything wrong with the code itself.
 
Unfortunately it's a production SQL server and I can't run the profiler on it since it's a resource hog.

Could you explain this part?

roddierod said:
Is there anything between the FreeBSD and SQL server that isn't present in the Linux machine route?
Click to expand...

Are we talking about the IP routing table? If so, I'm able to telnet into port 1433 on the SQL server from both, the Linux and the FreeBSD systems.

Thanks! :D

J.
 
ph0enix said:
Are we talking about the IP routing table? If so, I'm able to telnet into port 1433 on the SQL server from both, the Linux and the FreeBSD systems.
Click to expand...

I was wondering if maybe it was a firewall issue not letting the FreeBSD machine have access to the SQL port. But since you can telnet that would seem to rule that out.

If you don't have access to a development server to test things on maybe you should try php-adodb-ext-5.04 in the ports. That will allow you to access the SQL server using ADO commands - which may be easier to debug for a MS SQL server.
 
I compiled ADODB and loaded the extension. Now my "php -m" says:

Code: 
[PHP Modules]
ADOdb
calendar
ctype
curl
date
dom
filter
ftp
hash
iconv
imap
json
libxml
mssql
mysql
odbc
openssl
pcre
PDO
pdo_sqlite
posix
Reflection
session
SimpleXML
soap
SPL
SQLite
standard
tokenizer
xml
xmlreader
xmlwriter
zip

I found some samples here:
http://adodb.sourceforge.net/

...but they're not working. They mention including config.inc.php but I don't have that file on my system.

I tried running this:
Code: 
<?php
ini_set("display_errors", 1);
error_reporting(E_ALL);
$DB = NewADOConnection('mssql');
$DB->Connect('10.x.x.x', 'username','password', 'master');

# M'soft style data retrieval with binds
$rs = $DB->Execute("select * from sysobjects",array($key));
while (!$rs->EOF) {
    print_r($rs->fields);
    $rs->MoveNext();
}
?>
...and it gave me:

Code: 
Fatal error: Call to undefined function NewADOConnection() in /usr/home/jacks/adodb.php on line 4

Do you know what I'm doing wrong?
Thanks again for your help! :D

J.
 
You need this at the beginning of your code

Code: 
 require_once('adodb/adodb-exceptions.inc.php');
 require_once('adodb/adodb.inc.php');
 
I did that and now it's giving me:

Code: 
Warning: require_once(adodb/adodb-exceptions.inc.php): failed to open stream: No such file or directory in /current/path/adodb.php on line 4
Fatal error: require_once(): Failed opening required 'adodb/adodb-exceptions.inc.php' (include_path='.:/usr/local/share/pear') in /current/path/adodb.php on line 4

Any ideas?

Thank you! :D

J.
 
You have to set you include path in php.ini to include the directory that contains adodb directory, otherwise you have to use the full path in the require_once().
 
roddierod said:
You have to set you include path in php.ini to include the directory that contains adodb directory, otherwise you have to use the full path in the require_once().
Click to expand...

The problem is that I don't have adodb.inc.php or adodb-exceptions.inc.php anywhere on my system.
 
I double checked my install. I have /usr/local/share/adodb.

It looks as if that comes from the adodb-4.99.1 port. It's been a good while since I installed all this. I've been using the adodb with php for 5 or 6 years.
 
roddierod said:
I double checked my install. I have /usr/local/share/adodb.

It looks as if that comes from the adodb-4.99.1 port. It's been a good while since I installed all this. I've been using the adodb with php for 5 or 6 years.
Click to expand...

Do you have adodb.so at all on your system?
 
roddierod said:
/usr/local/lib/php/20060613 is where it is located on my system. That is where all my php extensions are
Click to expand...

That's where it is on my system too. I added the following line to /usr/local/etc/php/extensions.ini:

Code: 
extension=adodb.so

...so adodb.so gets loaded automatically. My feeling is that adodb.so is supposed to replace the PHP scripts you've mentioned but I can't find any documentation regarding using adodb.so.
** banging head into the wall **
 
If you look at the example of the sourceforge page link you posted the first line is:

include('adodb/adodb.inc.php');

The test-adodb.php that is in the php-adodb-ext source package also contains such a line. Take a look at that example as the adodb.so calls have a slightly different syntax.
 
roddierod said:
If you look at the example of the sourceforge page link you posted the first line is:

include('adodb/adodb.inc.php');

The test-adodb.php that is in the php-adodb-ext source package also contains such a line. Take a look at that example as the adodb.so calls have a slightly different syntax.
Click to expand...

Do you have an idea as to where I can get the missing PHP scripts or why they don't come as part of the 5.04 distribution?

Thanks! :)

J.
 
They are part of the adodb-4.99.1 port. Not sure why they are part of the 5.04 port since there are something not implemented in the extension that call for the scripts.
 
I got the scripts by installing the adodb5 port (I guess it's separate from the extension) but I'm still not having any luck connecting to the SQL server using the following code:

Code: 
<?php
ini_set("display_errors", 1);
error_reporting(E_ALL);
require_once('/usr/local/share/adodb/adodb-exceptions.inc.php');
require_once('/usr/local/share/adodb/adodb.inc.php');
$DB = NewADOConnection('mssql');
$DB->Connect('10.x.x.x', 'username','password', 'master');

# M'soft style data retrieval with binds
$rs = $DB->Execute("select * from sysobjects",array($key));
while (!$rs->EOF) {
    print_r($rs->fields);
    $rs->MoveNext();
}
?>

It produces:

Code: 
Warning: mssql_connect(): Unable to connect to server:  10.x.x.x in /usr/local/share/adodb/drivers/adodb-mssql.inc.php on line 520

Fatal error: Uncaught exception 'ADODB_Exception' with message 'mssql error: [: Connection error to server '10.x.x.x' with user 'username'] in CONNECT(10.x.x.x, 'username', '****', master)
' in /usr/local/share/adodb/adodb-exceptions.inc.php:78
Stack trace:
#0 /usr/local/share/adodb/adodb.inc.php(535): adodb_throw('mssql', 'CONNECT', false, 'Connection erro...', '10.x.x.x', 'master', Object(ADODB_mssql))
#1 /usr/home/jacks/ado4.php(7): ADOConnection->Connect('10.x.x.x', 'username', 'password', 'master')
#2 {main}  thrown in /usr/local/share/adodb/adodb-exceptions.inc.php on line 78

Thanks! :D

J.
 
With such a general error it pretty hard to figure out since you can't see what is hitting the server itself or if it's even reaching the server.

You need to be able to try the could against a development server so you can turn on SQL profiler then work you way back.
 
I got FreeTDS to work in PHP by creating /usr/local/etc/freetds.conf with the following info in it:
Code: 
[sql_server]
        host = 10.x.x.x
        port = 1433
        tds version = 8.0

...then using sql_server as the server name in the PHP code.
Connecting by IP address doesn't work. I don't know why.

Thanks for the help! :D

J.
 
You must log in or register to reply here.
Back
Top