SQL Script for Media.pk2

[sladlejrhfpq]

Hi guys!
Here is my next release, I think it will be useful. It always took a lot time for me, to edit the client side stuff, and it was hard to do without mistakes, so I decided to write a stored procedure to make it easier.

What does it for?
-You can make any changes to the db, and do it fast client side too.

It will help you to edit ItemData, and CharacterData text files.

How to use?

1. Execute the following script, make sure that your db name is correct. By default, it is SRO_VT_SHARD

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_GetMediaLines]    Script Date: 02/05/2012 12:42:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  procedure  [dbo].[_GetMediaLines]

@Type SMALLINT,
@CodeName VARCHAR(30)

AS

IF(@Type=1)
BEGIN
	SELECT 
	_RefObjCommon.Service,_RefObjCommon.ID,_RefObjCommon.CodeName128,_RefObjCommon.ObjName128,_RefObjCommon.OrgObjCodeName128,_RefObjCommon.NameStrID128,_RefObjCommon.DescStrID128,_RefObjCommon.CashItem,_RefObjCommon.Bionic,_RefObjCommon.TypeID1,_RefObjCommon.TypeID2,_RefObjCommon.TypeID3,_RefObjCommon.TypeID4,_RefObjCommon.DecayTime,_RefObjCommon.Country,_RefObjCommon.Rarity,_RefObjCommon.CanTrade,_RefObjCommon.CanSell,_RefObjCommon.CanBuy,_RefObjCommon.CanBorrow,_RefObjCommon.CanDrop,_RefObjCommon.CanPick,_RefObjCommon.CanRepair,_RefObjCommon.CanRevive,_RefObjCommon.CanUse,_RefObjCommon.CanThrow,_RefObjCommon.Price,_RefObjCommon.CostRepair,_RefObjCommon.CostRevive,_RefObjCommon.CostBorrow,_RefObjCommon.KeepingFee,_RefObjCommon.SellPrice,_RefObjCommon.ReqLevelType1,_RefObjCommon.ReqLevel1,_RefObjCommon.ReqLevelType2,_RefObjCommon.ReqLevel2,_RefObjCommon.ReqLevelType3,_RefObjCommon.ReqLevel3,_RefObjCommon.ReqLevelType4,_RefObjCommon.ReqLevel4,_RefObjCommon.MaxContain,_RefObjCommon.RegionID,_RefObjCommon.Dir,_RefObjCommon.OffsetX,_RefObjCommon.OffsetY,_RefObjCommon.OffsetZ,_RefObjCommon.Speed1,_RefObjCommon.Speed2,_RefObjCommon.Scale,_RefObjCommon.BCHeight,_RefObjCommon.BCRadius,_RefObjCommon.EventID,_RefObjCommon.AssocFileObj128,_RefObjCommon.AssocFileDrop128,_RefObjCommon.AssocFileIcon128,_RefObjCommon.AssocFile1_128,_RefObjCommon.AssocFile2_128,
	_RefObjItem.MaxStack,_RefObjItem.ReqGender,_RefObjItem.ReqStr,_RefObjItem.ReqInt,_RefObjItem.ItemClass,_RefObjItem.SetID,_RefObjItem.Dur_L,_RefObjItem.Dur_U,_RefObjItem.PD_L,_RefObjItem.PD_U,_RefObjItem.PDInc,_RefObjItem.ER_L,_RefObjItem.ER_U,_RefObjItem.ERInc,_RefObjItem.PAR_L,_RefObjItem.PAR_U,_RefObjItem.PARInc,_RefObjItem.BR_L,_RefObjItem.BR_U,_RefObjItem.MD_L,_RefObjItem.MD_U,_RefObjItem.MDInc,_RefObjItem.MAR_L,_RefObjItem.MAR_U,_RefObjItem.MARInc,_RefObjItem.PDStr_L,_RefObjItem.PDStr_U,_RefObjItem.MDInt_L,_RefObjItem.MDInt_U,_RefObjItem.Quivered,_RefObjItem.Ammo1_TID4,_RefObjItem.Ammo2_TID4,_RefObjItem.Ammo3_TID4,_RefObjItem.Ammo4_TID4,_RefObjItem.Ammo5_TID4,_RefObjItem.SpeedClass,_RefObjItem.TwoHanded,_RefObjItem.Range,_RefObjItem.PAttackMin_L,_RefObjItem.PAttackMin_U,_RefObjItem.PAttackMax_L,_RefObjItem.PAttackMax_U,_RefObjItem.PAttackInc,_RefObjItem.MAttackMin_L,_RefObjItem.MAttackMin_U,_RefObjItem.MAttackMax_L,_RefObjItem.MAttackMax_U,_RefObjItem.MAttackInc,_RefObjItem.PAStrMin_L,_RefObjItem.PAStrMin_U,_RefObjItem.PAStrMax_L,_RefObjItem.PAStrMax_U,_RefObjItem.MAInt_Min_L,_RefObjItem.MAInt_Min_U,_RefObjItem.MAInt_Max_L,_RefObjItem.MAInt_Max_U,_RefObjItem.HR_L,_RefObjItem.HR_U,_RefObjItem.HRInc,_RefObjItem.CHR_L,_RefObjItem.CHR_U,_RefObjItem.Param1,_RefObjItem.Desc1_128,_RefObjItem.Param2,_RefObjItem.Desc2_128,_RefObjItem.Param3,_RefObjItem.Desc3_128,_RefObjItem.Param4,_RefObjItem.Desc4_128,_RefObjItem.Param5,_RefObjItem.Desc5_128,_RefObjItem.Param6,_RefObjItem.Desc6_128,_RefObjItem.Param7,_RefObjItem.Desc7_128,_RefObjItem.Param8,_RefObjItem.Desc8_128,_RefObjItem.Param9,_RefObjItem.Desc9_128,_RefObjItem.Param10,_RefObjItem.Desc10_128,_RefObjItem.Param11,_RefObjItem.Desc11_128,_RefObjItem.Param12,_RefObjItem.Desc12_128,_RefObjItem.Param13,_RefObjItem.Desc13_128,_RefObjItem.Param14,_RefObjItem.Desc14_128,_RefObjItem.Param15,_RefObjItem.Desc15_128,_RefObjItem.Param16,_RefObjItem.Desc16_128,_RefObjItem.Param17,_RefObjItem.Desc17_128,_RefObjItem.Param18,_RefObjItem.Desc18_128,_RefObjItem.Param19,_RefObjItem.Desc19_128,_RefObjItem.Param20,_RefObjItem.Desc20_128,_RefObjItem.MaxMagicOptCount,_RefObjItem.ChildItemCount
	FROM _RefObjCommon INNER JOIN _RefObjItem ON _RefObjCommon.Link=_RefObjItem.ID
	WHERE _RefObjCommon.CodeName128 LIKE @CodeName AND _RefObjCommon.Service=1 ORDER BY _RefObjCommon.ID ASC
