CODEWITHSUNDEEP
javasql-serverjdbc
user301016
user301016

Reputation: 2237

Insert query - executeUpdate returning -1

I am trying to insert records into SQL Server using jdbc conn (in java). I am able to insert into SQL, if I manually copy the query statement in the java file. But its not inserting from the code?

Please help, where am I committing mistake?

           PreparedStatement preparedStatement = null;

        if (conn != null) {                 
            System.out.println("Connection Successful!");             
        } 

        //Create a Statement object
        Statement sql_stmt = conn.createStatement();

         //Create a Statement object
        Statement sql_stmt_1 = conn.createStatement();

        //Result Set for Prouduct Table
        ResultSet rs  = sql_stmt.executeQuery("SELECT MAX(ID), MAX(RG_ID), MAX(WG_ID) FROM " + strDBName + ".[dbo].Product");

        if ( rs.next() ) {     
            // Retrieve the auto generated key(s).     
            intID = rs.getInt(1); 
            intRG_ID = rs.getInt(2); 
            intWG_ID = rs.getInt(3); 
        }

        for (int iCount = 0 ;iCount < arrListLevel_1_Unique.size(); iCount++)
        {

         //Result Set for Prouduct Table


        sql_stmt_1.executeUpdate("\n IF NOT EXISTS(SELECT 1 FROM " + strDBName + ".[dbo].Product WHERE [Name] NOT LIKE '" + arrListLevel_1_Unique.get(iCount) + "') "
                + "\nINSERT INTO " + strDBName + ".[dbo].Product ([Name] ,"
                + "[RG_ID],[WG_ID],[Parent_Product]) "
                + "VALUES ( '" + arrListLevel_1_Unique.get(iCount) + "',"
                + + (intWG_ID + intRowIncrement) + ", " + (intWG_ID + intRowIncrement + 1) + ", 5828)");


        intRowIncrement++ ;
        }

    rs.close();
        sql_stmt.close();
        sql_stmt_1.close();


        //Close the database connection
        conn.close();

Upvotes: 2

Views: 9768

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

You have two plus signs + in the fifth row:

+ + (intWG_ID + intRowIncrement) + ...

Otherwise, the problem may lie in the IF ... statement. You can try this instead:

    sql_stmt_1.executeUpdate(
        " INSERT INTO " + strDBName + ".[dbo].Product ([Name] ,"
      + "[RG_ID],[WG_ID],[Parent_Product]) "
      + " SELECT '" + arrListLevel_1_Unique.get(iCount) + "',"
      + (intWG_ID + intRowIncrement) + ", "
      + (intWG_ID + intRowIncrement + 1) + ", 5828 "
      + " WHERE NOT EXISTS( SELECT 1 FROM " + strDBName
      + ".[dbo].Product WHERE [Name] LIKE '"
      + arrListLevel_1_Unique.get(iCount) + "') "
    ) ;

Upvotes: 1

user591593
user591593

Reputation:

I think the problem lies on the "\n", have you tried eliminating those 2 of "\n" and see if it's working?

Actually this kind of implementation (building SQL string with string concatenation) is really bad. At first is prone to SQL injection, and then secondly you will have problem if the value to be inserted contains character single quote or ampersand.

Instead, you should use "prepare statement".

And it's tidier to store the SQL string into a variable before executing it. So that you can log it (for debug purpose), roughly something like this:

String sqlCommand = "select * from " + tableName;
System.out.println(sqlCommand);
sqlStatement.executeUpdate(sqlCommand);

P.S. it is not advised to use system.out.println for debug, you should implement a proper logging system.

Upvotes: 1

Related Questions