Need Help

Stay Secure

Get Connected

Need Technology?

---

Incoming Students

Students

Faculty/Staff

---

Office of the Vice President for IT and CIO

myIdentity

Other IT Resources

About University IT

University IT Home

University IT » Web Services » Help » MsSql » Limitations

Using PHP to Connect to Microsoft SQL Server

Limitations

If you are familiar with using PHP to interact with MySQL databases, doing the same with MS SQL is not much different. However, there are two important limitations.

1. INSERT queries in MS SQL must be formed like the following example.

   INSERT INTO tablename (col1, col2, col3) VALUES(12, "Hello, World!", "test")

   By contrast, INSERT queries in MySQL can also be formed as follows. However, the following syntax will NOT work in MS SQL.

   INSERT INTO tablename SET
   	col1 = 12,
   	col2 = "Hello, World!",
   	col3 = "test"

2. Grabbing certain data types from a table with a SELECT query will cause the PHP script to fail. For example, columns of the datatype decimal cause problems on our servers, so the following MS SQL query will fail when run from PHP.

   SELECT decimal_field FROM tablename

   Variables of one datatype can be converted to another. This is called casting, and the MS SQL function CAST will do this for us. The datatype real is compatible, so we can CAST the decimal column as real, and the script will not fail. The following is an example of a successful query.

   SELECT CAST(decimal_field AS real) FROM tablename

   Incompatible datatypes can be cast as compatible datatypes before they are sent to PHP to avoid script failures.

   • Incompatible numerical datatypes may be cast as real variables. The following example shows how to do this.

     SELECT CAST(fieldname AS real) FROM tablename

   • Incompatible non-numerical datatypes may be cast into the text datatype, as shown in the following example.

     SELECT CAST(fieldname AS text) FROM tablename

   • The CAST function can be used on individual columns in a list as the following example demonstrates.

     SELECT col1, CAST(col2 AS text), CAST(col3 AS real) FROM tablename

   Note that it is theoretically possible to cast a variable as any other datatype—not just real or text. However, problems arise when incompatible datatypes are returned to PHP.

   The following table lists all of the datatypes in MS SQL 2000 and 2005, along with any issues to keep in mind when working with them.

   Datatype	Notes
   bigint	Incompatible datatype. CAST as real.
   binary	Compatible datatype. Must be converted to another form for viewing. See the section on Binary Data for information on converting to hexadecimal or decimal form.
   bit	Compatible datatype. See the section on The bit datatype for more detail.
   char	Compatible datatype. Only the first 255 characters are returned.
   datetime	Compatible datatype.
   decimal	Incompatible datatype. CAST as real.
   float	Incompatible datatype. CAST as real.
   image	Compatible datatype. Seems to behave exactly like the datatype text in MS SQL Server 2000.
   int	Compatible datatype.
   money	Yes
   nchar	Compatible datatype. See the section on Unicode Datatypes for more information.
   ntext	Incompatible datatype. CAST as text.
   numeric	Incompatible datatype. CAST as real.
   nvarchar	Compatible datatype. See the section on Unicode Datatypes for more information.
   real	Compatible datatype.
   smalldatetime	Compatible datatype.
   smallint	Compatible datatype.
   smallmoney	Compatible datatype.
   text	Compatible datatype.
   timestamp	Compatible datatype. See the section on Binary Data for information on displaying a timestamp. NOTE: timestamp columns cannot be modified. UPDATE queries will return an error.
   tinyint	Compatible datatype.
   uniqueidentifier	Compatible datatype. NOTE: the output of a uniqueidentifier column from a MS SQL SELECT query can be misleading. The string PHP returns does not match what is inserted into the database.
   varbinary	Compatible datatype.
   varchar	Compatible datatype. Only the first 255 characters are returned.
   xml	Incompatible datatype. (The datatype xml is present in MS SQL Server 2005, but not in MS SQL Server 2000.)