AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   Solved [HELP] SQLite queries (https://forums.alliedmods.net/showthread.php?t=332277)

iceeedr 05-03-2021 00:24

[HELP] SQLite queries
 
Good morning, I have an unsolved problem when making a query with SQLite, in the DB Browser for SQLite program the query returns normally with all the information, but when trying to perform the query in the game I get the following error:

Code:

Error: near "(": syntax error
The querie

https://i.imgur.com/r0DQXWO.jpg

PHP Code:

SELECT ROW_NUMBER() OVER (ORDER BY 1.0 kills deaths DESCPoskillsdeathsauth FROM tablename WHERE enabled AND kills AND deaths AND kills 100

PHP Code:


#define SQL_QUERY_CATCH_UPDATED_RANK    "SELECT ROW_NUMBER() OVER (ORDER BY 1.0 * kills / deaths DESC) Pos, kills, deaths, auth FROM %s WHERE enabled = 1 AND kills > 0 AND deaths > 0 AND kills > 100;"

public UpdatedRank(id)
{
        new 
szQuery[180]
        
formatex
        
(
                
szQuery,
                
charsmax(szQuery),
                
SQL_QUERY_CATCH_UPDATED_RANK,
                
SQL_TABLE
        
)

        new 
szData[2]
        
szData[0] = QUERY_UPDATE_RANK
        szData
[1] = id

        SQL_ThreadQuery
(g_hTuple,"SQL_HandleGlobal",szQuery,szData,sizeof(szData))
        return 
PLUGIN_HANDLED
}

public 
SQL_HandleGlobal(iState,Handle:hResult,szError[],iError,szData[],iSize)
{
        if(
iState == TQUERY_SUCCESS)
        {
                switch(
szData[0])
                {
                        case 
QUERY_UPDATE_RANK:
                        {
                                if(
is_user_connected(szData[1]))
                                {
                                        if(
SQL_NumRows(hResult))
                                        {
                                                new 
szSteamid[MAX_AUTHID_LENGTH]
                                                new 
iPos
                                                
while(SQL_MoreResults(hResult))
                                                {
                                                        
iPos SQL_ReadResult(hResult0)
                                                        
SQL_ReadResult(hResult3szSteamidcharsmax(szSteamid))

                                                        if(
equal(szSteamiduserData[szData[1]][szAuth]))
                                                        {
                                                                
userData[szData[1]][Rank] = iPos
                                                        
}

                                                        
SQL_NextRow(hResult)
                                                }
                                        }
                                }
                        }
                }
        }
        else
        {
                
log_amx("Error: %s"szError)
        }



Shadows Adi 05-03-2021 04:17

Re: [HELP] SQLite queries
 
PHP Code:

SELECT ROW_NUMBER() OVER (ORDER BY 1.0 kills deaths DESC), Poskillsdeathsauth FROM tablename WHERE enabled AND kills AND deaths AND kills 100

You forgot a comma after
PHP Code:

deaths DESC


iceeedr 05-03-2021 09:07

Re: [HELP] SQLite queries
 
Quote:

Originally Posted by Shadows Adi (Post 2745761)
PHP Code:

SELECT ROW_NUMBER() OVER (ORDER BY 1.0 kills deaths DESC), Poskillsdeathsauth FROM tablename WHERE enabled AND kills AND deaths AND kills 100

You forgot a comma after
PHP Code:

deaths DESC


I believe that is not the problem, even because when adding the comma (which does not make sense in the syntax) the querie does not even return in the program.

Edit 1:

Error code is 1..

Code:

(1) SQLITE_ERROR

The SQLITE_ERROR result code is a generic error code that is used when no other more specific error code is available.


JocAnis 05-03-2021 14:11

Re: [HELP] SQLite queries
 
Did you try same query with MySql? How i remember some functions are not possible to query with sqlite, like Auto_increment, so maybe thats the issue

iceeedr 05-03-2021 14:51

Re: [HELP] SQLite queries
 
Quote:

Originally Posted by JocAnis (Post 2745801)
Did you try same query with MySql? How i remember some functions are not possible to query with sqlite, like Auto_increment, so maybe thats the issue

Yes, as you said Auto_increment is not possible in sqlite, however, AUTOINCREMENT yes, as I demonstrated in the sqlite db browser program the syntax is correct, I am thinking that the sqlite version of amxx may not be as current and does not support "ROW_NUMBER () OVER ()"

Which leads me to think about how to count the querie lines by returning them in a column, what a boring situation...

Bugsy 05-03-2021 21:59

Re: [HELP] SQLite queries
 
What are you trying to do? Maybe it can be written a different way

Edit: Try this, you will need to add the few other conditions that you have.

PHP Code:

SELECT 
  
SELECT COUNT(*) + 
    FROM tblData
    WHERE 
((Kills/Deaths) > (t.Kills/t.Deaths) OR ((Kills/Deaths) = (t.Kills/t.Deaths) AND id<t.id))
  ) as 
rowIndex
  
t.Kills/Deaths,
  
AuthID
FROM tblData t 
ORDER BY rowIndex ASC


Source [ID, kills, deatrhs, authID]
Code:

"1"        "20"        "9"        "STEAM_1"
"2"        "50"        "12"        "STEAM_2"
"3"        "90"        "33"        "STEAM_3"
"4"        "52"        "9"        "STEAM_4"
"5"        "31"        "55"        "STEAM_5"

Result [Rank, Kills/Deaths, AuthID]
Code:

"1"        "5"        "STEAM_4"
"2"        "4"        "STEAM_2"
"3"        "2"        "STEAM_1"
"4"        "2"        "STEAM_3"
"5"        "0"        "STEAM_5"


iceeedr 05-03-2021 23:26

Re: [HELP] SQLite queries
 
Quote:

Originally Posted by Bugsy (Post 2745844)
What are you trying to do? Maybe it can be written a different way

Edit: Try this, you will need to add the few other conditions that you have.

PHP Code:

SELECT 
  
SELECT COUNT(*) + 
    FROM tblData
    WHERE 
((Kills/Deaths) > (t.Kills/t.Deaths) OR ((Kills/Deaths) = (t.Kills/t.Deaths) AND id<t.id))
  ) as 
rowIndex
  
t.Kills/Deaths,
  
AuthID
FROM tblData t 
ORDER BY rowIndex ASC


Source [ID, kills, deatrhs, authID]
Code:

"1"        "20"        "9"        "STEAM_1"
"2"        "50"        "12"        "STEAM_2"
"3"        "90"        "33"        "STEAM_3"
"4"        "52"        "9"        "STEAM_4"
"5"        "31"        "55"        "STEAM_5"

Result [Rank, Kills/Deaths, AuthID]
Code:

"1"        "5"        "STEAM_4"
"2"        "4"        "STEAM_2"
"3"        "2"        "STEAM_1"
"4"        "2"        "STEAM_3"
"5"        "0"        "STEAM_5"


What I want is not difficult, I just want to take the line number in the querie to determine the "rank" of the player, the problem with your suggestion is that it is extremely expensive, while my querie performs in 80 ~ 90ms its delay 1980 ms on average.

Could you give me your syntax analysis below? It would have to change the way the querie is made, since the search would be straight from the steamid table, but it was supposed to work, what do you think?

PHP Code:

SELECT (COUNT(*) + 1) AS Rank FROM tablename WHERE ((1.0 kills) / deaths) > (SELECT ((1.0 kills) / deathsFROM tablename WHERE auth '%s' AND enabled AND kills AND deaths AND kills 100 AND kills deaths); 


Bugsy 05-03-2021 23:45

Re: [HELP] SQLite queries
 
Not sure I can help then.

Using the above, this will get you an individual players rank
PHP Code:

SELECT Rank
FROM 
(SELECT 
  
SELECT COUNT(*) + 
    FROM tblData
    WHERE 
((Kills/Deaths) > (t.Kills/t.Deaths) OR ((Kills/Deaths) = (t.Kills/t.Deaths) AND id<t.id))
  ) as 
Rank
  
t.Kills/Deaths,
  
AuthID
FROM tblData t 
ORDER BY Rank ASC
)
WHERE AuthID="STEAM_3"


iceeedr 05-04-2021 00:58

Re: [HELP] SQLite queries
 
Solved with some small changes, which in this case are the filters, in the query proposed by Bugsy, thanks for the time spent.


All times are GMT -4. The time now is 02:30.

Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.