Help with SQL

[iwtLive]

hello!,
I'm trying to get the sum of the skill points a player used on skills, reset his skills and add that sum to the remaining SP, basically a SP resetter.
I can probably figure out how to reset the skills and add that sum into the remaining sp, but I'm having a small problem with figuring how to get that sum and I will really appriciate it if anyone could assist me!
I need a statement like (SELECT skilllevel FROM skills WHERE characterid = " + getCharId()) - and somehow sum all of them up and put them into a variable uhhhh

[MentaL]

[Naive]

select sum(skilllevel) from skills where characterid = "+getId()+"

[iwtLive]

select sum(skilllevel) from skills where characterid = "+getId()+"

thank you i will be testing that now, to put it inside a variable is getInt right?

[Fraysa]

thank you i will be testing that now, to put it inside a variable is getInt right?

The command will show a resultset with one row displaying the sum, so you have to use getInt to return it.

[iwtLive]

The command will show a resultset with one row displaying the sum, so you have to use getInt to return it.

thank you!
so it should be done like this with getInt("sum") i suppose

    public void spReset() {
        try { // sums up all the skill points
            PreparedStatement ps = DatabaseConnection.getConnection().prepareStatement("SELECT sum(skilllevel) FROM skills WHERE characterid = " + getId() + "");
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                int cnt = rs.getInt("sum");
                gainRemainingSp(cnt); // adds the sum to the remaining sp
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            dropMessage(6,"Something went wrong");
            return;
        }
            for (MapleData skill_ : MapleDataProviderFactory.getDataProvider(new File(System.getProperty("wzpath") + "/" + "String.wz")).getData("Skill.img").getChildren()) { //sets the sp to 0
                try {
                    Skill skill = SkillFactory.getSkill(Integer.parseInt(skill_.getName()));
                    changeSkillLevel(skill, (byte) 0, skill.getMaxLevel(), -1);
                } catch (NumberFormatException nfe) {
                    break;
                } catch (NullPointerException npe) {
                    continue;
                }
            }
    } 

[Fraysa]

I suppose. I suggest iterating over all the skills that the character has, because going through all the available skills is just waste of time.

[iwtLive]

I suppose. I suggest iterating over all the skills that the character has, because going through all the available skills is just waste of time.

you mean check for job and use changeSkillLevel for each skill invidually?

uhh i'm getting the "Something went wrong" so.. what could be the problem?x:

- - - Updated - - -

Hello again,
I'm getting a weird problem now.

    public void spReset() {
        try { // sums up all the skill points
            int cnt = 0;
            PreparedStatement ps = DatabaseConnection.getConnection().prepareStatement("SELECT sum(skilllevel) FROM skills WHERE characterid = " + getId() + "");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                cnt = rs.getInt(1);
                cnt += cnt;
            }
            gainRemainingSp(cnt); // adds the sum to the remaining sp
            rs.close();
            ps.close();
        } catch (Exception e) {
            dropMessage(6,"Something went wrong, please alert a GM.");
            return;
        }
            for (MapleData skill_ : MapleDataProviderFactory.getDataProvider(new File(System.getProperty("wzpath") + "/" + "String.wz")).getData("Skill.img").getChildren()) { //sets the sp to 0
                try {
                    Skill skill = SkillFactory.getSkill(Integer.parseInt(skill_.getName()));
                    changeSkillLevel(skill, (byte) 0, skill.getMaxLevel(), -1);
                } catch (NumberFormatException nfe) {
                    break;
                } catch (NullPointerException npe) {
                    continue;
                }
            }
    } 

This semi-works because when it should have brought me back 77 sp it only brought me back 37 sp, any ideas?x:

[Naive]

Try this.

public void spReset() {       
 try { // sums up all the skill points
            int cnt = 0;
            PreparedStatement ps = DatabaseConnection.getConnection().prepareStatement("SELECT sum(skilllevel) FROM skills WHERE characterid = " + getId() + "");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                cnt = rs.getInt("sum(skilllevel)");
            }
            gainRemainingSp(cnt); // adds the sum to the remaining sp
            rs.close();
            ps.close();
        } catch (Exception e) {
            dropMessage(6,"Something went wrong, please alert a GM.");
            return;
        }
            for (MapleData skill_ : MapleDataProviderFactory.getDataProvider(new File(System.getProperty("wzpath") + "/" + "String.wz")).getData("Skill.img").getChildren()) { //sets the sp to 0
                try {
                    Skill skill = SkillFactory.getSkill(Integer.parseInt(skill_.getName()));
                    changeSkillLevel(skill, (byte) 0, skill.getMaxLevel(), -1);
                } catch (NumberFormatException nfe) {
                    break;
                } catch (NullPointerException npe) {
                    continue;
                }
            }
    }

