Home > Default > Dynamic insertion of data in a Dynamic Column in a table

Dynamic insertion of data in a Dynamic Column in a table

October 11Hits:0
Advertisement
Hi EveryBody ,
I have a table where i am increasing the column dynamically . I need to insert data through PreparedStatement Like
pst = con.prepareStatement(CBBsqlConstants.addOrderItem);
               pst.setString(1,ein);
               pst.setString(2,insert_date);
               pst.setString(3,checkList);
               pst.setString(4,Quantities);
               pst.setDate(5,pick_date);
               pst.setDate(6,completed_date);
               pst.setString(7,comment);
               pst.setInt(8,status);
               pst.setString(9,agent_ein);
               i = pst.executeUpdate();
But here my column is increasing dynamically, so the above cant be constant as column is incresing . how do i handle the insertion part dynamically.
Thanks So much . Please help with this .

Answers

Server_java wrote:
Ya you are right ,
Take i am ordering some Items and quantity from checkbox and inserting that to the table , each item and quantity is going to consume a row , but when i am going have column for each item , all the items i am going to select is going to appear in a single row . so i am consuming .But only 256 column is allowed for a table ,but my item is not going to excced that . That maximum number of columns is the least of the problems here.
The problem is that you are taking data that should be in another table and turning it into metadata instead. That's a mistake because it makes your entire application brittle and it doesn't need to be. It also will make querying your table a nightmare.
Let's take a look at your solution and then the correct solution.
Your solution (condensed)
tblOrder
id
customername
apples
oranges
bananas
cherries
Sample data (CSV format for the forum)
1,"John Smith",0,0,0,1
2,"Jane Smith",1,0,0,3
3,"Kate Smith",0,2,1,0
The correct solution
tblOrder
id
customername
Sample data
1,"John Smith"
2,"Jane Smith"
3,"Kate Smith"
tblProduct
id
name
Sample data
1, "Apples"
2,"Oranges"
3, "Bananas"
4, "Cherries"
tblOrderItem
orderid
productid
quantity
Sample data
1,4,1
2,1,1
2,4,3
3,2,2
3,3,1
So what's the difference?
With your design what happens when you want to add a new fruit? Your schema changes and all your code breaks. With my design you simply insert one row and that's it.
And what happens if you do happen to eventually need more than 250 odd fruits? With your design you are screwed. With my correct design it's never going to be a problem.
And consider that with my design you can populate user inteface components using actual data and not table meta data.
And the list goes on... the point is the only correct solution is to use a proper relational design.

Read other 15 answers

Tags:

Related Articles

  • Query to get the data of all the columns in a table except any one columnOctober 11

    Can anyone please tell how to write a query to get the data of all the columns in a table except one particular column.. For Example: Let us consider the EMP table., From this table except the column comm all the remaining columns of the table should

  • How to retrieve the data type of a column of a table?October 11

    Hi, I want to retrieve the data type of a column of a table. At the moment I am querying "OCI_ATTR_DATA_TYPE" attribute but it is returning SQLT_CHR for both varchar2 and nvarchar2 data type columns. I need to distinguish between these two data

  • Dynamical columns in internal tableNovember 30

    Hi all. I need to make an internal table with dynamical columns(i have 3 static columns and the rest depends on the number of clients that i have in a month). If it's possible, how can i do it? Can anyone please help. Thanks & RegardsHi, type-pools :

  • Multiple dynamic column in cross tableOctober 11

    Hi all, I have a cross table which stored the sale's quantity and amount. The layout in view result likes below: saler     quantity1 quantity2 quantity3 quantitysummary amount1 amount2 amount3 amount4 amountsummary S3           100           20 S2   

  • Dynamic Column Header On Table ControlNovember 30

    Dear Friend    How I can set Dynamic Column Header Text On Table Control Regards SupperkornJust set it to a global variable name, and then set the value of that global variable as needed, e.g. in your TOP include define "g_my_header(20) type c"

  • How do i find out  what is the data type of a column of a Table in oracle or SQL?October 11

    a) What if i want to find out the  Datatype of a specific column in the Table. b) How do i find the Column Datatypes? Can anyone please help me. I am new to oracle and Trying to Learn some stuffHi, How about doing SQL> desc <table_name> SQL> d

  • Show Data as percent of Column with static tableNovember 30

    Hello, I have a problem calculating a % in a table. I have two columns in criteria tab: Departments and measure I need an extra column that calc % of total departments for each department. I know that I can do it with pivot table creating a new colum

  • How to insert test data of 10,000 records into emp tableNovember 30

    Hi I'm new to oracle can anyone please help me in writing a program so that i can insert test data into emp tableHi, user11202607 wrote: thanks sanjay , frank . But how can i insert only 4 deptno's randomly and how can i insert only 10 managers rando

  • Update data provider queryspecification wiith columns from multiple tablesNovember 30

    Hi I have scenario like I need  updated a queryspecificaiton with columns from multiple tables 1. Ex:   <bOQuery name="Query">           <resultObjects identifier="DS0.DO1" name="A$Application_ID"/>           <

  • Dynamic insertion of data in a Dynamic Column in a tableOctober 11

    Hi EveryBody , I have a table where i am increasing the column dynamically . I need to insert data through PreparedStatement Like pst = con.prepareStatement(CBBsqlConstants.addOrderItem);                pst.setString(1,ein);                pst.setStr

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.860 s.