MsSQL Export to CSV

In the near past I got frustrated searching for a tool to export from MsSQL express to proper CSV. As always the frustration lead me to google, and found the post on stackoverflow and to digital point forums and a lot of other places. All these had some or the other issues with us. One being the MsSQL not hosted on our servers and we dont have access to the GUI tools, the second is that I am a bit towards the fag end when Microsoft Technologies are in the anvil. Finally there was no other way and what I did was to migrate our PHP MySQL wrapper to support MsSQL.

Continue reading “MsSQL Export to CSV”

Getting datetime type into JavaScript as Date object

When selecting datetime to be displayed in a JavaScript ui library, select the unix_timestamp * 1000 from the sql

This is not any new thing and may be discussed at different other places. But just as I came across like any other things, I just wanted to make a record of this.

When selecting datetime to be displayed in a JavaScript ui library, select the unix_timestamp * 1000 from the sql.
Continue reading “Getting datetime type into JavaScript as Date object”

Session TimeZones and DateTime fields

I was pondering over how to coax mysql (and mssql) to automagically select local time if a session variable was set with the required timezone

Today I was pondering over how to coax mysql (and mssql) to automagically select local time if a session variable was set with the required timezone. Well we arrived at a conclusion that we could not do that on DateTime fields in MySQL. Alas we were having varchar(19) in the equivalent field in MsSQL, which was the outcome of an import from MySQL using a connection string and a procedure. We were at a dismay, and admitted defeat. Later while having lunch, I got enlightend about using the built in functions to do the methodical conversions.
Continue reading “Session TimeZones and DateTime fields”

Migration from MySQL to MSSQL – Quick Tip

With the help of some colleagues, I could export all the indexes and primary keys, which we were using from a MySQL table as MSSQL compatiable TSQL using a union query and on the MySQL information_schema.

With the help of some colleagues, I could export all the indexes and primary keys, which we were using from a MySQL table as MSSQL compatiable TSQL using a union query and on the MySQL information_schema.

I did the migration to MSSQL using some tips found online, and calling a sp after connecting directly with the MySQL server. Actually forgot how it was done. Anyway it does not matter in this post. Will add it as a comment later on. The important matter was that, the method did not import any of the indexes, or primary keys. We manually created those were absolutely necessary to roll out the project. And now that we were about to release a service patch, and this includes more incredible search methods, the indexes are absolutely necessary.
Continue reading “Migration from MySQL to MSSQL – Quick Tip”

php MsSQL Backup

A php script to dump database as sql from MS SQL

We started porting (provide dual support) one of our application from MySQL to MSSQL, sorry, actually we were trying to run the same code base on either MSSQL or MySQL as the client wishes. And those who knows both, will agree to the level of toughness and agony when considers the fact that all of us are on MySQL and are new to MSSQL. In this context, we wanted to provide backup and restore to the application administrator through the system itself, and was not contented with the .bak files provided by MSSQL backup.
Continue reading “php MsSQL Backup”

MySQL vs MsSQL – comparisons never end

Search for the title, and you will land in several sites, which will tell you the views of the author. In fact there may be hell lot of people who would tell you that one out performs the other. But in reality what does that mean ?. If one knows how to optimize the database server, the sql, and the code behind. The performance can be tuned to a factor that both are at par. Now that is a far fetched view. Just think of the real bare minimum hardware to handle 300 tables, with less than 50 having a million rows, and the rest having master data. The MsSQL server will need a min of dual core with 8G memory, whereas the mysql would run on a 2G Virtual Machine and even out perform the MsSQL server. Think of the SELECT …. LIMIT X,Y and the SELECT GROUP_CONCAT, the advantages when considering server side pagination as well as selections directly outputting JSON for ajax frontends.
Continue reading “MySQL vs MsSQL – comparisons never end”

Migration from MySQL to MSSQL our solutions – Continued

In the previous post, I had specified that a code analysis would be presented later on, which is happening now. Though I am not permitted to abstract the whole database abstraction, which would otherwise divulge the core business logic of the system too, I do expect that the following would be enough to guide a MySQL PHP developer to port his application to MSSQL 2005. Mostly the compatablities are maintained. But for the database design, we had to let away some of the wonderful features from MySQL.

We started by a code auditing and reworked the system such that we did not use any group_concat through out the system. Also all timestamp and datetime fields were changed to varchar(19) since we were already feeding those fields with the php function date’s return value or ‘now()’. But there was quite a handful of areas where we were using STR_TO_DATE and with different formats. So this had to be handled in its own way. And it is here we started our regular expression war path.
Continue reading “Migration from MySQL to MSSQL our solutions – Continued”

Migration from MySQL to MSSQL our solutions

Last post I did had all of my feelings, and despair, and now I am sure those were out of sheer negligence and mostly because I was unaware of the MS SQL product. Though I still do not agree with certain things like the row_count workaround for the limit feature, and escaping of quotes. But for our project we could more or less maintain a streamline by using the same code base for MySQL, SQLite3 and MSSQL 2005. Will try to explain this across a couple of posts.

Continue reading “Migration from MySQL to MSSQL our solutions”

Porting application from MySQL to MSSQL

At first I thought it should be a challenge, though I did not expect it to be too tough, since we were already using a custom db wrapper. Well it turned out to be well too heavy a nightmare. And there was situations where I even considered retiring from life. I pity those who are paying thousands of hard earned money to buy such filthy crap.
Continue reading “Porting application from MySQL to MSSQL”