Converting ms sql to mysql database

I’m really in a blogging mode yesterday and today. Because this is the last day of vacation and I don’t think I’ll be able to write things for a while that’s why I’m doing the best I can to add up entries to my blog.

Okay, enough with my life story. This time I’m going to show you the easiest way on how to convert an existing ms sql database into a mysql database. People call this database migrations (Yup there’s also database migrations in Rails but that’s a different one).

Before I proceed, let me tell you that I have found an article from the mysql site which discusses about this thing:

http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

I recommend that you go and check it out first. So that you’ll understand the basic concepts on database migrations.

Requirements

  • Existing ms sql database
  • DRPU Database converter – MS SQL to MySQL
  • Wampserver

Steps

First, you have to give a password to your root user using phpmyadmin. Because DRPU database converter doesn’t allow you to go any further if there is no password. We need it to be the root user because it has the most privileges to all the databases that is on the mysql database server. You might get some errors in the DRPU database converter if you don’t use the root user.

Next, install DRPU database converter. You can use Sandboxie if you just want to try this software. Here are the things that you need to input:

  • MS SQL Server – the computer name
  • MySQL Server – default would be localhost
  • Login Name – the user that I told you to create earlier
  • Password – the password of the user that you created

image

If you don’t know the name of your computer, just go to start->right click computer-> properties.  It looks something like the one below if you’re on windows 7 or vista.

image

Before you click on next, make sure that the sql service is up and running. Together with wampserver. Make sure that it is online.

Then you’ll see this screen if it has successfully connected. Just click on select all if you are not on the mood to click the tables one by one.

image

Yeah my example was a bit exaggerated since the SBO-COMMON database is the database that is used by SAP Business One. This one might take forever, so I just terminated the process by deleting the sandbox.

It might just take less than 10 seconds if your database is not as big as the SBO-COMMON database.

And here’s the screenshot when I open it up on Phpmyadmin

image

You can see from here that the demo version can only export up to 5 records into a mysql database. Doesn’t really matter since it already took the trouble of rebuilding the database from scratch.

image

How to export ms sql database using sql server management studio

 

Requirements

 

Prerequisites

You already know the basics of using Server Management Studio. Like connecting to the ms sql server, browsing the different databases and tables, executing sql statements.

Steps

First you have to open up SSMSE. Right-click on your database and select task then generate scripts.

image

image

 

Select your database. You can also check the script all objects in the selected database. So that it will no longer ask you later on what object (tables, stored procedures, views, etc..) to include in the sql file that will be produced.

image

Just go ahead and press the next button right away if you see this screen. That is if you don’t really see something that makes sense.

image

Then select the script to file. Make sure that you have also selected single file and Unicode text. After that, just click on the browse button and select the location where you want to export the sql file. And then name it using the name of the database or any name you want.

image

This is just a summary of what it will do.

image

Then here’s the screen that will notify you if the operation was a success or not.

image

If you open up the sql file that has been produced:

