Insert statement with subquery and extra column

Question

I want to use insert statement with select subquery from another table. In my case, I want to add a row from PRESENTATION table to AVAILABILITY table.

AVAILABILITY table structure:

availableID        (number, generated using sequence)
availableDay       (varchar)
availableStart     (varchar)
availableEnd       (varchar)
lecturerID(FK)     (varchar)
presentationID(FK) (number, generated using sequence)

PRESENTATION table structure:

presentationID         (number, generated using sequence)
presentationDay        (varchar)
presentationStart      (varchar)
presentationEnd        (varchar)

I tried to construct the query in DAO like this :

        String lecturerID = Abean.getLecturerID();
        String availableDay = Abean.getAvailableDay();
        String availableStart = Abean.getAvailableStart();
        String availableEnd = Abean.getAvailableEnd();
        Date availableDate = Abean.getAvailableDate();
        String presentationID = Abean.getPresentationID();

    try{  

            currentCon = JavaConnectionDB.getConnection();

            PreparedStatement ps=currentCon.prepareStatement("
            insert into availability (availableID,
                                      lecturerID,
                                      availableDay,
                                      availableStart,
                                      availableEnd,
                                      presentationid) 
           select(availabilityseq.nextval,
                  ?,
                  presentationDay,
                  presentationStart,
                  presentationEnd,
                  presentationid) 
                  from presentation where presentationid=?

            ");  
            ps.setString(1,Abean.getLecturerID());  
            ps.setString(2,Abean.getAvailableDay());  
            ps.setString(3,Abean.getAvailableStart()); 
            ps.setString(4,Abean.getAvailableEnd());  
            ps.setString(5,Abean.getPresentationID()); 
           // ps.setString(6,Abean.getAvailableID());
            ps.executeUpdate();  

            }
    catch(Exception e){
      System.out.println("add availability 2 failed: An Exception has occurred! " + e);
    }

As expected, it return error

ERROR: java.sql.SQLException: Invalid column index

So, how can i do insert with subquery with extra column?

UPDATE: MY DAO codes

notice that in the subquery, the where clause is `presentation id=?` should be `presentationid=?` — Sharon Ben Asher, Apr 14 '16 at 10:51
My bad.. But after i change it, same error occur. Maybe i should show the rest of my DAO? — Elly, Apr 14 '16 at 10:58
you're query has only 2 question marks, but you supply 5 bind variables ?? — Sharon Ben Asher, Apr 14 '16 at 13:31
do i have to do something like `values (?,?,?,?,?)` ?? Is that what you mean? — Elly, Apr 14 '16 at 13:33
you need to understand what you're doing. where are the values for the new row coming from? if they all come from the Java code, then why do you need the subquery? you need to decide what is the relation of the two tables and how they are populated? where are the values coming from? — Sharon Ben Asher, Apr 14 '16 at 17:52
The new row coming from `PRESENTATION` table retrieved by `presentationID` chosen by user. Means that only `presentationID` comes from JAVA code. — Elly, Apr 15 '16 at 03:56
so what is `Abean`? where does it come from and why do you set 5 bind variables in the prepared stmt?? — Sharon Ben Asher, Apr 15 '16 at 19:46
`Abean` is from Availability Bean. Cause..... Isn't that how it works? I don't understand. Sorry :( — Elly, Apr 16 '16 at 02:53
first of all, the number of bind variables that you supply with `setString()` have to match the number of "?" symbols that you specify in the SQL text. It seems that in the SQL text you copy column values from `PRESENTATION` into `availability`, however, the use of `ABean` suggests that the values come from that Java Object. so which is which? — Sharon Ben Asher, Apr 16 '16 at 06:37
let's take one column as example - `availableDay` - it is either populated from `presentationDay` or from `Abean.getAvailableDay()` but cannot from both. — Sharon Ben Asher, Apr 16 '16 at 06:38
in that case, do not supply `Abean.getAvailableDay()` as bind variable. seems like you need to specify `ps.setString(1,Abean.getPresentationID());` and again with index 2 , to match the "?" symbols in ther SQL text — Sharon Ben Asher, Apr 18 '16 at 07:15
So i have to specify which bean for `Abean.getAvailableDay()` since its come from table `PRESENTATION`. Symbol `?` is to match value `lecturerID`. This ID is depends on current user who insert the data. `lecturerID' does not exist in `PRESENTATION` table — Elly, Apr 18 '16 at 08:20
what do you mean " which bean for Abean.getAvailableDay()"? this doesn't make sense. you dont need `Abean.getAvailableDay()` at all if the value for column `availableDay` comes from column `presentationDay` . your sql text seems fine, but you need to fix the `ps.setString` to match the text — Sharon Ben Asher, Apr 18 '16 at 08:57

Sharon Ben Asher · Accepted Answer · 2016-04-18T15:24:25.867

0

You need to match the setting of bind variables values with the bind variables specification in the SQL text :

EDIT Apr 18th: removed parenthesis from select subquery

PreparedStatement ps=currentCon.prepareStatement("
            insert into availability (availableID,
                                      lecturerID,
                                      availableDay,
                                      availableStart,
                                      availableEnd,
                                      presentationid) 
           select availabilityseq.nextval,
                  ?,
                  presentationDay,
                  presentationStart,
                  presentationEnd,
                  presentationid
                  from presentation where presentationid=?
            ");  
            ps.setString(1,Abean.getLecturerID());  // match first "?"
            ps.setString(2,Abean.getPresentationID());    // match 2nd "?"

edited Apr 18 '16 at 15:24

answered Apr 18 '16 at 08:57

Sharon Ben Asher

13,849
5
33
47

I am getting `NullPointerException` but it retrieve the right `presentationID` and `lecturerID`. I checked using S.O.P the `availabilityDay`,`availabilityStart` and `availabilityEnd` were null – Elly Apr 18 '16 at 10:12
where are you getting it? – Sharon Ben Asher Apr 18 '16 at 10:48
what do the presentationDay/Start/End contain for the specified `Abean.getPresentationID()` – Sharon Ben Asher Apr 18 '16 at 10:50
I'm sorry.. what do you mean? Can you give example? – Elly Apr 18 '16 at 10:53
example of what? I am asking you to debug/print the value of `Abean.getPresentationID()` and see what are the values of the row in presentation table – Sharon Ben Asher Apr 18 '16 at 10:57
Ohh haha. Well i get `581` for example, as i choose the ID. It should give me : Day: `Wednesday` Start: `08.00 AM` End: `10.00 AM` – Elly Apr 18 '16 at 10:59
from the day, start and end...? – Elly Apr 18 '16 at 11:22
you mean when you fetch the row that was just inserted? – Sharon Ben Asher Apr 18 '16 at 11:25
I am sorry, I have no idea why the null values. the SQL seems correct to my eyes – Sharon Ben Asher Apr 18 '16 at 11:39
Day, start and end should be null right? because I'm not retrieving the value from servlet.... Does that make sense? – Elly Apr 18 '16 at 11:44
Day, start and end are not taken from servlet. they are taken from `presentation` table row where `presentationID = 581` – Sharon Ben Asher Apr 18 '16 at 12:55
Yes. That's what i thought. Wonder what is the problem -.- – Elly Apr 18 '16 at 13:18
you need to run the insert statement in an SQL client/editor, with the bind variables substituted and see what happens like this: insert into availability (availableID, lecturerID, availableDay, availableStart, availableEnd, presentationid) select(availabilityseq.nextval, 581, presentationDay, presentationStart, presentationEnd, presentationid) from presentation where presentationid=581 – Sharon Ben Asher Apr 18 '16 at 13:22
i got `ORA-00907: missing right parenthesis`. The error between `availability.nextval,.....` I will try again. I am not satisfied haha – Elly Apr 18 '16 at 14:08
insert into availability (availableID, lecturerID, availableDay, availableStart, availableEnd, presentationid) select availabilityseq.nextval, 581, presentationDay, presentationStart, presentationEnd, presentationid from presentation where presentationid=581 – Sharon Ben Asher Apr 18 '16 at 15:19
you also need to correct the SQL text in the Java code, see edited answer – Sharon Ben Asher Apr 18 '16 at 15:22
AHHHHH That's a silly mistake. Worked on SQL. But still getting NPE in java. Trying to solve it. Thank you very much! – Elly Apr 18 '16 at 15:46
Still not working out honestly. Getting `java.lang.NullPointerException` – Elly Apr 18 '16 at 16:13
SOLVED. Thank you :) – Elly Apr 19 '16 at 05:54
Yes :D. Thank you again @sharonbn. I owe you big time hehe – Elly Apr 19 '16 at 07:33

Insert statement with subquery and extra column

1 Answers1