Results 1 to 7 of 7
  1. #1
    poliocertes is offline Member
    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0

    Default Insert value selected from another table with JDBC

    I need to insert value into Postgres table. Part of them is selected from another table. See below:

    Java Code:
    INSERT INTO grafik(kalendarz, status, id_pracownika, czy_rozliczony)
    VALUES 
    ('08.grudzień',(SELECT id_rodzaj_dnia FROM rodzaj_dnia WHERE rodz_dnia='dzien_wolny'),'1','0');
    I did a code but it's not working.


    Java Code:
    				Connection c = null;
    				Connection c1 = null;
    				Statement stmt = null;
    				try 
    				{
    					Class.forName("org.postgresql.Driver");
    					c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/jacek","jacek", "zuza");
    					c1 = DriverManager.getConnection("jdbc:postgresql://localhost:5432/jacek","jacek", "zuza");
    					c.setAutoCommit(false);
    					System.out.println("Dostęp do bazy danych \n");
    					stmt = c.createStatement();
    					String sql =" INSERT INTO grafik (kalendarz, status, id_pracownika, czy_rozliczony)" + "VALUES ('"+ kalendarz +"', (SELECT id_rodzaj_dnia FROM rodzaj_dnia WHERE rodz_dnia='"+stat+"'), " +prac+", 0));";
    					stmt.executeUpdate(sql);
    					stmt.close();
    					c.commit();
    					c.close();
    				}
    				catch (Exception d)
    				{
    					System.err.println( d.getClass().getName()+": "+ d.getMessage() );
    					System.exit(0);
    				}
    				System.out.println("Rekord utworzony");
    			}
    Please give some kind of instruction how to do this properly. Thanks.

  2. #2
    poliocertes is offline Member
    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0

    Default Re: Insert value selected from another table with JDBC

    Anyone?

  3. #3
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default Re: Insert value selected from another table with JDBC

    Quote Originally Posted by poliocertes View Post
    I need to insert value into Postgres table. Part of them is selected from another table. See below:

    Java Code:
    INSERT INTO grafik(kalendarz, status, id_pracownika, czy_rozliczony)
    VALUES 
    ('08.grudzień',(SELECT id_rodzaj_dnia FROM rodzaj_dnia WHERE rodz_dnia='dzien_wolny'),'1','0');
    That would be more effeciently written as
    Java Code:
    insert into grafik(kalendarz, status, id_pracownika, czy_rozliczony)
    select '08.grudzień', id_rodzaj_dnia, '1', '0' FROM rodzaj_dnia WHERE rodz_dnia='dzien_wolny'
    Then, you can create that statement as a prepared statement replacing the "fixed" values with a ? and using PreparedStatements "set" moethods to set those values.

  4. #4
    gimbal2 is online now Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,702
    Rep Power
    5

    Default Re: Insert value selected from another table with JDBC

    "Its not working" contains no information at all. You get an error? If so: post it.

    You may want to try using a PreparedStatement in stead of a regular statement (google it). I also don't understand why you open two connections to the same database, you're only using one of them. As far as I can see anyway, part of the code is invisible to me.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  5. #5
    poliocertes is offline Member
    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0

    Default Re: Insert value selected from another table with JDBC

    Java code compiles fine without any error. All I got is error in java console:

    org.postgresql.util.PSQLException: ERROR: syntax arror at or near ")"

  6. #6
    gimbal2 is online now Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,702
    Rep Power
    5

    Default Re: Insert value selected from another table with JDBC

    So the SQL you're generating is invalid. Print it out with a System.out statement, copy/paste it into your PostgreSQL client tool and figure out what is wrong with it, then correct the mistake.

    What has been suggested so far is good advice, but at this point it is more valuable that you learn how to read errors, investigate them and correct them.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  7. #7
    poliocertes is offline Member
    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0

    Default Re: Insert value selected from another table with JDBC

    Quote Originally Posted by masijade View Post
    That would be more effeciently written as
    Java Code:
    insert into grafik(kalendarz, status, id_pracownika, czy_rozliczony)
    select '08.grudzień', id_rodzaj_dnia, '1', '0' FROM rodzaj_dnia WHERE rodz_dnia='dzien_wolny'
    Then, you can create that statement as a prepared statement replacing the "fixed" values with a ? and using PreparedStatements "set" moethods to set those values.
    Thanks a lot. It works.

Similar Threads

  1. [ask] error when selected table
    By halim in forum New To Java
    Replies: 14
    Last Post: 08-02-2013, 06:15 PM
  2. JButton to detect if cell in a table is selected
    By Levian in forum New To Java
    Replies: 5
    Last Post: 08-09-2011, 06:56 AM
  3. Replies: 0
    Last Post: 07-11-2008, 04:30 PM
  4. Replies: 0
    Last Post: 07-11-2008, 04:29 PM
  5. merging selected table cells using javascript
    By Renjini in forum JavaServer Pages (JSP) and JSTL
    Replies: 2
    Last Post: 06-17-2008, 01:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •