Joseph Scott’s Blog

Earlier this month Microsoft contributed a SQL Server Driver for PHP. They even have a SQL Server and PHP blog and their SQL Server forum now lists as PHP in their client list. The blog has an interesting post on development of the driver.

So, what does this thing do? It is for PHP5 only (not a big deal considering PHP4 will only be getting security updates after this year) on Windows (this part is a bummer, but not surprising) to access SQL Server 2000 and 2005 (anyone running anything older than that in production?).

You could work with SQL Server from PHP prior to this using FreeTDS. I’ve used the mssql_* PHP functions backed by FreeTDS on FreeBSD for years so this new driver from Microsoft isn’t a major game changer at this point.

That said, I find this a pretty exciting development.

Microsoft is actually providing a PHP library to work with some of their own software. I think that is great, although the really huge win would have been if it was available for non-Microsoft operating systems. This is great stuff though, not just talk, but real live code. Combining this with better PHP on IIS support I have a glimmer of hope that we’ll see more good things from Microsoft for PHP.

There has been plenty of PR about Microsoft doing more in this area, but providing real code and tools is what we actually need.

Welcome to another episode of being caught off guard by strange string requirements. Today I discovered (the hard way of course) that Microsoft SQL Server 2000 doesn’t like to have dashes in database names. Fine, I probably should have used an underscore instead, but this was a database being used for something else that I just need to do some quick queries against in PHP.

I knew that the connection between the web server and the SQL Server systems was working because I had scripts talking to other SQL Server databases. My problem wasn’t connecting, mssql_connect() didn’t cause an error, it was selecting which database to use. Fortunately the answer was right there in the docs for mssql_select_db():

To escape the name of a database that contains spaces, hyphens (“-”), or any other exceptional characters, the database name must be enclosed in brackets, as is shown in the example, below. This technique must also be applied when selecting a database name that is also a reserved word (such as “primary”).

So just get in the habit of wrapping your SQL Server database name in brackets ( [ ] ) and you’ll be fine:

   $conn = mssql_connect('SQLSERVERHOST', 'username', 'password');
   mssql_select_db('[database-name]', $conn);

What I want to know is, why brackets around the whole name instead of traditional character escaping?

I recently had the need format a datetime field in SQL Server 2000. My first thought was to look for to_char(), which does the job in PostgreSQL and Oracle. No luck there because SQL Server doesn’t have a to_char() function. After some hunting around I found out about the convert() function. This does the same thing as to_char() (more or less) but instead of using traditional date formatting codes, “style codes”.

Yes, you read that right. Instead of being able to get a four digit year by specifying Y or YYYY, you have to look up the style code that matches the format you want. In my case I was look for DD MON YYYY format. This turns out to be style code 106. Here is an example:

SELECT CONVERT(VARCHAR(11), datetime_field, 106) AS formatted_date

Some time with Google turned up a page listing examples of various style codes. Here is another one about 40% down the page.

There is an alternative to convert(), datepart(). This allows you to pull out specific parts of the datetime field. So you could do the same thing as the convert() example above, but it would be much more verbose.

UPDATE Fri 7 Jul 2006 @ 11:30am : I really should have included a list at least some of the format codes in this post for future reference:

• 100 – mon dd yyyy hh:miAM (or PM)
• 101 – mm/dd/yy
• 106 – dd mon yy
• 108 – hh:mm:ss
• 109 – mon dd yyyy hh:mi:ss:mmmAM (or PM)
• 130 – dd mon yyyy hh:mi:ss:mmmAM (or PM)

Mangus Hagander has a detailed how to for replicating from MS SQL Server to PostgreSQL. It certainly isn’t perfect, but for those willing to go through the work this would be a very interesting way to add PostgreSQL to a SQL Server shop. I don’t plan on using this right now, but I’m definitely going to file this away as something with potential.

This morning I went on a hunt to find out where SQL Server 2000 keeps the view definitions for a database. I quickly came across information_schema feature (seems that having information_schema is part of the SQL92 standard) which exposes view details via information_schema.views. I was hoping that would be the end of my journey, but then I ran into a problem.

Go back and look at the details for the Information_Schema.Views, pay close attention to the VIEW_DEFINITION column. It turns out that if the view definition is more than 4000 characters then it returns NULL. Why they didn’t just make it a TEXT field I do not know. Of course one of the two view definitions that I was looking for was returning NULL. At least I had figured out why, now I needed to know how to get around it.

I knew I could not have been the only one to run into this, so it was back to searching for a solution. I eventually came across a post about sp_helptext that gets around the 4000 character limit. It turns out that sp_helptext will not only give you a view definition, but also triggers, functions, check constraints and stored procedures. Quite handy to have around. So I was able to get the view definition I was looking for (broken up across multiple rows) with:


EXEC sp_helptext 'my_view_name'


You’ll still have to put all the rows back together to get the complete view definition, but that it much better than just getting NULL. Of course if I could figure out what the Information_Schema.Views or sp_helptext is looking at to get the actual view definition I could do it myself. So far I haven’t been able to find that information, and I’m not sure that I’m going to spend much time looking since I have a workable solution at this point.

