[SQL] Looking for fixed code

[Xijezu]

null.

Want to do that, too.

[Raskim2000]

Originally Posted by Xijezu

Heya,

Some confusing title, but I will explain it for ya.
I'm working on a GM/Admin-Panel in php, for example: Insert Items or Buffs.

Thats because most times I'm not able to connect ingame, because I'm not at home.

I'm going to release the finished script for sure.

All was working fine expect inserting skillcards.

When I'm going to insert a skillcard via SQL/PHP, I will get it Ingame.
But if I relogg, either the skillcard disappears or it is +1 insteat of +10.

Raskim? ismoke? someone of NCarbon? someone who knows about SQL?
Try to find the issue.


First try:

 Spoiler

$item = Skillcard
$index = Place in bag
$value = Enchant
$msid = max(sid) +1 (the lastest inserted ID +1)
INSERT INTO [Telecaster].[dbo].[Item] ([sid],[account_id], [owner_id], [summon_id], [auction_id], [keeping_id], [idx], [code *changed caused for problems with bbcode], [flag], [cnt], [level], [enhance], [endurance], [gcode], [create_time], [wear_info], [socket_0], [socket_1], [socket_2], [socket_3], [remain_time], [update_time], [elemental_effect_type], [elemental_effect_expire_time], [elemental_effect_attack_point], [elemental_effect_magic_point]) VALUES ('$msid', '0', '1514', '0', '0', '0', '$index', '$item', '2', '1', '1', '$value', '0', '6', '2011-10-26 08:51:00', '-1', '0', '0', '0', '0', '0', '2011-10-26 08:51:19', '0', '2011-10-01 08:51:26', '0', '0')

Second try (just a general insert_item-procedure)

 Spoiler

DECLARE
@IN_SID								BIGINT,
@IN_OWNER_ID						INT,
@IN_ACCOUNT_ID						INT,
@IN_SUMMON_ID						INT,
@IN_AUCTION_ID						INT,
@IN_KEEPING_ID						INT,
@IN_CODE							INT,
@IN_IDX								INT,
@IN_FLAG							INT,
@IN_CNT								BIGINT,
@IN_LEVEL							INT,
@IN_ENHANCE							INT,
@IN_ENDURANCE						INT,
@IN_GCODE							INT,
@IN_REMAIN_TIME						INT,
@IN_ELEMENTAL_EFFECT_TYPE			TINYINT,
@IN_ELEMENTAL_EFFECT_EXPIRE_TIME	DATETIME,
@IN_ELEMENTAL_EFFECT_ATTACK_POINT	INT,
@IN_ELEMENTAL_EFFECT_MAGIC_POINT	INT

SET @IN_SID = $msidb // lastest item-number +1
SET @IN_OWNER_ID = $sid // Item-ID 
SET @IN_ACCOUNT_ID = 0
SET @IN_SUMMON_ID = 0
SET @IN_AUCTION_ID = 0
SET @IN_KEEPING_ID = 0
SET @IN_CODE = $code // Item-ID
SET @IN_IDX = $idx // Place in Bag
SET @IN_FLAG = $flag // Flag, usually 0 or 2
SET @IN_CNT = 1
SET @IN_LEVEL = $level // Level 
SET @IN_ENHANCE = $enh // Enchance
SET @IN_ENDURANCE = 0
SET @IN_GCODE = $gcode // Gamecode, usually 10 by skillcards?
SET @IN_REMAIN_TIME = 0
SET @IN_ELEMENTAL_EFFECT_TYPE = 0
SET @IN_ELEMENTAL_EFFECT_EXPIRE_TIME = 0
SET @IN_ELEMENTAL_EFFECT_ATTACK_POINT = 0
SET @IN_ELEMENTAL_EFFECT_MAGIC_POINT = 0
EXEC smp_insert_item @IN_SID, @IN_OWNER_ID, @IN_ACCOUNT_ID, @IN_SUMMON_ID, @IN_AUCTION_ID, @IN_KEEPING_ID, @IN_CODE, @IN_IDX, @IN_FLAG, @IN_CNT, @IN_LEVEL, @IN_ENHANCE,@IN_ENDURANCE, @IN_GCODE, @IN_REMAIN_TIME, @IN_ELEMENTAL_EFFECT_TYPE, @IN_ELEMENTAL_EFFECT_EXPIRE_TIME, @IN_ELEMENTAL_EFFECT_ATTACK_POINT,@IN_ELEMENTAL_EFFECT_MAGIC_POINT

Thanks for reply.

Why don`t you try it on an other way. You try to insert them directly, this seem`s not to work. Try it with the ShopBox (dbo.paiditem)

 Spoiler

INSERT INTO [telecaster].[dbo].[PaidItem]([account_id],[avatar_id],[avatar_name],[item_code],[item_count],[rest_item_count],[bought_time],[valid_time],[server_name],[taken_avatar_id],[taken_avatar_name],[taken_server_name],[taken_time],[taken_account_id],[confirmed],[confirmed_time]) VALUES (2,NULL,NULL,305721,1,1,'2011-10-01 12:00:00.000',NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL)