END
IF(@Type=2)
	BEGIN
	SELECT 
	_RefObjCommon.Service,_RefObjCommon.ID,_RefObjCommon.CodeName128,_RefObjCommon.ObjName128,_RefObjCommon.OrgObjCodeName128,_RefObjCommon.NameStrID128,_RefObjCommon.DescStrID128,_RefObjCommon.CashItem,_RefObjCommon.Bionic,_RefObjCommon.TypeID1,_RefObjCommon.TypeID2,_RefObjCommon.TypeID3,_RefObjCommon.TypeID4,_RefObjCommon.DecayTime,_RefObjCommon.Country,_RefObjCommon.Rarity,_RefObjCommon.CanTrade,_RefObjCommon.CanSell,_RefObjCommon.CanBuy,_RefObjCommon.CanBorrow,_RefObjCommon.CanDrop,_RefObjCommon.CanPick,_RefObjCommon.CanRepair,_RefObjCommon.CanRevive,_RefObjCommon.CanUse,_RefObjCommon.CanThrow,_RefObjCommon.Price,_RefObjCommon.CostRepair,_RefObjCommon.CostRevive,_RefObjCommon.CostBorrow,_RefObjCommon.KeepingFee,_RefObjCommon.SellPrice,_RefObjCommon.ReqLevelType1,_RefObjCommon.ReqLevel1,_RefObjCommon.ReqLevelType2,_RefObjCommon.ReqLevel2,_RefObjCommon.ReqLevelType3,_RefObjCommon.ReqLevel3,_RefObjCommon.ReqLevelType4,_RefObjCommon.ReqLevel4,_RefObjCommon.MaxContain,_RefObjCommon.RegionID,_RefObjCommon.Dir,_RefObjCommon.OffsetX,_RefObjCommon.OffsetY,_RefObjCommon.OffsetZ,_RefObjCommon.Speed1,_RefObjCommon.Speed2,_RefObjCommon.Scale,_RefObjCommon.BCHeight,_RefObjCommon.BCRadius,_RefObjCommon.EventID,_RefObjCommon.AssocFileObj128,_RefObjCommon.AssocFileDrop128,_RefObjCommon.AssocFileIcon128,_RefObjCommon.AssocFile1_128,_RefObjCommon.AssocFile2_128,
	_RefObjChar.Lvl,_RefObjChar.CharGender,_RefObjChar.MaxHP,_RefObjChar.MaxMP,_RefObjChar.InventorySize,_RefObjChar.CanStore_TID1,_RefObjChar.CanStore_TID2,_RefObjChar.CanStore_TID3,_RefObjChar.CanStore_TID4,_RefObjChar.CanBeVehicle,_RefObjChar.CanControl,_RefObjChar.DamagePortion,_RefObjChar.MaxPassenger,_RefObjChar.AssocTactics,_RefObjChar.PD,_RefObjChar.MD,_RefObjChar.PAR,_RefObjChar.MAR,_RefObjChar.ER,_RefObjChar.BR,_RefObjChar.HR,_RefObjChar.CHR,_RefObjChar.ExpToGive,_RefObjChar.CreepType,_RefObjChar.Knockdown,_RefObjChar.KO_RecoverTime,_RefObjChar.DefaultSkill_1,_RefObjChar.DefaultSkill_2,_RefObjChar.DefaultSkill_3,_RefObjChar.DefaultSkill_4,_RefObjChar.DefaultSkill_5,_RefObjChar.DefaultSkill_6,_RefObjChar.DefaultSkill_7,_RefObjChar.DefaultSkill_8,_RefObjChar.DefaultSkill_9,_RefObjChar.DefaultSkill_10,_RefObjChar.TextureType,_RefObjChar.Except_1,_RefObjChar.Except_2,_RefObjChar.Except_3,_RefObjChar.Except_4,_RefObjChar.Except_5,_RefObjChar.Except_6,_RefObjChar.Except_7,_RefObjChar.Except_8,_RefObjChar.Except_9,_RefObjChar.Except_10
	FROM _RefObjCommon INNER JOIN _RefObjChar ON _RefObjCommon.Link=_RefObjChar.ID WHERE CodeName128 LIKE @CodeName AND _RefObjCommon.Service=1 ORDER BY _RefObjCommon.ID ASC
