SQL ALTER user issue | Together Java | Page 1

alpine raven Apr 28, 2024, 9:58 AM

#

Why does this not work? ```try (Connection conn = db.getConnection()) {

            try (PreparedStatement stm = conn.prepareStatement("ALTER USER ? WITH (usersTime,usersCooldown,usersAccess,userIsRoot) VALUES"
                    + "(?, ?, ?, ?)")) {
                stm.setString(1, userToEdit);
                stm.setInt(2, usersTime);
                stm.setInt(3, usersCooldown);
                stm.setInt(4, usersAccess);
                stm.setInt(5, userIsRoot);
                stm.executeUpdate();
                
            } catch(SQLException s) {
                s.printStackTrace();
                System.out.println("Unable to edit user.");
            }
            
        } catch(SQLException sq) {
            
            
            
        }


Error: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(usersTime,usersCooldown,usersAccess,userIsRoot) VALUES12312, 10, 1, 0' at line 1

thorny vergeBOT Apr 28, 2024, 9:58 AM

#

alpine raven Why does this not work? ```try (Connection conn = db.getConnection()) { ...

Detected code, here are some useful tools:

#

<@&987246399047479336> please have a look, thanks.

alpine raven Apr 28, 2024, 10:01 AM

#

Also, this throws the same error: ```try (Connection conn = db.getConnection()) {

            try (PreparedStatement stm = conn.prepareStatement("ALTER USER WHERE usersUid = ? WITH (usersTime,usersCooldown,usersAccess,userIsRoot) VALUES"
                    + "(?, ?, ?, ?)")) {
                stm.setString(1, userToEdit);
                stm.setInt(2, usersTime);
                stm.setInt(3, usersCooldown);
                stm.setInt(4, usersAccess);
                stm.setInt(5, userIsRoot);
                stm.executeUpdate();
                
            } catch(SQLException s) {
                s.printStackTrace();
                System.out.println("Unable to edit user.");
            }
            
        } catch(SQLException sq) {
            
            
            
        }```

thorny vergeBOT Apr 28, 2024, 10:01 AM

#

alpine raven Also, this throws the same error: ```try (Connection conn = db.getConnection()) ...

Detected code, here are some useful tools:

devout nebula Apr 28, 2024, 10:32 AM

#

What are you trying to do

alpine raven Apr 28, 2024, 10:40 AM

#

Just edit the user which was specified with the given values of a method

thorn sage Apr 28, 2024, 10:48 AM

#

You want update:

UPDATE LOW_PRIORITY table3
SET col1 = 'text-a', col2='text-b'
WHERE id < 100;

alpine raven Apr 28, 2024, 12:11 PM

#

Ok can I replace ‚text a‘ with ? Also

#

And what does low_priority do exactly

devout nebula Apr 28, 2024, 12:42 PM

#

Whenever you have issues with SQL/repos in Java, try to get the query to work first without java (command line interface with the db). Then you will know if the issue is your query or your code

alpine raven Apr 28, 2024, 12:43 PM

#

ok thanks

alpine raven Apr 28, 2024, 2:05 PM

#

                
                try (PreparedStatement stm = conn.prepareStatement("UPDATE LOW_PRIORITY users"
                        + "SET usersTime = ?, usersCooldown = ?, usersAccess = ?, userIsRoot = ? "
                        + "WHERE usersUid = ?;")) {
                    stm.setInt(1, usersTime);
                    stm.setInt(2, usersCooldown);
                    stm.setInt(3, usersAccess);
                    stm.setInt(4, userIsRoot);
                    stm.setObject(5, userToEdit);
                    stm.executeUpdate();
                    
                } catch(SQLException s) {
                    s.printStackTrace();
                    System.out.println("Unable to edit user.");
                }
                
            } catch(SQLException sq) {
                
                
                
            }
            
        }``` still no

thorny vergeBOT Apr 28, 2024, 2:05 PM

#

alpine raven ``` try (Connection conn = db.getConnection()) { ...

Detected code, here are some useful tools:

grim glade Apr 28, 2024, 3:05 PM

#

@alpine raven you know you can make multi line strings, right?

#

            try (Connection conn = db.getConnection()) {
                
                try (PreparedStatement stm = conn.prepareStatement("""
                        UPDATE LOW_PRIORITY users
                        SET 
                            usersTime = ?, 
                            usersCooldown = ?, 
                            usersAccess = ?, 
                            userIsRoot = ? 
                        WHERE usersUid = ?;
                    """)) {
                    stm.setInt(1, usersTime);
                    stm.setInt(2, usersCooldown);
                    stm.setInt(3, usersAccess);
                    stm.setInt(4, userIsRoot);
                    stm.setObject(5, userToEdit);
                    stm.executeUpdate();
                    
                } catch(SQLException s) {
                    s.printStackTrace();
                    System.out.println("Unable to edit user.");
                }
                
            } catch(SQLException sq) {
                
                
                
            }
            
        }

alpine raven Apr 28, 2024, 3:08 PM

#

oh

#

didnt realise

#

ill try that

#

but how do I clarify WHICH user to edit

#

oh nvm

grim glade Apr 28, 2024, 3:10 PM

#

also you can combine try blocks if you want

#

try (Connection conn = db.getConnection();
     PreparedStatement stm = conn.prepareStatement("""
                        UPDATE LOW_PRIORITY users
                        SET 
                            usersTime = ?, 
                            usersCooldown = ?, 
                            usersAccess = ?, 
                            userIsRoot = ? 
                        WHERE usersUid = ?;
                    """)) {

}

#

sorry indentation is wonky

#

but this is allowed

alpine raven Apr 28, 2024, 3:17 PM

#

ok I see

#

deffo gonna try it later

alpine raven Apr 28, 2024, 8:05 PM

#

grim glade ```java try (Connection conn = db.getConnection()) { ...

this worked btw,

#SQL ALTER user issue