mssql_query

add a note User Contributed Notes

huberkev11 at hotmail dot com
12-May-2006 11:47


Solution for the following Error:



Warning: mssql_query() [function.mssql-query]: message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. 



This is because you are using column types of like ntext instead of text.  There are 2 solutions.



1.  Change all ntext column types to text or

2.  Your query must look like: SELECT CAST(field1 AS TEXT) AS field1 FROM table

taavi at mydomainhere dot com
19-Apr-2006 12:49


watch out for mssql.timeout configuration value - if you dealing with scripts that take long time to run (backup managment, dts jobs), it might just stop running query in the middle without giving any php or sql error.

km at mcminndigital dot com
19-Apr-2006 02:17


Another note for the archives:



If for some reason you aren't on a windows platform (using FreeTDS etc), or on a older windows platform and are having a hard time with unicode errors with ntext and images there are two things to do:



1) make sure you odbc drivers are up to date

 :: or ::

2) make sure to convert any ntext or image fields in your select. If you need an image from a select then you are stuck with upgrading your odbc drivers if you are getting unicode errors.



Use mssql_field_type to get your field types however you want.



SELECT [Name], [UserID], [SomeValue], convert(TEXT, [Description]) FROM [MyDB.[dbo].[MyTable];

15-Apr-2006 07:35


If you are having problems with truncated text fields from mssql queries (pe. at 4096 characters), try some of the following:



in php.ini:

- mssql.textlimit = 65536

- mssql.textsize = 65536



in your php code, before your queries:

- mssql_query ( 'SET TEXTSIZE 65536' , $myConn );

- ini_set ( 'mssql.textlimit' , '65536' );

- ini_set ( 'mssql.textsize' , '65536' );

andrei_mole at yahoo dot com
07-Apr-2006 04:30


if anyone has faced a problem with displaying images from mssql image field it could be that they have an ole header put by sql.

here is an example. hope it helps:



<?php 

$db=mssql_connect("server","user","pass"); 

$d=mssql_select_db("NorthWind", $db); 

$sql = "SELECT Picture FROM Categories WHERE CategoryId=2"; 

$res = mssql_query($sql); 

$row = mssql_fetch_array($res); 

$data = $row['Picture']; 

header("Content-Type: image/bmp"); 

//remove the ole header

$data = substr($data,78); 

//print the pic in the browser

echo $data; 

mssql_close(); 

?>

augustinus at netsession dot net
02-Mar-2006 02:53


Regarding the note of jesse at lostangel dot net, written on 17-Sep-2002 03:33 implementing the mysql_insert_id function for MSSQL I have a remark:



The example hasn't worked on my database server (SQL Server 2000 Standard Edition with SP3a, english) so i just left of the exec statement.

Instead I used:



$result = mssql_query("INSERT INTO STUFF(gaga,otherGaga) VALUES ('hello','apple'); SELECT @@IDENTITY as insertId;");



list ($gagaId) = mssql_fetch_row($result);

Rizwan Khan
23-Feb-2006 06:47


MSSQL will not let you use TEXT data in the where clause, TEXT data is BLOG data, its max size is 2,147,483,647 bytes (each character occupies one byte).

The varchar and char can have max of 8000 characters, so you can use text data in a where clause if you convert as follows convert(varchar(8000),SOMETEXTDATA)



select * from tHistory where myNotes=convert(varchar(8000),SOMETEXTDATA)

Rizwan Khan
23-Feb-2006 06:37


Just explaining how to get date in YYYY-MM-DD format much easily, when you use convert function in SQL you usually give the format as one of the arguments. for example <br>

select convert(varchar(20),getdate(),20)<br>

select convert(varchar(20),getdate(),120)<br>

will both return dates in yyyy-mm-dd hh:mi:ss(24h) format. you can also use <br>

select convert(varchar(20),getdate(),21)<br>

select convert(varchar(20),getdate(),121)<br>

in yyyy-mm-dd hh:mi:ss.mmm(24h) formats.<br>

So now when you have the date as string from either of the four convert functions you can either take the left part of the string or use a substring function to get the date in YYYY-MM-DD format.<br>

following are few examples.<br>

select left(convert(varchar(20),getdate(),20),10)<br>

select left(convert(varchar(20),getdate(),120),10)<br>

select left(convert(varchar(20),getdate(),21),10)<br>

select left(convert(varchar(20),getdate(),121),10)<br>

select substring(convert(varchar(20),getdate(),20),1,10)<br>

select substring(convert(varchar(20),getdate(),20),1,10)<br>

select substring(convert(varchar(20),getdate(),120),1,10)<br>

select substring(convert(varchar(20),getdate(),21),1,10)<br>

select substring(convert(varchar(20),getdate(),121),1,10)<br>

tristanx3 at rogers dot com
30-Dec-2005 11:35


It would appear that MSSQL will not let you use fields of the TEXT data type in WHERE clauses.



I tried running a simple query to get account data for a particular user based on email address, but the command was not returning any data.  I finally realized that this was because the emailaddress column was of the data type TEXT.  When I changed the WHERE clause in the query to test against a name, which was of type VARCHAR, the query worked perfectly.



I overcame this problem by converting the TEXT field to VARCHAR in the WHERE clause, as noted below:



<?php

### Field 'emailaddress' is of data type TEXT in the SQL database



# This would not work!

$sql = "SELECT * FROM accounts WHERE emailaddress = 'test@test.com'";



# Converting the data type inline solved the problem

$sql = "SELECT * FROM accounts WHERE CONVERT( VARCHAR, emailaddress ) = 'test@test.com'";

?>



This seems to have solved my problem, although I am still unsure of the limits (if any) to this solution.

12-Sep-2005 06:18


To get column of type datetime as varchar in format: "YYYY-MM-DD" you should use:



$query = "SELECT    

   CAST(DATEPART(YYYY,row_date) AS CHAR(4)) + '-' 

     + RIGHT(CAST(100+DATEPART(MM,row_date) AS CHAR(3)),2) + '-'

     + RIGHT(CAST(100+DATEPART(DD,row_date) AS CHAR(3)),2) + '' AS row_date,

            FROM TABLENAME WHERE id = '$id'";



(Where "row_date" is of type datetime)

12-Sep-2005 04:32


Another way how to use dates in queries:



$createdate="2005-01-30";



$query = "

DECLARE @newdate datetime;

SELECT @newdate=convert(varchar(100), '$createdate', 102);

INSERT INTO TABLE(NUMBER, DATE) VALUES ('$num', @newdate);"



and fire the query....

php at syntax101 dot com
08-Jul-2005 11:40


On certain server setups simply replacing a single quote with two single quotes will result in:

you\''d 



I use the following function for single quote escaping quote for MSsql:



function escapeSingleQuotes($string){

    //escapse single quotes

    $singQuotePattern = "'";

    $singQuoteReplace = "''";

    return(stripslashes(eregi_replace($singQuotePattern, $singQuoteReplace, $string)));

}

php at syntax101 dot com
08-Jul-2005 11:39


On certain server setups simple replacing a single quote with two single quotes will result in:

you\''d 



I use the following function for single quote escaping quote for MSsql:



function escapeSingleQuotes($string){

    //escapse single quotes

    $singQuotePattern = "'";

    $singQuoteReplace = "''";

    return(stripslashes(eregi_replace($singQuotePattern, $singQuoteReplace, $string)));

}

rzachris at yahoo dot com
20-May-2005 04:25


If you're having problems trying to get data stored in a varchar column with more than 255 chars use CONVERT to change the data type to TEXT like this:



$query=mssql_query("SELECT table.id, CONVERT(TEXT, table.comment) AS comment FROM table");



Don't forget to set name for the column (AS comment) or you won't be able to retrieve the data ...

Ben Mullins
01-Feb-2005 10:11


Although mssql_query does not support datetime paramaters with stored procedures, there is a simple way of getting around it.



Bind the parameter as a string i.e. SQLVARCHAR or SQLCHAR 



mssql_bind($stmt, "@JoinDate", value, SQLVARCHAR, false,null,19);



and then use the sql server function CONVERT() in your stored procedure to convert the parameter to a datetime variable before using it.



SELECT @JD = convert(datetime,@JoinDate,103)

warwick dot barnes at aad dot gov dot au
12-Jan-2005 08:13


Using PHP 4.3.9 and MSSQL Library 7.0, when I try to SELECT text from a column (field) defined as VARCHAR 8000 I get only the first 255 characters of the text - it's trucated for no apparent reason.



To get round this I changed the column type from VARCHAR 8000 to TEXT, but then the output was trucated to 4096 characters.



To fix this I changed two values in PHP.INI:



mssql.textlimit = 16384

mssql.textsize = 16384



Now my text is trucated to 16384 characters, which is big enough for me - but you can apparently use a value as large as 2147483647.

roger4a45 at yahoo dot es
28-Oct-2004 05:15


Using Aplication Role with PHP



Introduction



Aplication Role is a Microsoft SQL Server 2000 feature that allow to make control what "source" use your data. It means that you can allow select, inset or update operation from your PHP code and deny it from and ODBC source, Microsoft Access or any kind of application that want to use your data.



Scenario:



Imagina you have a DAtabase named MYDB. This DB has three table. TABLEA, TABLEB and TABLEC. Imagine that your user named 'nemesis' can access to TABLEA from anywhere but you want that from table B and C access from your PHP code.



Follow this steps



[From your MSSQL Administrator]



1 -. From your Database MYDB create a new role (Standard Role). Insert this user inside this role.

2 -. Edit permisions and deny any operation at TABLEB and TABLEC.

3 -. Create a new Role (Aplication Role). For intance  it named 'myaccess' with a password 'anypassword'.

4 -. Edit permisions and allow any operation you wish at TABLEB and TABLEC



[From your source] (I don't include any control errors to simplify source)



$s = mssql_connect('MYSERVER','nemesis','nemesispassword');

$b = mssql_select_db('MYDB',$s);



//This one activate application role. Any user permision are 

//ignored. User permision are override with application role

// permisions.



$query = "EXEC sp_setapprole 'myaccess', 'anypassword'";

$b = mssql_query($query);

$result = mssql_query('SELECT * FROM TABLEB,$s);

[...]



Note: If you kill "$query = ..." you will find out that SELECT fails. If you insert that line $quey will be succeed. Now, nemesis only can take data from TABLEB and TABLEC through your PHP code. If he/She try to use that data through ODBC driver then he can not do it.



NOte: Application role drops when you make a mssql_close.

dennis at -nospam- darknoise d0t de
27-Sep-2004 08:02


In response to post from jesse at lostangel dot net from 18-Sep-2002:



INSERT ...

SELECT @@IDENTITY 

may NOT work as desired! 

While running multiple queries at the same time, you will get the last identity value generated, somewhere in your database.



If you need to know the identity value generated by your INSERT statement, use SCOPE_IDENTITY().

aleks [aleks AT linuxgalaxy DOT org]
17-Apr-2004 04:39


Just in case anyone is having problems with mssql_query calling a procedure, where the procedure has multiple SELECTs, INSERTs and/or UPDATEs and some do not return any kind of a result, but some do.

When trying to run mssql_query in that case an error: "Warning: mssql_fetch_array(): 1 is not a Sybase result index in ..." would result, or if one uses something like "SELECT @@IDENTITY" to return something for those statements an error: "Expected dbnextrow() to return NO_MORE_ROWS." would result.

A simple fix for the error(s) is to use "SET NOCOUNT ON" statement before any statements that do not return anything and then "SET NOCOUNT OFF" before any statements that do.



--Aleks

labrat457_minusthispart!_*-*_!yahoo.com
05-Feb-2004 02:50


You can run a stored proceedure AND get back a result set from mssql_query().  I figured this little trick out when I realized I couldn't use the mssql_init() function group for my stored procedure stuff as I needed datetime variables to be passed, and they are not supported.  However, you can "encapsulate" a snippet of stored procedure within a normal query that can be executed via mssql_query() and have a result value returned.  For example, here's a query which passes a store's local date and time to a stored procedure that converts it to the GMT values that is used internally by the rest of the database structure:

$temptime=time();

$storeid = 68;

$deptid = 70;

$StartDate = strftime("%b %d %Y %H:%M:%S",$temptime);

$spquery="

BEGIN

DECLARE @date datetime,

    @GMreturn datetime

SELECT @date='$StartDate'

execute TZ_toGMT '$storeid','$deptid',@date,@GMreturn OUTPUT

SELECT @GMreturn

END";



Now, when $spquery is passed to mssql_query(), it will return with a value for @GMreturn, which can be parsed out with mssql_fetch_array() as with any other query.

papaggel at csd dot uoc dot gr
21-Nov-2003 07:16


If you try to INSERT a large string to a MSSQLServer 2000 table attribute of type nvarchar/varchar/text/nchar/char, but it is limited to the first 256 characters do the following:



1. Open Tools->SQL Query Analyzer

2. In SQL Query Analyzer 

    i.   Go to Tools->Options

    ii.  Select the "Results" tab

    iii. Change "Maximum characters per column" to something else

Me
30-Aug-2003 03:53


Kelsey made a note that the only character that MSSQL needs "escaping" is the single quote ', and it done by using two single quotes ''.  You will want to make sure that, when using strings, you contain the strings in single quotes, since you can't escape double quotes.



In addition, you won't find a mssql_escape_string() function (though there are for other DB's, i.e. mysql_escape_string()), but using:



$escapedString = str_replace("'","''",$stringToEscape);



Will accomplish the same thing.

jagos at ebrno dot net
24-Jul-2003 07:27


This's note about mssql and truncating binary output from database (mostly image ...), i spent about 2 days tuning this stuff and fortunately i made the hit ... 

    so if you're experiencing truncates of your binary data read from mssql database (it looks like incomplete, broken or even no images) check mssql section of your php.ini file and set values of mssql.textlimit and mssql.textsize variables to their maximum (2147483647) or at least bigger size than the default is ... so i hope it helps a bit, have a good time

arnarb at oddi dot is
30-Apr-2003 09:16


If you'd like to store binary data, such as an image, in MSSQL, it's common to have problems with addslashes and co.



This is because the MSSQL parser makes a clear distinction between binary an character constants. You can therefore not easilly insert binary data with "column = '$data'" syntax like in MySQL and others.



The MSSQL documentation states that binary constants should be represented by their unquoted hexadecimal byte-string. That is.. to set the binary column "col" to contain the bytes 0x12, 0x65 and 0x35 you shold do "col = 0x126535" in you query.



I've successfully stored and retrieved jpeg images in a column with the "image" datatype. Here's how:



// storing a file

$datastring = file_get_contents("img.jpg");

$data = unpack("H*hex", $datastring);

mssql_query("insert into images (name, data)

         values ('img.jpg', 0x".$data['hex'].")");



// retrieving

$result = mssql_query("select data from images where name = 'img.jpg'");

$row = mssql_fetch_assoc($result);

header("Content-type: image/jpeg;");

echo $row['data'];



As you can see there is nothing to do with the image on they way out, just blurb out the buffer your recieve as with any other field type.

AzRaH
01-Mar-2003 06:15


If you are experiencing TRUNCATING issues trying to return more than 4096 bytes on a VARCHAR field try converting it to text:



CONVERT(TEXT, Yourfield) 



EX:

$sql->db_select("SELECT cc_main.cc_entry_id, CONVERT(TEXT, cc_main.comment) FROM cc_main");

wind at wag dot nl
15-Nov-2002 04:55


Had a similar problem to Jesse



What I experienced with Microsoft SQLserver 2000 and PHP 4.1.2 was that if you execute a query consisting of multiple queries separated by a semicolon with mssql_query is that the queries themselves are executed but the connection is dropped immediately after that. It seems that the phpdriver doesn't count on this.

The EXEC solution (or maybe sp_executesql) works fine in these cases because to the db-library it is one statement.

php at netznacht dot de
11-Oct-2002 07:12


Just tried to give a string:



Welcome at Mike's   to the SQL Server:



He misinterpreted the \' as the end of the String.

MySQL will resolve  \' and not think that the String has ended.



You should replace the \' before constructing the MSSQL Query string.

jesse at lostangel dot net
18-Sep-2002 06:33


While trying to return a @@IDENTITY after an INSERT statement, the only way I could get a valid return result was to encapsulate the query within an EXEC like so:



    $result = mssql_query("

        exec(\"

        INSERT INTO Files 

            (ownerId, name, sizeKB, path) 

            VALUES ('$contactId', '$userfile_name', '$filesize', '$path')

        SELECT @@IDENTITY as fileId\")

        ");

    list($fileId) = mssql_fetch_row($result);



This returned the appropriate @@IDENTITY in a valid result set.  Not sure if mssql supports multiple inline commands or not.  But that assumption would back the useage of the EXEC command in order to execute these properly.

adamhooper at videotron dot ca
16-Jul-2002 02:15


As with all _query() functions, if there is no database connection and mssql_connect() fails, the return value will be NULL (not false).

cusaacb at squared dot com
21-Jun-2002 03:04


Using the latest version of freetds with support for tds ver 7.0.  I could not get more than 256 characters on varchar until I set the environment variable TDSVER=70.  



Then it correctly returns all of the field.

arthur dot mcgibbon at btinternet dot com
09-Apr-2002 11:01


The mssql_execute etc. commands for stored procs do not seem to support a DATETIME type.

This means using mssql_query.

Now problems occur when you want to use parameters that have Input values and Output values.  SQL Server refuses to acknowledge the following...



declare @ret int, @i int

select @i = 25

exec @ret = testsp @Param1 = @i output

select @ret, @i



It doesn't like 2 select statements, even when the first is changed to "set".



To get round this, put the whole query into an exec command.

e.g.

mssql_query("exec('declare @ret int, @i int

select @i = 25

exec @ret = testsp @Param1 = @i output

select @ret, @i')"



This seems to work fine.

pbl1 at free dot fr
29-Nov-2001 01:17


Environment :Windows2000 SQLServer2000


Syntax: If you put between [] the name of the table in which you want to perform the selection it works, otherwise it is not running.





$query = "SELECT * FROM [TABLENAME]"; 


mssql_query($query)

wraithgar at hotmail dot com
01-Aug-2001 04:49


We are using --with-sybase=/opt/sybase-11.9.2/





We had a problem at first getting mssql results w/ a text field to return anything more than 4096 bytes.


The mssql.textlimit and mssql.textsize parameters in php.ini had no effect on this.


Turns out you can control this from the mssql end w/ one simple query


mssql_query("set textsize 65536");





Of course, 65536 is just an example, use whatever value you need to get back as a max.





We tested it and the setting stayed through the whole connection, no need to run that query before EVERY one of your real queries.  However, it didn't seem to retain across sessions.. (sorry, can't simply run the query once and forget about it)





Hope this helps others who are banging their head on a wall like we were.

mark at gamezone dot co dot za
28-Nov-2000 02:58


With freetds 4.2 (on freebsd) connecting to an MS-SQL7 db, I encountered the problem where certain INSERT and UPDATE queries were not returning resulting in the script timing out.





It seems as though a query which doesn't return column data, just doesn't return.





To remedy this I append "; SELECT @@IDENTITY" or "; SELECT 1" or any arbitrary query to my normal insert/update queries, this causes it to return arbitrary column data after the normal query and thus give control back to php.





-Mark

slick at 4servicesupport dot com
25-Jul-2000 11:15


jake.b is right.  By getting freetds from http://www.freetds.org and configuring PHP4 --with-sybase=<freetdsdirectory>, you can connect to an MSSQL7 server from Linux.  Then I was able to execute stored procedures by flinging the entire query string to mssql_query.  Using the returned pointer, I was able to retrieve data into an array using mssql_fetch_array.

add a note