ADOdb and MSSQL: Differences from MySQL and my ADOdb impression

On our first project that I'm in that utilizes MSSQL, a database server made by Microsoft, I've chose to use ADOdb abstraction, to prevent possible incompatibility. However it wasn't that easy. I had problem connecting, problem with SQL and more.

What is PHP ADOdb?

ADOdb is an abstraction library. Abstraction aims to simplify coding by doing the procedure but hiding the code that does the procedure. It is not actually hidden though, you can always dig in to the code but that defeats the purpose of abstraction, which is, to require less thinking for the programmer.

It is also designed to have shortcuts on stuffs that are frequently done. Therefore it lessens the lines of codes and making it easier to read.

Unsolved Connection Problem To MSSQL

2 of us on our team had problem connecting using SQL clients like SQL Server 2005,Visual Studio and Navicat. It says Login failed for user <username>. My team mate though, using mac, can connect using Navicat using the same credentials. Then I theorize that there's something weird on windows (or vista[our office PCs uses vista]) that maybe preventing us to connect.

The PHP on our server can connect, and since it's not a requirement to browse the db on a desktop client, I shifted my effort on creating a simple querying script on the server, so we can use it on querying.

Differences of MSSQL and MySQL

It wasn't as smooth as I imagined it, since the role of abstraction layer is to prevent compatibility issues. Here are some of the issues I had running PHP ADOdb on MSSQL as compared on PHP ADOdb on MySQL

• backticks ( ` ) - it was my habit to backtick tables and field names on mysql, I realized it is not supported on MSSQL and I later learned that it uses brackets instead ( [ ] )
• LIMIT clause - this is not supported on MSSQL, you will have to use something like: SELECT TOP 10 * FROM tablename
• Floats you see might not be what you see. I found out that even with SQL where clause " fieldName > 0 ", I see results returning fieldName which seems to be equal to 0.00 , I then tried ROUND(fieldName,2), a suggestion of my teammate and it worked. Our theory is, it might be containing values beyond the 2nd decimal digit.
• SHA1 and MD5 - these encryption algorithms are not called like SHA1() and MD5() on MSSQL. They are called by a function named HashBytes,(eg. HashBytes("SHA1","123") ) which returns binary version, like if php sha1() is called with 2nd parameter as true. To convert to hex, you have to use a function named master.dbo.fn_varbintohexstr() , which converts it to 0x01234 hex version, which then leads you to SUBSTR() it so you can remove the "0x". Messy isn't it?
• example: SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA1', '123')), 3,512)
• The big limit is 512 is a play safe solution for MD5(32 hex characters) and SHA1(40 hex characters)
• Random order - there's no clear support on MSSQL on MySQL's ORDER RAND() ... you can use a unique id seeder which is newid() though. Pretty slow though.
• SUBSTRING() -  the third parameter (length) is required. SUBSTR() alias is non existent. Second parameter when set to 0 does not return blank string does not return blank string.

ADOdb Functions I Like

I've used ADOdb before on my second job, what I liked about it is that it has functions like the following:

• getRow($sql) which gets the first row of select as php array,
• getOne($sql) which gets the first row's first column(first cell), (eg. for sum(), count()) (as string)
  
• getAssoc($sql,...) which returns array(first column =>  second column) format which is very useful in a lot of instances specially when combined with smarty template's html_options
• autoExecute($table,$data,$mode,$where,...) - which inserts or update table. Useful specially for my situation having MSSQL which I am unfamiliar with. However I was not able to use it on MSSQL since this current project was read-only on DB.

Things I wish ADOdb has

• escape string - it has quote() function, but sometimes I want an escape function that will not quote my strings
• one dimensional list array query - it has getAssoc(), but sometimes I want a numeric list of array which is messy to do with getAssoc(), take this sql for example:SELECT country_id FROM countries
  
  Although it can be  done by crappy way like array_values(), I still wish there is a built in function on it.
• meta quote (quoting for fields or table)  - a way to quote the field name like `field_name`, probably useful for reserved names

Achieving The Real Abstraction

• Field quoting - use a function. (eg. string(7) "`date`" db_quote_field('date') ) or a class method
• LIMIT clause - omit it. Then use GetArray([$number_of_rows]) if needed a specific limit or GetRow()
• SHA1 / MD5 - you may not be able to use the same variable approach here, since MSSQL and MySQL SHA1() and MD5 it has a parameter and differ in number of closing parenthesis. Because of this, you may use a PHP function or an SQL procedure. I suggest using a PHP function.
• eg. string "SHA1('123')" db_proc('SHA1','123')
• Random ordering, Substring and other functions - Concat, IfNull, length, random, substr page of phplens appears to have list of abstraction functions and properties.

Conclusion

Always remember to use a user with limited permission when using a new SQLanguage.  On this case I specifically requested for a read-only user.

Although I had a experience that is not so smooth on using MSSQL, I understand that I have to learn more and use it before I conclude (although sometimes I'm a Microsoft hater :)) ), so I can't say that MSSQL is bad. Yes every PHP Developer likes MySQL, but there are people that are comfortable with it you'll have to face that fact if you want to be a good developer.