Sign in to follow this  
Thevenin

Simpler MySQL 'insert into' command?

Recommended Posts

As my database's column count expands at a seeminglessly cruel and unusual rate, I find myself making some pretty bad mistakes with aligning the data sets in my MySQL INSERT commands. For example: >INSERT INTO something (x,y,z,f,h,o,e,r,rr,rrr,p,pp,qr,delta,this,is,a,very,long,insert) VALUES ('5','3',f','yes','hi','which','letter','is','this?','oh drats', 'have I gone too far?','or not far enough','=/'); >kab00m! MySQL ERROR. Cannot match column to value! I'm wondering if the 'MySQL Connector Net' contains any wrappers around this, such as, say... MySQLInsertCommand.AddValue('columnname1','value1'); MySQLInsertCommand.AddValue('columnname2','value2'); MySQLReader = MySQLInsertCommand.Execute();

Share this post


Link to post
Share on other sites
Actually I have/had the same problem with a browser game I made once. While developing it I often created tables with lots of values, and was starting to get tired of making the dreaded `insert query`. And if your doing everything from MySQL CMD then you do have to do it over and over again.

To answer the question? There isn't a simpler way to do the insert query, sorry. Tough luck really, guess you'll just have to be more carefull :P

Share this post


Link to post
Share on other sites
Quote:
Original post by ToohrVyk
I don't get it. Shouldn't you write that insert statement only once, anyway? Write it correctly that single time, and be done with it.


Sorta. Everytime I make modifications to the structure of my table (which I do quite often), I have to go back and hack away at this incredibly long insert statement. Although this may sound like a simple task, it gets incredibly difficult when the column list and the value list are separated by a couple pages of values (there are 634 columns in my table; a hundred of which have non indexed names).

I quickly hacked this function togethor, since I honestly can't handle dealing with misalignments any longer.

    /* A wrapper for generating long MySQL 'INSERT INTO' commands. */
public class cInsertMaker
{
List<string> TheColumns = new List<string>();
List<string> TheValues = new List<string>();

public void sInsertRecord(string TheColumn, string TheValue)
{
TheColumns.Add(TheColumn);
TheValues.Add(TheValue);
}
public string sGenerateInsert(string TheTable)
{
string TheReturn = "INSERT INTO " + TheTable + " (";
for (int TheLoop = 0; TheLoop < TheColumns.Count; TheLoop++)
TheReturn += TheColumns[TheLoop] + ",";
TheReturn = TheReturn.Substring(0, TheReturn.Length - 1);
TheReturn += ") VALUES (";
for (int TheLoop = 0; TheLoop < TheColumns.Count; TheLoop++)
TheReturn += "'" + TheValues[TheLoop] + "',";
TheReturn = TheReturn.Substring(0, TheReturn.Length - 1);
TheReturn += ");";
return TheReturn;
}
}





Yes, its soo terrible of code. But given the environment it will be used in, it's perfectly safe and of adequate performance. Shame a wrapper like this wasn't built into the connector though, but I suppose it would be slightly too specialised.

[Edited by - Thevenin on October 6, 2006 4:40:06 AM]

Share this post


Link to post
Share on other sites
Quote:
Original post by Thevenin
(there are 634 columns in my table; a hundred of which have non indexed names).

That many columns?!? Wow!
I think your database format is the actual problem here than. Split that huge table up in some more managable tables, is my advice.

Share this post


Link to post
Share on other sites
Quote:
Original post by DaBono
Quote:
Original post by Thevenin
(there are 634 columns in my table; a hundred of which have non indexed names).

That many columns?!? Wow!
I think your database format is the actual problem here than. Split that huge table up in some more managable tables, is my advice.

I agree - if you have that many columns in your tables, then you should refactor it.

John B

Share this post


Link to post
Share on other sites
Quote:
Original post by DaBono
Quote:
Original post by Thevenin
(there are 634 columns in my table; a hundred of which have non indexed names).

That many columns?!? Wow!
I think your database format is the actual problem here than. Split that huge table up in some more managable tables, is my advice.


Agreed. Having 634 columns is just fool (no offense intended) and very ineficient. Don't forget you are using a relational database management system. I really doubt that you got no redundancy in these columns.

Share this post


Link to post
Share on other sites
Not that you need another person saying it, but this really sounds like you need to think about your design. Are you aware of the different normalized forms of databases? If you normalized this may be easier to manage. If it already is normalized you might want to think about the design in general. That many columns is just unmanageable. If you really need to keep this massive table maybe using views (the latest MySQL supports them I believe) would be an option to break it up into manageable parts.

Share this post


Link to post
Share on other sites
I know it's already been said, but, if I had a 600-field table in my database, I would seriously consider using something other than a naked database. I would possibly serialize the object as XML or even binary data, keeping only the most used fields (an index, maybe variables you will want to select on). Another option would be to drop the database and think up something else.

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
Quote:
Original post by Dave
My tables don't usualy have more than about 5 columns.
Oo. Here's a cookie for you. *gives cookie*

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this