User Contributed Notes
Microsoft SQL Server functions |
 add a note |
camber at ais dot org
11-Feb-2000 10:02 |
|
To access MS SQL from Unix you may use Sybase's OpenClient which happens to
work (depending on platform, 7.0 needs SP1 and big endian platforms have
trouble) or FreeTDS (www.freetds.org) which is still alpha-ish but
directly supports MS SQL.
|
|
jhamann at sfresearch dot com
21-Apr-2000 04:49 |
|
I have just finished setting up php4 with freetds to connect to a MSSQL 7
server. I used freetds 5.0 and configured with --with-tdsver=7.0
|
|
caspian_ at hotmail dot com
07-Jul-2000 05:14 |
|
To compile PHP to support M$SQL on LINUX:
-Download FreeTDS:
www.freetds.org
and compile as required. Make sure to compile with the
correct version of the protocol (read the readme)
-Compile PHP
with '--with-sybase=/usr/local/freetds' or wherever you put
freetds.
PHP will now support both mssql_*** and sybase_***
from Linux.. enjoy!
|
|
steeven at kali dot com dot cn
24-Dec-2000 10:07 |
|
IMPORTANT NOTEs:
1. results sets are not supported, only the first
result set can be retrieved. for example:
SELECT 1
SELECT
3,5
Only the first result set can be fetch. Use a Union
instead.
2. Each fetch to cursor will get a result set. Fetch a large
quantity row sets will raise the server's overhead on network. try other
way instead.
3. The very usefull advantage of ADO is that muti-result
set are supported. the core function is sp_cursorxxxxx. You can use it
with a muti-record set support client. I'm very disapointed that PHP has
not support it now.
|
|
myers at fil dot org
22-Feb-2001 01:03 |
|
Having trouble with pesky TEXT datatypes? Try the query 'set textsize
555555555;' to set the limit on how much a TEXT will
return.
Symptoms of the problem are hanging web browsers and
"FATAL: emalloc(): Unable to allocate -2147483648 bytes"
errors in apache's error.log
|
|
myers at fil dot org
22-Feb-2001 08:12 |
|
> Having trouble with pesky TEXT
> datatypes? Try the query 'set
> textsize 555555555;' to set the limit
> on how much a
TEXT will return.
I was trying to figure out why 555555555 works
and not anything higher. Then it occured to me ... the machine in
question has 512 MB of ram. The moral of the story is that you have to
adjust the size to how much memory you have and how much data you expect
to come out of the database.
|
|
netservice at 21cn dot com
19-Apr-2001 04:01 |
|
on Linux,
FreeTDS are not support GB2312 charset, use Sybase-11.9.2
client tools is ok.
|
|
lorraine at ksd dot net
13-Jun-2001 05:10 |
|
for anyone trying to connect to mssql on win nt using php4 if the options
listed here don't work try donloading the latest dll's for extensions. the
version 7 dll we tried to use would not work
|
|
|
21-Aug-2001 12:01 |
|
> Having trouble with pesky TEXT datatypes? Try the query 'set textsize
555555555;'
> to set the limit on how much a TEXT will
return.
I was indeed having troubles with those pesky TEXT
datatypes, this query alone didn't help either, i had to put
mssql.textlimit = 65536
and
mssql.textsize =
65536
in php.ini and then restart _the WHOLE_ of IIS (not just
the website) to get it working (and don't forget to use that query
too)
(That's on Win32 + IIS)
|
|
php at fireball88 dot de
31-Jan-2002 10:19 |
|
Easy Oracle-Connection with my Class for Oracle/mySQL/MSSQL!
my
cDBC-Klass is free and with Documentation and sources.
visit my
Site:
|
|
gpeipman at yahoo dot com
11-Feb-2002 06:05 |
|
Library php_mssql.dll is where has to be, but PHP cannot load it. SQL
Server drivers are installed and MSDE 2000 is running on my
machine.
X-Powered-By: PHP/4.1.1 Content-type: text/html
PHP
Warning: Unable to load dynamic library 'c:\program
files\php\extensions\php_mssql.dll' - The specified module could not be
found. in Unknown on line 0
The line of code I used, is the
following:
<?
$db =
mssql_connect("MyComp","myUserName","myPassword");
?>
|
|
nospam at nospam dot com
21-Feb-2002 07:00 |
|
If you have some trouble with FreeTDS and TEXT limit (default 4096)
and
set TEXSIZE doesn't work and you can't even use mssql_init to use
stored proc.
use a simple query with "exec
stord_proced_name"..
and create the store proc with
set
textsize ...
your data processing (like select...)
at the end
enter
set textsize 0..
and it will work perfectly !!!!
|
|
elee at kinwai dot net
15-Apr-2002 05:29 |
|
The belows procedures works on the following configuration: Mandrake linux
8.2,MS Sql server 2000, windows 2000 server, PHP ver 4.12, apache
1.3.24,Freetds 0.53.
Configure Freetds
1. Download Freetds 0.53
at www.freetds.org
2. ./configure --prefix=/usr/local/freetds
--with-tdsver=7.0
3. make
4. make install
5. Edit
/usr/local/freetds/etc/freetds.conf
6. Add
[MyServer70]
��DSN for PHP
host = 10.23.52.114 ��MS SQL Server IP
port =
1433 ��MS SQL Default Port
tds version = 7.0 ��MS SQL 2000
Version No.
client charset = UTF-8 ��Charset Encoding, it can be
omitted if you don't want to disply in unicode
Configure PHP
4.12
1. Download PHP4 from www.php.net
2. ./configure
--with-sybase=/usr/local/freetds --with-apache=../{Apache Path}
--enable-track-vars
3. copy all files in /usr/local/freetds/lib/ to
/usr/lib
4. make
5. make install
Configure Apache
1.3.24
1. ./configure
--activate-module=src/modules/php4/libphp4.a
2. make
3. make
install
4. cp php.ini-dist /usr/local/lib/php.ini
5. Edit Your
httpd.conf or srm.conf file and add;
AddType application/x-httpd-php
.php
6. /usr/local/apache/bin/apachectl start
Feel free to email
to us @ [email protected] or [email protected]
German Chu & Eagle
Lee
|
|
jd at keane dot com
24-Apr-2002 09:36 |
|
Connecting to MSSQL 7 on UNIX/Apache/php4.1.2. I followed a bunch of
instructions below from folks using Linux and other configurations...so
give this a try, it may work.
download freetds from
www.freetds.org
configure arguments --with-prefix=/usr/local/freetds
--with-tdsver=7.0
edit freetds.conf in
/usr/local/freetds/etc/freetds.conf
[MyServer70]
host
= XX.XX.XX.XXX(your MSSQL IP)
port = 1433
tds version = 7.0
client charset = UTF-8
reconfigure PHP using
--with-sybase=/usr/local/freetds/ and all of your normal args.
restart apache and it works...well it did for me. Good
luck
jd
|
|
dalfarop at bsch dot com dot pe
05-Jul-2002 05:23 |
|
NEED HELP
having problems with php_mssql.dll
under MSWINDOWS 2000
and apache 1.3.24
if i enable the extension=php_mssql.dll the i receive
an error message
"mssl:Unable to initialize
module"
"Module compiled with
API=20010901,debug=0,thread-safety=1"
"PHP compiled with
API=20020429,debug=0,thread-safety=1"
and if i disable
extension=php_mssql.dll then i receive in my
browser:
"X-Powered-By: PHP/4.2.1 Content-type: text/html
Fatal error: Call to undefined function:
mssql_connect()"
dONT KNOW WHAT TO DO
i think its my dll
that its not working
where can i get the latest
thanks for your
help
Please mail me to [email protected]
David
|
|
christian at wenz dot org
10-Jul-2002 07:18 |
|
> "mssl:Unable to initialize module"
> "Module
compiled with
API=20010901,debug=0,thread-safety=1"
>
"PHP compiled with
API=20020429,debug=0,thread-safety=1"
as you can see, the
php_mssql.dll and PHP itself have different versions. Maybe there is an
old php_mssql.dll lying around in \windows\system(32) or so...
|
|
a dot pfister at jenbacher dot com
05-Aug-2002 05:11 |
|
Hello!
Just a Hint:
You have to install the Connectivity-Part
from the SQl-Server CD or the MSDE to use the php_mssql.dll.
|
|
php at zfi dot org dot uk
16-Aug-2002 12:58 |
|
We observed that datetime values returned through the mssql functions were
in a different format to that observed in Enterprise Manager / Query
Analyzer.
i.e. EM said: 2002-08-16 09:58:06
PHP-MSSQL said: 16
Aug 2002 9:58
The following php.ini setting stopped this from
happening:
mssql.datetimeconvert = Off
System: PHP 4.2.2,
SQL Server 2000, Win 2000 Server
|
|
scott at datalink dot net dot au
28-Aug-2002 09:38 |
|
If you are running IIS/MSSQL/PHP with PHP in CGI mode and are getting a
"CGI Error" message from IIS, read on...
A bug exists in
the MSSQL driver (possibly on Microsoft's side) that will cause this
problem on high performance servers running PHP in CGI mode, especially if
your app uses frames. It is related to lots of simultaneous connections
to the SQL server from PHP.
A bugnote on the PHP database had
lots of input, which can be read here:
A
solution was found, which involves tuning the Win2k or WinXP server for
'Forground Applications'. This eliminates the problem
completely.
It seems that CGI applications under IIS are launched
into user space, owned by the IUSR_* user. So tuning the server to
process forground apps makes sense.
To do this, right-click 'My
Computer', select 'Properties', select the 'Performance' tab, and then
select 'Forground Applications' and click 'OK'.
|
|
jcamam at yahoo dot es
15-Nov-2002 02:25 |
|
I had problems with ntext data types: only first 'n' characters were
returned. I solved it using a cast:
SELECT cast ( column_name as
text ) as column_name ...
It looks like it returns text value as
varchar type, so it truncates the result.
Other limitation is the
next: TEXTSIZE variable sets the max length of column content that should
return
mssql_query ("set TEXTSIZE 100000;");
Hope
this helps someone.
Josep Campillo
|
|
wolff at 21st dot de
17-Dec-2002 11:08 |
|
Please note that although it�s stated everywhere that you can use
--whith-sybase to make the mssql_* functions available on *nix platforms,
this doesn�t mean that all functions of the MSSQL-Extension are really
available.
In fact, only those functions that are provided by the
Sybase extension are mapped to the corresponding mssql_* function names,
but no more. Therefore, a large part of the API is unavailable on
Unix/Linux.
Functions that do not exist are for example
mssql_fetch_assoc(), mssql_field_name(), mssql_field_length() and
mssql_field_type().
As a result of this, if you use PEAR::DB�s
MSSQL driver class, you can�t use the tableInfo() method. Automated
insert/update query generation using autoExecute() won�t work as
well.
There�s a new version of the MSSQL extension in CVS now that
will also compile under Unix, making the full API available - but it�s not
likely that it will make it into the 4.3.0 release.
|
|
james at adtekh dot com
24-Dec-2002 12:21 |
|
If you find that you are not getting the whole value of a text field back
from the database, you have probably reached the default 4kb limit. This
can be fixed by changing the mssql.textlimit and mssql.textsize values to
something larger than 4096. I actually rewrote an entire section of code
before realising it wasn't my code that was causing the problem, but this
limit!
|
|
till'at'klimpong'dot'com
27-Dec-2002 11:08 |
|
"--with-mssql" is available as of 4.3.0 (not earlier!)
|
|
peter at schumacher dot dk
08-Jan-2003 05:19 |
|
It's possible to change the default connection type from Named Pipes to
TCP/IP
WITHOUT installing the Connectivity-Part from the MSSQL7
Server-cd,
The
registry-key,
[HKEY_LOCAL_MACHINE\SOFTWARE\
Microsoft\MSSQLServer\Client\ConnectTo]
holds the current connection-type.
Initially it's set to
"DSQUERY"="DBNMPNTW" which corresponds to Named
Pipes.
Change it to "DSQUERY"="DBMSSOCN" which
corresponds to TCP/IP.
(Only tested on Win2KPro)
|
|
rwcatalano - the answer shop
14-Jan-2003 04:44 |
|
Web Server Type: Windows 2000 Server
SQL Server Type: Version
7
ERROR:
Unable to load dynamic library '.\php_mssql.dll' - The
specified module could not be found. in Unknown on line 0
ERROR:
Unable to load dynamic library '.\php_mssql.dll' - The
specified procedure could not be found. in Unknown on line 0
How
To Fix:
First in most circumstances you are getting this error because
you have a older version of PHP installed (Such as 4.2.3), but you are
trying to use a newer php_mssql.dll file (Such as the one that comes with
4.3.0)
Step 1:
Create a file called info.php that contains the
single line:
<?php phpinfo(); ?>
-executing this file in
your web browser will tell you what version of PHP you are running and it
will through a couple of visual alerts displaying the errors mentioned
above.
Step 2:
If you installed PHP using the installer you will
have to go back to the php site and download the binary .zip file for
Windows, for the version of PHP you currently have installed.
Step
3:
Open the zip file (you don't have to unzip the whole file becuase
you are just looking to copy one (1) dll file,
"php_mssql.dll".
Step 4:
Pending on whether you are
running a default install, copy the "php_mssql.php" file to the
following directory "c:\winnt\"
Step 5:
Double check
that the following file
exists:
c:\winnt\system32\ntwdblib.dll
Step 6:
Edit your
php.ini, search for the string "php_mssql.dll", and remove the
semi-colon (;) from the front of the value.
Step 7:
Start and
stop IIS Services by clicking the windows "start" button in the
bottom left corner, select run, and type the command:
net stop
iisadmin
(Type y at the promt)
When the command window dissapears,
click the windows "start" button in the bottom left corner
again, select run, and type the command:
net start w3svc
Step
8:
Run your info.php file again. If you did not get any errors, you
are good to go; if you get the error again or your error looks like this:
'c:\program files\php\extensions\php_mssql.dll',
then you need to move
your "c:\winnt\php_mssql.dll" to the directory mentioned in the
error message, for this example: "c:\program
files\php\extensions\".
Good Luck!!
|
|
Ernesto Domato
27-Jan-2003 07:42 |
|
If you want to use the FreeTDS library under Linux to access the functions
for Microsoft SQL Server, use the --with-sybase=/path/to/freetds instead
of --with-mssql=/path/to/freetds as mentioned on the manual.
This
will solve the problem that I had with mssql_query that does a
segmentation fault.
You can also check bug #20935 ()
for more information.
This note is for PHP
4.3.0
Greettings.
Ernesto
|
|
larsrohr at eecs dot berkeley dot edu
28-Jan-2003 08:30 |
|
Apache/2.0.44 (Unix) mod_ssl/2.0.44 OpenSSL/0.9.7 PHP/4.3.0
We
compiled PHP with "--with-mssql=/usr/local/freetds", and found
that we could only mssql_connect using the IP:port syntax, not the name we
defined in freetds.conf.
To fix this, we added to the
/usr/local/apache2/bin/envvars
file:
FREETDSCONF="/usr/local/freetds/etc/freetds.conf"
export
FREETDSCONF
|
|
ahopper at agentmaster dot com
04-Mar-2003 03:00 |
|
I've repeatedly run into problems with the 255 character limitation in
PHP's MSSQL extension, so I threw together this stored procedure to break
up data greater than 255 characters into a series of columns. I hope this
helps those looking to pull binary data from MSSQL
Server.
____________________________________
Example
Usage:
<?
mssql_connect("localhost","agentmaster","");
mssql_select_db("agentmaster");
$query
= mssql_query("sp_GetStuff
@table='pics',@field='medium_photo',@where='id=462891'");
$cols =
mssql_fetch_row($query);
foreach($cols as $chunk)
{
$data.=
$chunk;
}
echo
$data;
mssql_close();
?>
____________________________________
Stored
Procedure:
CREATE PROCEDURE sp_GetStuff
(
@table
VARCHAR(100),
@field VARCHAR(100),
@where
VARCHAR(100)
)
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS
ON
DECLARE @sql VARCHAR(8000)
SET @sql = '
DECLARE
@DataLength INT,
@bytes INT,
@i INT,
@start
INT,
@sql VARCHAR(8000),
@sql1 VARCHAR(8000),
@sql2
VARCHAR(8000),
@sql3 VARCHAR(8000),
@sql4
VARCHAR(8000),
@sql5 VARCHAR(8000),
@holder
VARCHAR(8000)
SET @DataLength = 0
SET @DataLength = (SELECT
DATALENGTH(' + @field + ') as dataLength FROM ' + @table + ' WHERE ' +
@where + ')
IF(@DataLength > 0)
BEGIN
SET @sql1 = ''SELECT
''
SET @sql2 = ''''
SET @sql3 = ''''
SET @sql4 = ''''
SET
@sql5 = ''''
SET @bytes = 0
SET @i = 0
WHILE @bytes <=
@DataLength
BEGIN
SET @start = @bytes
IF(@bytes >
0)
SET @holder = '',''
ELSE
SET @holder = ''''
SET
@bytes = @bytes + 255
SET @i = @i + 1
SET @holder = @holder +
''SUBSTRING(' + @field + ','' + convert(varchar(5),@start) + '',255) as
c'' + CONVERT(varchar(5),@i)
IF (LEN(@sql1) + LEN(@holder) <
4000)
SET @sql1 = @sql1 + @holder
IF ((LEN(@sql2) +
LEN(@holder) < 4000) AND (LEN(@sql1) + LEN(@holder) >
4000))
SET @sql2 = @sql2 + @holder
IF ((LEN(@sql3) +
LEN(@holder)) < 4000) AND ((LEN(@sql1) + LEN(@sql2) + LEN(@holder) >
8000))
SET @sql3 = @sql3 + @holder
IF((LEN(@sql4) +
LEN(@holder)) < 4000) AND ((LEN(@sql1) + LEN(@sql2) + LEN(@sql3) +
LEN(@holder) > 12000))
SET @sql4 = @sql4 + @holder
IF
((LEN(@sql5) + LEN(@holder)) < 4000) AND ((LEN(@sql1) + LEN(@sql2) +
LEN(@sql3) + LEN(@sql4) + LEN(@holder) > 16000))
SET @sql5 = @sql5
+ @holder
END
exec(@sql1 + @sql2 + @sql3 + @sql4 +
@sql5 + '' FROM ' + @table + ' WHERE ' + @where +
''')
END'
exec(@sql)
|
|
dave at purevanity dot net
12-Mar-2003 07:46 |
|
Just a quick note for all you new FreeTDS users;
You need to compile
your FreeTDS based on what MsSQL server version you are using. When
compiling use the flags '--with-tdsver=' and then append the MsSQL version
you are using. For MsSQL 2000, its 7.0.
|
|
user from .ee
27-Mar-2003 01:56 |
|
i could not connect to mssql database (redhat -> win2000) without port
arter server ip (it was written in sybase conf but it still didn't
work):
$con=mssql_connect('192.168.0.1:1433','user','passwd');
hope
it is useful for someone.
thanks.
|
|
jerry dot wilborn at fast dot net
30-Mar-2003 06:16 |
|
be aware that compiling with --with-sybase=DIR may get your mssql functions
working but variables like "MSSQL_ASSOC" when trying to fetch an
array won't work. the mssql functions are mearly aliases to their
respective sybase components (grep the sybase source for
mssql).
what does this mean? if you're 'while'ing thru a fetched
row and wanting to get "list($column_name, $column_value) =
each($row)" then you need to test $column_name such as: if
(is_int($column_name)) continue;
since the sybase fetch row gives
both numeric and textual representations of the fetched data and has no
ability to do one or the other.
- jerry
|
|
lorenthal at hotmail dot com
01-Apr-2003 10:47 |
|
I had a problem connecting to any MS SQL Server. I configured everything OK
but I couldn't connect to any server (I received the "MS SQL: Unable
to connect to server: 192.168.0.25" message).
It worked fine
in one computer but it didn't work in another computer. After some tests,
I searched the web for a newer version of the "ntwdblib.dll"
file, and I found one with 348KB size, while the one I had was only 273KB
size.
After replacing the old file with the newer, everything went
Ok.
You can find this file in the following address:
atw/1cAC5A1E16/NCxILGZ0cA/
ftp/ftp.sunet.se/pubh/vendor/
microsoft/Products/Oemtest/
v1.1/WOSTest/Tools/ntwdblib.dll
(note:
I had to cut the address above into five lines to reduce the length of the
line).
Hope it's useful to you,
Sergi Miranda
|
|
tord
04-Apr-2003 12:56 |
|
If you have problems connecting to a MS SQL server, one reason may be that
the MS SQL server is configured to use a protocol other then Named
Pipes.
Install the MS SQL client software and run the client
configuration tool. Add an Alias to the database server and specify the
type of protocol the server is configured to (TCP/IP in my case).
|
|
|
add a note |
PHP Manual
Function Reference
아파치
mime_content_type