Jump to content
  • Advertisement
Sign in to follow this  
Thevenin

Simpler MySQL 'insert into' command?

This topic is 4303 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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
Advertisement
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.

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
INSERT INTO something SET x='5', y='3', z='f', ...
should avoid the problem of having to line up column names and values.

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
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!