SQLEXCEPTION : Bad value for type long

Question

EDIT: I fixed the insert off by one mistake so I deleted that part of the question but still have the Bad value for type long error.

EDIT: the create statement for the small company table

create table small_company
(
    first_name varchar(20),
    last_name varchar(30),
    address text,
    emp_id serial not null
)
;

Created from:

create table company
(
    first_name varchar(20),
    last_name varchar(30),
    company_name text,
    address text,
    city varchar(30),
    province text,
    postal varchar(7),
    phone1 text,
    phone2 text,
    email text,
    web text
)
;

create index name_priority
    on company (first_name)
;

I'm getting this error saying "Bad value for type long :"

// insert values into the "small_company" table

long insertEmp(String first_name, String last_name, String address) {

    String sql = "insert into small_company values (?,?,?)";

    long id = 0;

    try {
        Connection conn = connect();
        PreparedStatement pstmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

        pstmt.setString(1,first_name);
        pstmt.setString(2,last_name);
        pstmt.setString(3,address);

        int affectedRows = pstmt.executeUpdate(); // returns number of affected rows

        if (affectedRows > 0) { // means a row was affected so get the ID

            try {
                ResultSet rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    id = rs.getLong(1); // if cursor finds row with id, return it
                }
            }

            catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }

    catch (SQLException e){
        System.out.println(e.getMessage());
    }

    return id;
}

public static void main(String[] args) {

    Queries q1 = new Queries();
    //q1.getRowCount("company");
    //q1.displayTable();
    //q1.displayCompany(); // <- what I use to display table!
    //q1.findEmployee(10);
    q1.insertEmp("Laura","Lane","Daily St. Planet co. NE"); //<-what I use to insert!
}

All help is greatly appreciated!

Remember to close your Statement and Connection. This is easily done by replacing your `try` with a [try-with-resources statement](https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html): `try (Connection conn = connect(); PreparedStatement pstmt = con.prepareStatement(…)) {` — VGR, Aug 16 '18 at 17:38
@ a_horse_with_no_name oh apologies, I was using DataGrip and it was out of sync, I did alter the table and add serial a long time ago. — pear, Aug 16 '18 at 19:17

score 1 · Answer 1 · 2018-08-16T20:08:50.673

1

The first column in small_company is first_name and you can't use getLong() on a varchar column. You have to use getLong("emp_id") instead.

Alternatively you can tell the JDBC driver to only return the emp_id:

PreparedStatement pstmt = conn.prepareStatement(sql,new String[]{"emp_id"});

Then getLong(1) should work as well as only one column is returned (at least with an up-to-date JDBC driver).

edited Aug 16 '18 at 20:08

answered Aug 16 '18 at 20:01

Oh I understand. Thank you so much! – pear Aug 16 '18 at 20:24

SQLEXCEPTION : Bad value for type long

1 Answers1