Update To MSSQL To MySQL Translation
I have started building out my MS SQL to MySQL translation post. I have decided that I am going to tell the whole "story" in MS SQL 2005 before I even try to attempt any of it in MySQL. I feel like this will help me get it done faster - not having to stop all the time to figure out how this is done in MySQL at ever step of the journey. Here is what I have added:
- Drop Table If It Exists - MSSQL
- Create Table - MSSQL
- Populate Table - MSSQL
I have decided to go with actual tables where I would usually have gone with temporary, in-memory tables since it seems that temp tables are not a very widely supported property of database systems. This already makes me feel uncomfortable. This is such an awesome feature of Microsoft SQL server; I use temp tables all the time to break down huge SQL algorithms into smaller, more manageble steps.
Reader Comments
Could you elaborate on your comment that temp tables are not widely supported? Because the way I see it not only pretty much all databases support them, but they even use similar syntax.
@Jochem,
I might be wrong - I am new to MySQL. I am talking about this:
DECLARE @contact (
id INT,
name VARCHAR( 20 )
);
Now, the variable @contact is a temp table that exists only for that SQL execution. I think I tried to do this in MySQL and it was not a happy kitten.
That was supposed to be:
DECLARE @contact TABLE ( ... )
I really think you are getting hung up on a syntax idiosyncrasy. I would write:
CREATE TEMPORARY TABLE contact (
id INT,
name VARCHAR( 20 )
)
This works in Oracle, MySQL, PostgreSQL, SQLite, Firebird, Ingres, Informix and probably quite a few other databases. I would call that "widely supported".
@Jochem,
I don't think I am getting hung up on "syntax idiosyncrasy"; if there is stuff that is supported and I don't use it, I assure it's because I don't know about it. I did not realize that there was a TEMPORARY keyword. This looks pretty cool. I assume that you can just create this and forget about it (hence 'temporary')? Can you tell me a bit more?
@Jochem,
I just did a quick little Google search and I think we may be talking about two very different things here. From what it looks like, TEMPORARY table seems to create an actual physical table (that just gets dropped automatically). It looks like this has permissions issues, and what happens if you have two users runs this script concurrently? Are there going to be naming-conflict issues?
Please clarify if I have am way off here.
What is the semantic difference between a physical table and an non-physical table? Would an INSERT or a SELECT work differently? Can you use them in different SQL statements? I don't care about the semantics. Why do you care?
Permissions is only an issue if you start bringing global temp tables into the equation. Again, why do you care? Do you actually have a use-case where there are not just multiple sessions of the same user, but multiple sessions of different users? Because if you do, you will also have permissions issues with declared temp tables in SQL Server.
The SQL standard defines three different types of temporary tables, declared, created local and created global. What can you do with declared (i.e. MS SQL Server / DB2 style) that you can not do with created (i.e. the rest of the world style) temporary tables?
@Jochem,
As long as they act the same way, I don't care about the minor differences. And to be honest, I don't know enough about "sessions" in SQL server to even really know what you are referring to. I am not trying to split hairs here, I just want to make sure that I don't create something that I find out later causes a big conflict.
Let me talk something out....
One of the things that I use temporary tables for is to break down reporting into smaller, more manageable steps. While all reports require their own logic, often times, I will have a table that is named something like @valid_id into which I store some initial filtering. I just need to know that if I have several reports running in parallel that all use a temporary table named "valid_id", that the system is going to blow up?
If you can calm my nerves on that, I will be happier than pig in slop :)
They will not blow up. Just like @identity is specific to a session created temp tables are specific to a session (unless you declare them global, same as in SQL Server).
And a session pretty much equates to a connection for as long as you don't issue statements to change the CURRENT_USER / SESSION_USER / ACTIVE_USER or whatever your database calls it.
@Jochem,
Dare I say, you might have just rocked my world???
@Jochem,
I just ran this a twice on my MySQL 5 server:
CREATE TEMPORARY TABLE test (
id INT,
name VARCHAR( 20 )
);
The first time, it runs fine. The second time, it tells me that table "test" already exists. It appears as if it is not dropping automatically. Any suggestions? I will take a look at the documentation, but I thought I would run this by you quick.
@Jochem,
I thought maybe this happened because I ran it in the query analyzer right in the MySQL 5 database, so I created a CFM page and did a CFQuery tag with the CREATE TEMPORARY TABLE call. Again, first page it ran well, but then upon page refresh, is got the "already exists" error.
Then, I had this thought that since "Maintain connections across requests" is checked in the data source in the ColdFusion admin, this might be messing with what is considered a close connection.
I went into the admin and unchecked that box. Now, multiple page refreshes execute the CREATE TEMPORARY TABLE call without exception.
But this raises some serious red flags:
1. Is it good to have that "maintain connections" unchecked? I was told there was a performance gain to have that checked at all times.
2. Even if that is unchecked, does that pertain to multiple CFQuery tags on the same page? Meaning, can I run the CREATE TEMPORARY TABLE script N times in the same CFM template without causing a name conflict?
Please help me if I am missing something really big here. But this shaping up to be more than just a semantic difference.
I think I should have read your code instead of your description. What you are trying to do is not creating a temporary table, but declaring a variable. You can pretty much forget porting that. Some things are widely portable, some things are not. Modules (stored procedures) and anything that has got to do with them are not.
But can't you just drop the temporary table once you are done with it (and before you call the script again?) I think this is what you *should* be doing anyway. This way you are performing manual garbage collection. If you don't you will get the table exists error that you were getting before, and the potential for trouble along the way. It's similar to the variable as it is only in existence for the duration of your SQL script (as you are creating it at the beginning, then dropping it once you are done with your script).
@Gareth,
My problem with having to manually drop a table is that it means the table exists in some physical way. This concerns me when you have many people hitting a site, possibly with the same page and functionality.
Also, what if (and I know this shouldn't really happen), what if your SQL script craps out in the middle of running - maybe bad user data you didn't account for? Then, you have created the temp table and the script died before you could drop it. Then, next time you go to run the page (assuming connections are maintained across calls, which is the usual case), your CREATE statement will crap out saying the table already exists.
At least, I think that is what will happen.
There's just too many moving parts. The table variable seemed so easy and natural to use. I am not sure why I would fight it. But, I guess, when you work with MySQL, you have to give up some cool stuff (and possibly gain some other stuff???).
@Jochem,
Sorry for the naming confusion. Since I didn't know about "TEMPORARY" tables, I used "temporary" in the common sense, meaning, it only existed for a short time.
@Ben,
In that case you would just check whether it exists first. If so, you would drop the table first before recreation. Making it transactional should also (I think) allow you to roll it back, and remove the temporary table if something happened along the way. This is usually good practice anyway, especially if you're manipulating tables and data.
Also, if you're going to have lots of people hitting the table, this would probably be the tipping point of actually creating the table rather than just generating it temporarily each time :)