Edit: Don`t forget, that the Launcher need the row "/cash".

My Launcher use this line. (It`s for official, so don`t waste your time trying to "Hack" it ;p)

 Spoiler

/auth_ip:195.59.138.22 /locale:windows-1252 /country:DE /cash /commercial_shop /help_url_w:620 /help_url_h:633

[Xijezu]

Well, sure, that would be an alternative way, but I'm confused about the columns, dont know what all of this does.

Magst mal skype on kommen und mich über die spalten aufklären? xD

[lawlywood]

Danke Raskim Ich bau das Query noch n bissl schöner und dann poste ichs hier

[RoflcopterGoesSoiSoiSoi]

you never fill out sid, it's auto-increment

[lawlywood]

I got this working now
It won't set a time limit cause i don't need it. Feel free to add it, if you want.
Those items will appear in the item box. Look for the /cash flag in your launcher. Thank you Raskim for posting those infos about the paidItem thing

Users won't recieve a message in the chat, that they have recieved a new item!

Use Telecaster
DECLARE @id int; --accountid
DECLARE @itemcount int; --Anzahl der Items
DECLARE @item int;
DECLARE @avatarid int;

-----------Insert Settings here-----------------
SET @item = '' --INSERT ITEM ID (e.g. 2010764 R6 Cloak)
SET @itemcount = '1' --INSERT THE NUMBER OF ITEMS
SELECT @avatarid = sid FROM Character WHERE name = 'CHARNAME' --INSERT CHARACTER NAME
------------------------------------------------

SELECT @id = account_id FROM Character WHERE sid = @avatarid

--Warning
IF @id IS NULL
BEGIN
PRINT 'WARNING: This Character does not exist. No Rows affected!'
RETURN
END

--DB Update
INSERT INTO [telecaster].[dbo].[PaidItem]([account_id],[avatar_id],[avatar_name],[item_code],[item_count],[rest_item_count],[bought_time],[valid_time],[server_name],[taken_avatar_id],[taken_avatar_name],[taken_server_name],[taken_time],[taken_account_id],[confirmed],[confirmed_time]) VALUES (@id,@avatarid,NULL,@item,@itemcount,@itemcount,'2011-10-01 12:00:00.000',NULL,NULL,NULL,NULL,NULL,NULL,@id,NULL,NULL)

[Raskim2000]

Originally Posted by lawlywood

I got this working now
It won't set a time limit cause i don't need it. Feel free to add it, if you want.
Those items will appear in the item box. Look for the /cash flag in your launcher. Thank you Raskim for posting those infos about the paidItem thing

Users won't recieve a message in the chat, that they have recieved a new item!

Use Telecaster
DECLARE @id int; --accountid
DECLARE @itemcount int; --Anzahl der Items
DECLARE @item int;
DECLARE @avatarid int;

-----------Insert Settings here-----------------
SET @item = '' --INSERT ITEM ID (e.g. 2010764 R6 Cloak)
SET @itemcount = '1' --INSERT THE NUMBER OF ITEMS
SELECT @avatarid = sid FROM Character WHERE name = '' --INSERT CHARACTER NAME
------------------------------------------------

SELECT @id = account_id FROM Character WHERE sid = @avatarid

--Warning
IF @id IS NULL
BEGIN
PRINT 'WARNING: This Character does not exist. No Rows affected!'
RETURN
END

--DB Update
INSERT INTO [telecaster].[dbo].[PaidItem]([account_id],[avatar_id],[avatar_name],[item_code],[item_count],[rest_item_count],[bought_time],[valid_time],[server_name],[taken_avatar_id],[taken_avatar_name],[taken_server_name],[taken_time],[taken_account_id],[confirmed],[confirmed_time]) VALUES (@id,@avatarid,NULL,@item,@itemcount,@itemcount,'2011-10-01 12:00:00.000',NULL,NULL,NULL,NULL,NULL,NULL,@id,NULL,NULL)

Ist doch im Prinzip nichts anderes wie mein Script?

Na gut, du hast noch die klausel reingehauen, das er dir anzeigt das die falsche id verwendet wurde.

[lawlywood]

Nein ich habs einfacher gemacht, dass du nicht für jeden insert:
1. die character nummer suchen musst
2. die account nummer suchen musst
3. die blöcke abzählen musst
und 4. müll in der db drinnen steht

Also ich finds so deutlich komfortabler event items zu verteilen...

[Raskim2000]

Pardon, ich bin etwas verwirrt gewesen, aber ich merk gerade das du die items direkt ins inventar legen willst mit dem script, aber da fehlen doch noch die taken_time usw.

Edit: Vergiss was ich gesagt hab, nicht mein Tag heute

[lawlywood]

lolz. ne ich will ja, dass man sich den char aussuchen kann, da manche items char-bound sind.

[Xijezu]

Da ihr ja bestimmt gesehen habt, und ich mich just 4 fun an den Cashshop gesetzt habe, finde ich auch, das die normale Procedure um längen einfacher ist.
Man hat die ganzen Werte, sprich acc. id etc. in Variabeln gespeichert, und brauch dann nichts mehr machen, ...
Ich verzichte eigentlich so schon grundsätzlich auf die INSERT ITEM-Variante, ich mag die nicht. :/ xD

[lawlywood]

nuja ich nutz immer ingame insert_item aber verschiedene items (donation wings oder event zeug) landet immer im eigenen inventar. dafür ist die box ganz nice