END

IF(@Type!=1 OR @Type!=2)
BEGIN
	PRINT 'The Type must be 1 OR 2!'
	PRINT 'Use @Type=1 for Items'
	PRINT 'Use @Type=2 for Characters'
END

/* Stored Procedure by sladlejrhfpq */

2/a If you want an Item's line, set the Type=1, and set the CodeName to the Item you want.
Example:

USE [SRO_VT_SHARD]
exec _GetMediaLines
@Type=1,
@CodeName='ITEM_CH_BOW_11_A_RARE'

2/b If you want a Character's or NPC's line, set the Type=2, and set the CodeName to the Char/NPC you want.
Example:

USE [SRO_VT_SHARD]
exec _GetMediaLines
@Type=2,
@CodeName='MOB_CH_MANGNYANG'

2/c If you want to get more lines, for example all Jupiter monster's code to the Characterdata, do it like this:

USE [SRO_VT_SHARD]
exec _GetMediaLines
@Type=2,
@CodeName='MOB_JUPITER%'

3. After done, just copy the lines, and add them to your Media.pk2

It was created by me, please do not re-release under your name.
Press Like if it was useful.

 

[hectormousa]

Re: [Release] SQL Script for Media.pk2

thanks bro

 

[VitorVRC]

Re: [Release] SQL Script for Media.pk2

very userful thanks man

 

[Statiic]

Re: [Release] SQL Script for Media.pk2

thx very userful query ^^

 

[sladlejrhfpq]

Re: [Release] SQL Script for Media.pk2

I'm glad to see that it is useful

 

[grayfox]

Re: [Release] SQL Script for Media.pk2

very userful one
ty

 

[enricoPo]

Re: [Release] SQL Script for Media.pk2

thanks : )

 

[kaperucito]

Re: [Release] SQL Script for Media.pk2

There is a problem with this query, it return decimal values as 0,0 instead of 0.0. The client read those with "." separator not ",", so if you copy it directly to your media your client will crash because it does not support ",".

 

[Caipi]

Re: [Release] SQL Script for Media.pk2

Use something like ..

UPDATE Tablename
SET Column1 = REPLACE(Column1,',','.')/*,
Column2 = REPLACE(Column2,',','.') */
WHERE CHARINDEX (',',Column1) > 0

OR

SELECT REPLACE(Column,',','.')

 

[whhacker93]

Re: [Release] SQL Script for Media.pk2

Thanks for this, going to be very useful!

 

[Kira]

Re: [Release] SQL Script for Media.pk2

I Liked your work ;D

 

[tamer153]

Re: [Release] SQL Script for Media.pk2

hey and skilldata_35000enc >?

 

[sladlejrhfpq]

Re: [Release] SQL Script for Media.pk2

hey and skilldata_35000enc >?

You can copy them from the _RefSkill table directly

 

[tamer153]

Re: [Release] SQL Script for Media.pk2

i'm getting crashes with itemdata_enc so i need to copy by query to uncrashes . . ..

 

[archer09]

Re: [Release] SQL Script for Media.pk2

when i try to get all of the records from db i'm getting client crash
its due to the numbers which have "," it have to be "." at media
its ok, i replaced them as "." but atm its crashing at stones what the hell

 

[lewy1000]

Work. THX:*

 

[katukaslt]

Sorry maybe it is a dumb question but where does it save the file? media.pk2 i mean.

 

[sladlejrhfpq]

Sorry maybe it is a dumb question but where does it save the file? media.pk2 i mean.

it does not create the Media.pk2, this procedure just helps you creating the lines for Itemdata and Characterdata text files. Since the data for them is collected from 2 tables, and not all columns, it will make your job easier.

 

[katukaslt]

Understood that will take me alot of time shiet..

 

[sladlejrhfpq]

well, it just selects the lines for you, and you should copy them to your text files. if you are planning to create a complete media.pk2, that will take some time, but this procedure just helps you in itemdata and characterdata, the other ones are kinda easy to do. (for example teleportbuilding.txt is the same as the lines in _RefObjCommon)