USE [students]
GO
/****** Object:  Table [dbo].[student_info]    Script Date: 05/29/2011 15:20:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student_info](
	[idnum] [nchar](10) NULL,
	[fname] [nchar](10) NULL,
	[lname] [nchar](10) NULL,
	[course] [nchar](10) NULL,
	[address] [nchar](10) NULL
) ON [PRIMARY]
GO

Basically it looks like the sql file that’s been produced by mysql. But it won’t work if you try to import it using phpmyadmin. Since ms sql and mysql are very different databases.

So what you can do with this sql file? You can actually open it up on notepad, copy the contents and execute it on SSMSE to rebuild your table. As you can see from the code above, it doesn’t create the database, it just uses it. So you still have to create a database with the same name as the one specified in the sql file to make use of this sql file. Another thing, it doesn’t actually backup your data. Or dump statements as they call it.

So what’s the purpose of this tutorial? To create an sql file which describes an ms sql database. If you are planning to migrate from ms sql to mysql database. Then this file can serve as a basis for the database that you are going to create in mysql. All of the table structure are in one place.

Spend time wisely in front of your computer

Third entry in a row. This time I’m going to share to you how to spend time wisely in front of your computer.

Create video tutorials
If you want to train yourself to speak in English, that is if you are not already good at it. Then the best way to do that is to create video tutorials. You must be a bit tech-savvy if you want to do this. Since you can’t actually teach anything if you don’t know anything about computers, and technical stuff. You can use camstudio or camtasia to create your video tutorials. I believe that when you teach someone how to do things you also learn from it. Since what you’ve learned really never stick to your mind like a super glue if you don’t do it the second time or the third time.

Twitter
I believe you can get more useful information in twitter than on facebook. By following news sites, tech sites, web development sites, educational sites, and people who are already well-known on the field that you wish to pursue. You are actually enriching yourself with all the information that they wish to share.

Stumbleupon
This is one of the websites that I fell in love with. Its simply awesome, giving you the power to explore websites which fits your interests. This is the best site to visit if you are not actually doing something.

Google Reader
There’s also google reader. The rss feed reader used by people who doesn’t want to use desktop applications to read rss feeds. Google reader is a place where you can link all of the sites that you want to read. Keeping up with sites is really tiresome if you have to visit them everyday just to read their content.


Watch video tutorials
You can also watch video tutorials. If you want to learn how to use a specific software or a programming language. You can just go ahead on Lynda, Virtual Training Company , or Total Training and search for something that you want to learn. Of course, most of these tutorials are not for free. That’s why step 2 would be to visit your favorite torrent site and search for available torrent of that video tutorial that you wish to watch.

If you consider yourself a good man and don’t want to use torrents. Then you can try out the new boston  channel on Youtube. Tutorial in there are absolutely free. And really awesome and easy to understand. The creator of the channel, Bucky Roberts as he calls himself. Is currently doing some tutorials on Unreal Development Kit. So if you’re into game development then you can go ahead and check those out.


Coding
If you want you can also take up courses on w3schools and learn how to code. Programming is a nice way to train your mind. Solving hard problems is always rewarding. So if you want to challenge yourself, you can go ahead and take the programmer’s path. I recommend that you start with a fully object oriented programming language like Java. You could be an awesome game, web, or desktop application developer in the future if you pursue this field.

Designing
If you are not really into code, then perhaps you might want designing. Designing is for creative people, so if you think you’re creative then this field is for you. You can design anything from websites, commercials, and a lot more. I can’t really talk more about it since I’m not a designer.

Blog
And I almost forgot what I’m doing right now. You can also blog if you want. As for me, blogging is one of my favorite past times. Not only do I got to integrate what I have learned into my brain, I also get to share it to the world. To the people who are basically taking the same path as mine.

Watch movies, tv series or anime
There’s no question about this. If you are totally bored then you can just download movies, tv series, or anime from torrent sites. But be careful when you do this especially when your connection is slow. You might want to open a hundred tabs on your browser first and read all of those content while you are downloading torrents.
I recommend the Supernatural tv series, its really awesome. For anime, you can watch headless, letter bee, or one piece. Naruto doesn’t get my recommendation since it’s a super hard gay anime. For the movies, I recommend watching animated movies if you want a good laugh. Some of my favorites are (get ready for the big list) : Cars, Kung Fu Panda, Ice Age 1-3, Happy feet, Toy Story 1-3, Bee movie and Ratatouille. There are many more, but those are the one’s which are directly available to my conscious mind. So bear with me.

Maybe you are asking why is this even included to the list. The simple answer is that every human being needs time to slack. And the wisest way to slack is to watch something entertaining. To put away your mind from the daily stress of life.
 

Conclusion
That’s it! I hope the next time you turn on the computer, don’t just mindlessly go to facebook and spend the whole day browsing it. I know it’s a really addictive site but we must have control over it if you don’t want to waste precious time.

How to hide from people on facebook

I’m not really in the mood to watch tutorials and increase my tool set today. So I’m going to teach you how you can stay offline from the facebook chat for the people that you specify. Yeah that’s right, you can now hide from your teachers if in case you are not in school and you are just doing facebook at home.

First you have to login. Then click on the Account tab. Then select edit friends.

image

Then click on create a list.

image

Next, name the list anything you want. And search for the people you want to hide from. Yeah you can go ahead and select all your teachers if you want.

image

After selecting all the people you want to hide from. Click on create list

image

Finally, you can just click on Go Offline beside the list that you have created. (Yup I ‘m really lazy to open up photoshop and do some filter on those, fyi I’m just using the good old Ms Paint to edit my screenshots. So pardon me if that doesn’t look very good to you)

image

Connecting to mysql using dreamweaver

In this tutorial I’m going to show you how you can connect your php application to mysql using dreamweaver. If you already know how to connect mysql and php, and you are looking for an alternative way to connect those two together then this tutorial is for you.

Things you’ll need

  • Adobe Dreamweaver – You can use either cs4 or cs5.
  • Wampserver

 

 

Things to do

First open up Dreamweaver. Then create a new site if you haven’t created a site yet.

image

Just name the new site to whatever you want and browse for the site folder. The path should be something like:

c:/wamp/www/newsite

Next, click on the servers tab. Then click on the add button to add a new server.

image

 

On the Basic tab, type in any server name you want. But connect using Local/Network. The server folder should be the root directory or the www directory if you are using wamp. Web URL should be http://localhost since this is only a testing machine where you develop applications. If not then you might want to type in the ip address of the computer where wampserver is installed. It may look something like: http://192.168.250.300:8080. That’s just a sample ip address, don’t go ahead and type that one.

image

Next click on advanced tab. Set server model as PHP MySQL

image

Next, uncheck the remote, and check testing. And click save.

image

Then you have to switch to app developer plus workspace. On the right-hand panel, select the databases tab, then select databases tab again. Click on the add button, and select MySQL Connection.

image

Connection name should be anything you want, MySQL server would be localhost, default username would be root and you can just type in the name of the database  that you are going to work with or just click on the select button and select the database from the list. Lastly, click on the test button to check if you are really connected or not. You might want to launch wampserver first, and that its already online before clicking on the test button.

image

You can now see that the database tab is already filled with table information, views , and stored procedures.

image

 

 

Conclusion

That’s it for this tutorial, next time I’ll be showing you how you can create forms based on the fields on the tables in the database. Of course, this will all be done without actually coding a single line of code.
But I do not actually recommend this method if you are still learning about php and mysql. Because you wouldn’t actually learn how to code things if you do it this way. Its much better if you also know how to code things and fully understanding how they work before you dive in the shortcut methods to accomplish things such as these.

Automatically include a path in php

This is a quick tutorial on how to automatically include a path in every php file that you create. This does not mean that you no longer need to do an include:

include_once('class.string.php');

I just cleared things up. Since that was my assumption when I first learned that I can explicitly include paths in every php file that I create without actually including it.

Things you’ll need
Wampserver
Text editor

Things to do
First you have to launch wampserver, then right left click on the tray icon >php>php.ini

image

Open the file using a text-editor if windows prompts you what program should you use to open the file.

After the file has been opened. Press ctrl + H on your keyboard then type include_path, click on find next until you see the windows word right above the word include_path.

Uncomment the line by removing the semicolon before it:

; Windows: "path1;path2"
include_path = ".;C:php_includeshome_made"

If you want to include multiple paths then you can separate them using a semicolon, like the example that has already been given just above the include path.

The path that I included contains php classes that I could use for login, connecting to mysql database, sessions, and formatting strings and dates:

image

You can also include files which you will include as a header, footer or a sidebar. But be sure to reference the css and javascript files needed by those files which you have put in the include path.
Save the file and restart all services from the wampserver tray icon.

image

All you have to do now is to include those files inside the files where you need them:

require_once('class.sessions.php');

It’s a bit easier when you include it using the full path isn’t it?
Don’t forget to declare an object of the class if it’s a class that you are trying to include.

$sessions = new sessions();

 

Conclusion

That’s it for this quick tutorial. Just make sure that the paths that you included exists, and that the files that you have included exists in the included path. Because you will get a nasty error on every page if the path doesn’t exist.

How to use Jquery ui datepicker

Its been a week since I posted, I’m slacking off since vacation days are almost over. I was just watching movies, and tegami bachi and when I got tired I watch video tutorials from lynda.com.

Well, that’s it for a quick update of my life. This time I’m going to show you how you can use the Jquery ui datepicker in case you’re having trouble making it work.

 

Things you’ll need:

Jquery core
Jquery ui

What to do:

First you have to build your folder structure. So that you won’t have any trouble linking the files that will be needed later on.
Create a folder named datepicker or anything you like. This is where we will be putting all the files which are needed in this tutorial.
This will be the structure:

image

Here’s a description for each of those folders:

  • css – this is where you will put the css file which came with the jquery ui download. The original filename of that css file will be jquery-ui-1.8.13.custom.css, just change that to jq_ui.css so that it will be easier to reference later on.
  • images – just copy and paste the image folder which came with the jquery ui download.
  • jquery_core – this is where you will put the file that you downloaded from the jquery site. Just rename the file to jq.js.
  • jquery_ui – Open up the js folder from the extracted jquery ui archive. Then copy the files , and paste it on this folder. Rename the file named jquery-1.5.1.min.js to jquimin.js
  • datepicker.php – yup, this is the file that we will be working on.

 

Hope that was clear to you. Next stop, we link the necessary files into datepicker.php:

<link rel="stylesheet" type="text/css" href="css/jq_ui.css"/>
<script type="text/javascript" src="jquery_core/jq.js"></script>
<script type="text/javascript" src="jquery_ui/jquimin.js"></script>

Then build up the function which will call up the datepicker:

<script type="text/javascript"> $(function(){ $("#datefrom").datepicker({dateFormat: 'yy-mm-dd'}); $("#dateto").datepicker({dateFormat: 'yy-mm-dd'}); $( "#datefrom" ).datepicker(); $( "#dateto" ).datepicker(); }); </script>

As you can see, you have control over the date format which will show up once you have selected the date. In the example, the output will look like this:

2011-07-01

If you’ll be storing this data on a date field on a mysql database then you don’t need to change this format anymore.
If you want to store this date with the current time. Then you can use php to do so. Just set the timezone first, and then declare a variable $time to store the current time:

date_default_timezone_set('Asia/Manila');
$time = date("g:i:s");

Later on, you can just concatenate the value of time to the value of date:

$_POST['datefrom'] . " ". $time;

It will look something like that if you plan to use $_POST to submit form values on the server. But remember to sanitize those first using mysql_real_escape_string.

 

Yeah, I almost forgot about linking the correct image path on the jquery ui css file. Open that up if you want to see the forward and backward arrows on your datepicker:

image

We will do this quick using the find and replace tool. Just press ctrl + H on your keyboard, then do what you see in the screenshot, then click on replace all:

image

Note that you can only do this if you have strictly followed my instructions. Your folder structure might be different, be sure to reference those correctly.

 

Conclusion
That’s it for this tutorial. Just consult the jquery ui page for the datepicker if you want to further customize your datepicker: http://jqueryui.com/demos/datepicker/