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.

New Position: Lead PHP Developer

June of this year I was employed on my second job(PHP Developer) that paid me 133% more than PHP Programmer). My former officemate on my first job, told me about a job opening with the same salary, I wasn't able to entertain this because my second job's website got hacked and all files got wiped, so I needed to help. And I don't think it's a good idea to leave a company after 1 month cause I think it'll look ugly on my resume.

The offer

3 months later I called them again and to my surprise they asked me to go to there office right away located in Ortigas, NCR, Philippines that night because they need it urgent. I had a test which is a bit hard( I was expecting tests would be easy) then next day I was offered the job as PHP Software Developer (although I would just call it PHP Developer), for 14% more more salary but with 37% less working hours but it is night shift.

I came back to there office on night shift, then discovered the one that I will replace, who needs to go away for personal reason, is the PHP Lead Developer of two other developers. Of course I didn't expected that I will be the next team leader cause I've just worked for 2 years(+1 year by my own) and I am new to the company. This guy explained to me the current project status and the background of the company we will work in. When I asked him if I will be the next lead developer, he  said he don't know.

The challenge: being unexpected leader

Some days later I formally started and the surprising part is that the CEO assigned the leader. Although in doubt, I just thought this is my chance to experience it. Considering I'm still a young developer(but not generally young though :P, at age 22, I know it's not gonna be easy to lead team mates who don't know you.

I had to adjust with the codes I inherit, which I call (sorry) bad code. I had learned how to aim for benefit of simplicity on my first job while my second job thought me on the benefit of complexity. I had my first job for 2 years, had them both simultaneously for 2 months and had my second job for 3 months. I learned a lot from my two previous jobs and it's time to apply the lessons from both.

Sinking in

All seems to be good, the catch is it takes 2 hours to go office. Including the time to prepare before and after office it takes away extra 6 hours of my daily life. I always say this same phrase "Tatanda ako ng maaga" (I'll get old early) :)). So I am preparing to rent-to-own a condo 15 minutes away from office. I just wish I can budget my money well cause it'll cost 65% of my current salary. XD