Item Name Insert

[Sandro1029]

Hello there. I want to ask some ppl here which has more knowledge than me and as well share my idea&knowledge to another ppls. So. Some days ago I've got an idea. You know that there are some GMs which can't even install SQL, nor get the correct item by the name from database. So I thought like "Why not?"

The main idea is: Create a lua function which gonna insert you an item equals to your request. For example. I wanna get Abhuva boss card. I will just type in chat next text:

/run correct_item("abh")
OR

/run correct_item("Dragon") to get White Dragon card.

So, how it works:
First of all, there is about running SQL queries from LUA.

We gonna use this as a base.
Mine smps got updated with next stuff:

IF @IN_KEY LIKE '%_correct_item'
BEGIN
	EXEC dbo.[smp_correct_item] @IN_VALUE
END

So after inserting new global variable ending on _correct_item, it gonna execute my own procedure named smp_correct_item

Obiviously, you should create a new procedure (No matter where, but I made it in Telecaster). Here it is:

ALTER PROCEDURE [dbo].[smp_correct_item]

   @ QUERY_TEXT NVARCHAR(1024)

AS
SET NOCOUNT ON

DECLARE  @ QUERY_TEXT NVARCHAR(1024)
SET   @ QUERY_TEXT = ( SELECT TOP 1 [id] FROM Arcadia.dbo.view_item WHERE [EN Name] LIKE CONCAT('%'   @ EXECUTE_TEXT,'%' ) )



IF   @ QUERY_TEXT IS NULL OR @ QUERY_TEXT = 0
BEGIN
UPDATE Telecaster.dbo.Character SET [flag_list] = CONCAT( ( SELECT [flag_list] FROM Telecaster.dbo.Character ),'\r\ncorrect_item:'   @ QUERY_TEXT,'\r\n') WHERE [name] = ( SELECT [value] FROM Telecaster.dbo.GlobalVariable WHERE [name] = 'item_correct_char' )
END
IF @ QUERY_TEXT IS NOT NULL 
BEGIN
UPDATE Telecaster.dbo.Character SET [flag_list] = CONCAT( ( SELECT [flag_list] FROM Telecaster.dbo.Character ),'\r\ncorrect_item:'  @ QUERY_TEXT,'\r\n') WHERE [name] = ( SELECT [value] FROM Telecaster.dbo.GlobalVariable WHERE [name] = 'item_correct_char' )
END

I think you already read my mind. You should create next view in Arcadia to get the id/name list for the procedures. Like this one:

SELECT id,
enname.value as "EN Name"
FROM dbo.ItemResource itemres LEFT JOIN dbo.StringResource_EN enname ON itemres.id = enname.code
ORDER BY [id] ASC
OFFSET 0 ROWS;

So, in the end, here is the lua script file:

function correct_item(name)
	del_flag("correct_item")
	save()
	local query = tostring(name)
	set_global_variable("test_item_correct_item",query)
	set_global_variable("item_correct_char",gv("name"))
	sleep()
	correct_insert_item()
	save()
end

function sleep()
  local ntime = ( get_os_time() + 0.0001 )
  
  while get_os_time() < ntime do
  end
  
end

function correct_insert_item()
save()
local flag_items = get_flag("correct_item")
	
	if flag_items == "EmptyQuery" then
		private_notice("The ID is empty")
	else
		insert_item(flag_items,1)
		private_notice("You got "..tostring(get_item_name(get_item_handle(flag_items))).." ")
	end
end

Everything should work perfect. But there are some problems:
1) I have no idea how to force the game read/write/remove flags correct way. It always requires logout, which completely isn't good.
2) I have no idea how to add new lines transfer in the table. I'll be very grateful to the guy which can show me how2do that. I'm getting flaglist like:

 correct_item:101302
\r\ncorrect_item:112501\r\ncorrect_item:250021\r\n

Instead of proper one, lol

I hope it gonna be useful for someone. As well I hope someone can help me to improve that.

[Sandro1029]

Edited a bit. For some reasons it mentions a guy which has QueRy nickname while I'm trying to write @ QUERY without spacebar between @ and Q