Simpler MySQL 'insert into' command?
Author
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();
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.
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
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
Author
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]
INSERT INTO something SET x='5', y='3', z='f', ...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.
Quote:Original post by DaBonoQuote: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
Quote:Original post by DaBonoQuote: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.
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