[iwtLive]

Try this.

public void spReset() {       
 try { // sums up all the skill points
            int cnt = 0;
            PreparedStatement ps = DatabaseConnection.getConnection().prepareStatement("SELECT sum(skilllevel) FROM skills WHERE characterid = " + getId() + "");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                cnt = rs.getInt("sum(skilllevel)");
            }
            gainRemainingSp(cnt); // adds the sum to the remaining sp
            rs.close();
            ps.close();
        } catch (Exception e) {
            dropMessage(6,"Something went wrong, please alert a GM.");
            return;
        }
            for (MapleData skill_ : MapleDataProviderFactory.getDataProvider(new File(System.getProperty("wzpath") + "/" + "String.wz")).getData("Skill.img").getChildren()) { //sets the sp to 0
                try {
                    Skill skill = SkillFactory.getSkill(Integer.parseInt(skill_.getName()));
                    changeSkillLevel(skill, (byte) 0, skill.getMaxLevel(), -1);
                } catch (NumberFormatException nfe) {
                    break;
                } catch (NullPointerException npe) {
                    continue;
                }
            }
    }

It brought me back 105 sp instead of 83, uh thats weird.
I'm starting to think I should just count the sp by level

[hecari]

It brought me back 105 sp instead of 83, uh thats weird.
I'm starting to think I should just count the sp by level

If the code gives you sp back, then probably it's working fine. As on why it give you back 105 instead of 83, you have to debug it. I recommend starting on finding how much is cnt .

public void spReset() {       
 try { // sums up all the skill points
            int cnt = 0;
            PreparedStatement ps = DatabaseConnection.getConnection().prepareStatement("SELECT sum(skilllevel) FROM skills WHERE characterid = " + getId() + "");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                cnt = rs.getInt("sum(skilllevel)");
            }
            System.out.println("Database give " + cnt + " sp");
            gainRemainingSp(cnt); // adds the sum to the remaining sp
            rs.close();
            ps.close();
        } catch (Exception e) {
            dropMessage(6,"Something went wrong, please alert a GM.");
            return;
        }
            for (MapleData skill_ : MapleDataProviderFactory.getDataProvider(new File(System.getProperty("wzpath") + "/" + "String.wz")).getData("Skill.img").getChildren()) { //sets the sp to 0
                try {
                    Skill skill = SkillFactory.getSkill(Integer.parseInt(skill_.getName()));
                    changeSkillLevel(skill, (byte) 0, skill.getMaxLevel(), -1);
                } catch (NumberFormatException nfe) {
                    break;
                } catch (NullPointerException npe) {
                    continue;
                }
            }
    }

And, I don't think that's the correct way to do preparestatement. It doesn't sanitise. Not really sure though. I only did preparestatement in PHP before but it shouldn't matter in this case.

[iwtLive]

it's fine i'm done dealing with this, everything just goes wrong
-rq-

[Fraysa]

it's fine i'm done dealing with this, everything just goes wrong
-rq-

You won't get far in life if you just throw away everything because of a minor error. Sometimes the answer is just beneath your nose. I suggest taking @hecari advice and just debug it to see how much exactly it gives the player, or manually execute the sql command to see how much it returns (in the sql query browser) -- that'd be much easier.

[iwtLive]

I don't know how to do any of these lol.. (debugging, execute the sql manually) just never mind it :p

[Naive]

I don't know how to do any of these lol.. (debugging, execute the sql manually) just never mind it :p

@hecari already provided you what you needed to debug. Maybe your calculations are wrong? Maybe it should be 103. Do what @Fraysa said with entering the sql command in a query browser. select sum(skilllevel) from skills where characterid = (get your character id and put it here) and execute it. If the return value is 103, then it's 103 and your calculations were in correct. If you want to see what skills added up to 103, select * from skills where characterid = (your character id). You may have missed the beginner SP count.