CSV madness

[Krypticpain]

Well, Ive started the daunting task of creating a pserver. Im doing it for fun and to learn a lot on the way. I am a Dekaron and pserver noob, but I think I am learning fast and have been reading ePvP and DKUnderground for weeks.

Right now I am trying to get a grip on the CSV madness I find myself in. There HAS to be a better way to handle all these CSVs.

1) Import the CSVs and file DB structure into MS Access. I can create forms that will generate PCs, items and NPCs. It will also give the ability to generate reports that show the exp/item curves vs mob lvl to help with zone balancing.

2) Focus on SQL. Learn SQL in and out to create resuable scripts to generate items/chars etc. This could be done with notepad++. I figure learned SQL will have huge benefits anyway.

3) Do it all by hand in the SQL enterprise manager. I dont know why but this option nags me. I feel like there is an easier way.

I plan on sharing a great deal of my work with the community, only holding back what may risk the security of the server.

What are peoples thoughts, recommendations or requests? I know Access 2013 in and out and have a good grasp of SQL. I just dont have the experience with Dekaron to know what is worth the time.

My weekend project is going to get a SQL script written to generate PCs with appropriate GM stats.

[janvier123]

wow, good luck then

[Krypticpain]

Originally Posted by janvier123

wow, good luck then

Why thank you. I hope to have something for you to see by Monday. Just a little something to get started.

[Krypticpain]

Okay, so I wrote a simple SQL query that can be used to create a GM character and update his stats at the same time. If you do some research you will find it similar to KingTut's GM code with a twist.

1. Create a character on the server
2. Open MS Server SQL Query Analyzer
3. Enter this code:

 Spoiler

Use character
Go
Update [user_character]
Set dwMoney=5000000000,
dwStorageMoney=5000000000,
nHP=65000,
nMP=65000,
wStr=3000,
wDex=3000,
wCon=3000,
wSpr=3000,
wStatPoint=1000,
wSkillPoint=300,
wLevel=250,
nShield=65000,
character_name='[GM]Name'
Where character_name = 'Name';

4. Enter the character's name at the bottom, the GM name and feel free to play around with the values.

Feel free to ask questions if I was unclear.

Edit: Added Spoilers

[Decima]

declare @charname char(19)
set @charname = 'paste name here'

Use character
Go 
Update [user_character]
Set dwMoney=900000000,
dwStorageMoney=900000000,
wStr=65500,
wDex=65500,
wCon=65500,
wSpr=65500,
wStatPoint=0,
wSkillPoint=1000,
wLevel=250,
character_name='[GM]'@charname
Where character_name = @charname;

nShield=65000,
nHP=65000
nMP=65000, // not necessary, /gm heal in game will fill ur hp/mp, and it is set from ur stats when u login and all ur stats set to almost max, unless u want ppl to be able to kill ur gm, and im not really sure id 5 billion is going to be ok in ur inv, i think max is only 1, u can mail them money in game as well so i would remove that as well.

[janvier123]

and why should you give GMs direct access to the database, thats just wrong !

[Decima]

hes not?

just a query to run to make someone a GM right?

[janvier123]

lol, ok here is my version (Fixed ofc)

PHP Code:

declare @charname varchar(40) // YES 40, not 19
set @charname = 'paste name here'

Use character
Go 
Update [user_character]
Set 
character_name = '[GM]'@charname
Where character_name = @charname; 


[Krypticpain]

Thank you both for the replies. I really like the declared variables as that makes it easier to interact with.

I started this code because I am at the point where I am creating many characters to test skills, armor sets and animations. This script lets me copy paste instead of manually going into the tables.

I am definitely NOT giving non-dev personal access to the database.

[Decima]

i thought max character name length was 25?

minus the 4 needed for the [GM] should had been 21 (not 19, thats my bad) but yeah making it 40, kinda overkill when u can only have 25 max :P plus what if the orginal name is 25 already, and u add 4 more characters to it? db wont like that, and i doubt the game would either

and would be a good idea to set the stats max so players dont kill them every time they log on

declare @charname varchar(21) // 21, becus max character name length is 25, minus the 4 characters needed for '[GM]'
set @charname = 'paste name here' // if original name length is 25 it will cut 4 off for the GM name and wont bug out the db

Use character
Go 
Update [user_character]
Set 
wStr=65500, // Max their stats so ******* players dont just kill them @ login
wDex=65500,
wCon=65500,
wSpr=65500,
wStatPoint=0, // clear any extra points they may have so they dont bug their GM
wSkillPoint=1000, // give them plenty of skill points to max their skills
wLevel=250, // max their level so they dont gain any extra stat points to bug their gm with
character_name = '[GM]'@charname
Where character_name = @charname;

and if ur smart and dont wanna learn the hard way, dont ever give anyone access to the db, not even ur mom

[Krypticpain]

Does the @ sign have a special function in SQL or is it just for your visual reference? I learned SQL through MS Access so Im still picking things up.

[Decima]

yes, the @ symbol is what tells the SQL that it is a variable

like the $ in php, and the % in asp

[Krypticpain]

Originally Posted by Decima

yes, the @ symbol is what tells the SQL that it is a variable

like the $ in php, and the % in asp

Thank you, makes everything clear now.

[janvier123]

Originally Posted by Decima

i thought max character name length was 25?

Nope, here is some stuff that can back me up

PHP Code:

--Table: dbo.user_character

CREATE TABLE dbo.user_character (
  character_no       varchar(18),
  character_name     varchar(40) NOT NULL, 


PHP Code:

CREATE PROCEDURE dbo.SP_CHAR_CREATE
    @user_no            varchar(14)                ,
    @character_name        varchar(40) 


PHP Code:

CREATE   PROCEDURE dbo.SP_GUILD_GUILDMASTER_UPDATE
    @i_guild_code            VARCHAR(10)        ,    --길드코드
    @i_from_character_name    VARCHAR(40)        ,    --이전길드장캐릭터명
    @i_to_character_name    VARCHAR(40)        ,    --위임길드장캐릭터명 


seems its 40 to me
anyway, yea it is kinda big but it must have some reason why they made it that big

[Decima]

have u tried making a 40 character name and then logging in game tho?

it only reads 25 characters. . . . .

just cus the db is expecting 40 characters or less doesnt mean the game will :P