As an added bonus, go back to the Information_Schema.Views page again and take a look at the IS_UPDATABLE column notes. Turns out that it always returns NO.

While it was nice of Microsoft to include the Information_Schema.Views as part of SQL Server 2000, don’t depend on the data they provide, in some cases it will be wrong. I haven’t looked to see if this is something that has been fixed in SQL Server 2005.

UPDATE Tue 21 Mar 2006 @ 4:00pm: Turns out that getting the definitions of the Information_Schema.Views and sp_helptext is fairly simple. They are kept in the master database in SQL Server 2000. Connect to the master database and take a look at the views, you’ll see several that are owned by INFORMATION_SCHEMA. Then take a look at the listing for Stored Procedures. There are tons of sp_* procedures, one of which is sp_helptext. This lead me to running exec sp_helptext 'sp_helptext' to get the definition of sp_helptext. You can also look at it by looking at the properties of sp_helptext, but you’ll be doing a lot scrolling in a little tiny window. Ick.

After looking at the definitions of both I think I’m going to stick with sp_helptext.

UPDATE Tue 21 Mar 2006 @ 4:40pm: Here is the MSDN info on INFORMATION_SCHEMA and more specifically INFORMATION_SCHEMA.VIEWS which includes notes about the limit of 4000 characters to view_definition and that is_updatable always returns no.

We are purchasing some software at work that uses SQL Server 2000 as a back end. I’ve been bringing those systems up and relearning SQL Server since I haven’t look at it in more than five years. We’ll be importing some existing data for this software so I’ve started writing up a few scripts. I’m using PHP with ADOdb for database access. For the most part things have gone well, with some small expections/gotchas. Fortunately none of the problems I noticed were hard to fix, but they did seem a little odd sometimes. Here are some notes on my experiences with this process thus far:

1. FreeTDS – Build

   In order to get PHP talk to SQL Server 2000 I’m using FreeTDS on a FreeBSD system. The first thing to note is that SQL Server 2000 uses 8.0 TDS. When building from FreeTDS from the ports collection be sure to override the default, which is 5.0. You’ll also need to enable msdblib in FreeTDS, so make sure that WITH_MSDBLIB is enabled during the build also. Then all that is needed for PHP4 to speak to SQL Server is the php4-mssql port.

2. FreeTDS – Config

   In recent versions of FreeTDS they’ve started using a freetds.conf file to configure connections. There is an example in there for connecting to SQL Server 2000, here is what I ended up using:
   
[SQL2K]
host = xxx.xxx.xxx.xx
port = 1433
tds version = 8.0
; dump file = /var/log/freetds.log
; dump file append = yes
; debug level = 99


   I had a few problems connecting initially, so it was helpful to run with debug on until I got things straightened out. In PHP you’d use SQL2K to connect instead of the host name or IP address.

3. FreeTDS – Domain Logins

   Our user accounts are all managed by Active Directory, besides the default accounts in SQL Server 2000 all of the permissions are handled via Windows domain accounts. This means that all of your usernames have to be prefixed with the Windows domain. Something like EXAMPLE\username. If you run into problems check out the FreeTDS documentation about domain logins.

4. SQL Server 2000 – Quoting

   Note that quotes are escaped with quotes, not back slashes. I personally don’t care for this method, but it seems to be the only one that SQL Server 2000 supports. If you are going to use CSV to import data you need to pay attention to this. Although CSV is supported I ended up going to tab delimited instead. This is the default that SQL server expects and it made some of the quoting issues easier.

5. ADOdb – rs2tabout()

   For the sake of consistency I’m using ADOdb to deal with database access in PHP. One neat feature I found was the ability to easily export a result set to CSV or tab delimited formats. This was exactly what I needed to do, so this seemed like a great solution. Unfortunately rs2tabout() tries to escape quotes, which SQL Server choked on. The quotes didn’t need to be escaped since tabs were being used as delimiters, so I poked at the code to see what was happening. All of the export functions derive from a base function with several options, so I was able to call that function directly and override the defaults. If you want data tab delimited with quote escaping turned off you can do that with:

   print(_adodb_export($rs, "\t", " ", true, false, false));

   or

   $line = _adodb_export($rs, "\t", " ", false, false, false);

6. bcp

   Importing lots of data with bcp is much easier than clicking through the import/export wizard. To make life easier you’ll need a format file for the table that your are importing data into. The easiest way I’ve found to generate the format file is to let bcp do it for you:
   
bcp testdb.dbo.tablename format tablename.dat -U username -P password -f tablename.fmt -c

   Then importing is done like this:
   
bcp testdb.dbo.tablename in tablename.tsv -U username -P password -f tablename.fmt -e tablename.err

   If bcp has any problems importing data will be logged in the tablename.err file. If it runs into enough errors the import will terminate, the default is ten. In the test table that I’ve been using has 98,000+ records in it. The process of retrieving the data and formating into tab separated values (tsv) takes about 110 seconds. The import with bcp takes less than 5 seconds. I like it when things go fast :-)

That pretty much covers it for now.