PHP5 and MsSQL on openSUSE11

English Applications
1

I am running openSUSE 11.2 and would like to connect to MsSQL using PHP5.3.0 (I do connect to MySQL 5.1.45)
Lots of info is available on the Google but nothing on SUSE.
I installed “freeTDS” and “PHP5-PDO” objects but am not sure which way to go.
Is ODBC another option? speed/functions?
Thank you in advance for any advice and guidance.

2

Do you mean you do not want the PHP version in the repository? If so, then probably the reason you cannot find anything on it is that no one thinks it is worth installing an earlier version than the one in the repository.

3

  1. I did install the PHP 5.3.1.51 + php-mssql version from repository;
    I can connect to SQL2005Express but cannot retrieve records.
    I am not sure where to look / change as freetds.conf (freetds v0.82 and TDS version: 4.2) looks Ok.
    I had to change “mssql.secure_connection”

  2. There are a lot of us that would like to migrate but require connectivity to MS / Oracle, some people have struggled for weeks, some has given up, and me?
    I am patient…

4

I am connecting, mysql, mssql and oracle at Aker Solutions Egersund. The mssql is equal to mysql in use. For Oracle it’s a little different.

You has to install php5-mssql for MsSQL, php5-mysql for MySQL and php5-oci8 for Oracle.

To find php5-mssql for OpenSuSE was some googling away, but I managed to find it. It should have been in the standard packages from OpenSuSE, but it’s not.

One Click Install php5-mssql

5

Thanks for reply, I did mension that php-mssql is installed.
I can connect using freeTDS, let me share…

suse11-3m5:/srv/www/htdocs # /usr/bin/tsql -C
Compile-time settings (established with the “configure” script)
Version: freetds v0.82
freetds.conf directory: /etc
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: 4.2
iODBC: no
unixodbc: yes

suse11-3m5:/srv/www/htdocs # /usr/bin/tsql -S mssqlsrv -U ***** -P *****
locale is “en_US.UTF-8”
locale charset is “UTF-8”
1> version
using TDS version 7.0
1> quit

I can connect per mssrv.php but cannot retrieve the number of contact records…
Echo “result” shows nothing…

?php
$myServer = “10.10.0.28\sqlexpress”;
$myUser = “";
$myPass = "”;
$myDB = “NetCall”;

//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 * FROM Contacts LIMIT 0,15”;

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

$numRows = mssql_num_rows($result);
//echo $numRows “<br/>”;

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

echo $query;
echo $result;

//close the connection
mssql_close($dbhandle);
?>

6

So why didn’t you proceed further with the CLI tsql program to see if the query statements work there?

7

No sure what command to use to retrieve / show records using freeTDS.
Can you share…
Thank you in advance.

8

The same commands you use in PHP. All that the php-mssql module is doing is piping your queries to MSSQL via TDS.

9

I either get a

  1. Invalid object name ‘contact’ or
  2. Incorrect syntax near ‘netcall’.

Do I have to “go” after each line?
Do I have to skip the first 5 lines because we are already connencted?

Thank you in advance.

10

You would have to type in whatever interactive commands are needed to connect you with the database in question in that session, which is distinct from the session started by PHP. That will help you figure out what might be wrong with your query statements.

11

Can you use LIMIT on mssql? I can’t here:

SQL Server 9.0.3054
SELECT * FROM [SomeTable] LIMIT 0,15

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘0’.

SELECT * FROM [SomeTable]
<returned rows here>

This is via MS SQL management Studio though, not via PHP. Could be different maybe?

12

$myServer = “10.10.0.28\sqlexpress”; is wrong!! It’s ment to work on Windows host only.

This is correct on Linux/Unix:

<?php
$hostname=‘mssql.server.com:1433’; //// specify host, i.e. ‘localhost’
$user=‘mssqluser’; //// specify username
$pass=‘mssqlpassword’; //// specify password
$dbase=‘mssqldatabase’; //// specify database name
$connection = mssql_connect("$hostname" , “$user” , “$pass” ,TRUE)
or die ("<p>Can’t connect to MsSQL server $hostname</p><br><p>Please
contact hepldesk.</p>");
$db = mssql_select_db($dbase , $connection)or die (“Can’t select database.”);
?>

13

This is the equivalent to MySQL LIMIT in MsSQL
SELECT TOP 20
CAS.cad_steel_id,
CAS.cad_steel_no
FROM
CAD_STEEL CAS

14

You get extra points if you can do that and select the middle 20 rows from a database…

15

SELECT MIDDLE 20
CAS.cad_steel_id,
CAS.cad_steel_no
FROM
CAD_STEEL CAS

Extra points for Knurpht. If it doesn’t work this way, it should. Is there something like M$Fate ? :wink:

16

I am not sure what you want to retrieve, but it’s easy to limit a query if there’s a VIEW like this:

CREATE VIEW
CAS_NPRQTY
AS
SELECT
row_number() over (order by CAS.cad_steel_seq_no) AS ‘id’,
CAS.cad_steel_id,
CAS.cad_steel_seq_no
FROM
CAD_STEEL CAS
WHERE
CAS.cad_steel_type = ‘NPRQTY’

Then it’s easy to use the WHERE clause on the VIEW to select the records you need. However, this kind of question does not belong here. Or??

17

Got is working…rotfl!
It was the “LIMIT 0,15” - freeTDS does not understand it.

18

Yeah, row_number() over is the way in MS-SQL. I think its only become available in 2008 though, my 2005 can’t do it.

You’re right though, theres no need to discuss it here, and knurpht doesn’t get any points, because which MIDDLE 20 did he want :slight_smile:

I found the lack of LIMIT in MS-SQL a real pain when I started using it (I learned on MySQL). I was just pointing out that that may have been what was borking his query really…

Its mostly a pain (for me) for web pages, where you may have 600 items, and want to display them in pages of 20 at a time, and pass a ?page=n parameter. Easy in MySQL, harder in MS-SQL. Not sure is LIMIT is in the ANSI standard or not, but its a very nice addition if its not.

19

MsSQL 2005 does have the row_number() function. Look at this article: Row_Number() function in SQL Server 2005 — DatabaseJournal.com

20

Thanks, more than one way to achive similar results, more difficult in MS but it works…
I had a look at Comparison of different SQL implementations - very professional and a eye-opener for newbie’s like myself.

For joloevaa: the “$myServer = 10.10.0.28\sqlexpress” works 100% on SUSE11.x (just make sure freetds.conf (instance) is modified correctly) :wink: