SQL Table Query Problem

[Deddalt]

resRoot = getResourceRootElement(getThisResource()) 
  
function createSQLTable( ) 
        executeSQLCreateTable( "table", "username BLOB, password BLOB, lastname TEXT, firstname TEXT, wallet REAL, bank REAL, faction BLOB, admin INTEGER, phours INTEGER, skin INTEGER, pos_x INTEGER, pos_y INTEGER, pos_z INTEGER"..string1..", "..string2..", "..string3 ) 
end 
  
function Connect( ) 
        username = { } 
        username[source] = { } 
        fadeCamera( source, true ) 
        username[source].log = 0 
        setCameraPosition( source, -2642.8926, 1927.8561, 224.3582 ) 
        setCameraLookAt( source, 1481.1476, -1750.8605, 15.4453 ) 
        setCameraMode( source, "fixed" ) 
        local name = getClientName( source ) 
        local firstname = gettok( name, 1, string.byte( "_" ) ) 
        local lastname = gettok( name, 2, string.byte( "_" ) ) 
        local check = executeSQLQuery( "SELECT lastname, firstname FROM table WHERE lastname = '"..lastname.."'" ) 
        if( not check == false ) then 
            triggerClientEvent( source, "2login", getRootElement() ) 
            username[source].lastname = lastname 
            username[source].firstname = firstname 
        else 
            username[source].firstname = firstname 
            username[source].lastname = lastname 
            triggerClientEvent( source, "2register", getRootElement(), firstname.."_"..lastname ) 
        end 
end 
  
addEventHandler( "onResourceStart", resRoot, createSQLTable ) 
  

It is returning the error:

ERROR: Database query failed: near "table": syntax error (SELECT lastname, firstname FROM table WHERE lastname == 'Tokudaiji' ) 

Edited September 16, 2008 by Guest

[50p]

Use only 1 =

So for example

"lastname = '" .. name .. "'" 

[Deddalt]

I updated the first post with the most current code I'm using, but it's still returning the same error.

[Ace_Gambit]

You are creating a table named table which is a reserved word in many SQL languages. Maybe that is why you get a syntax error. Why would you name a table table anyway? Try to give it a more descriptive name (e.g. player).

[Michael_Sund]

I don't get anythng about this SQL in MTA things

[churchill]

I don't get anythng about this SQL in MTA things

ignore the idea of SQL in MTA to begin with - read this:

http://www.w3schools.com/sql/default.asp (or start from http://www.w3schools.com/sql/sql_intro.asp)

read and understand about tables (The concept of SQL tables is a bit different to LUA tables, and the basic commands of SELECT, UPDATE, INSERT and DELETE, etc.

If you want to play with some example select statements, they provide you with this page to do a few basic SELECT statements of your own:

http://www.w3schools.com/sql/sql_tryit.asp

Just to make sure you understand, try their SQL test:

http://www.w3schools.com/quiztest/quizt ... ?qtest=SQL

If you get a good score on this, then you're ready to move to the next step.

This really is only a basic tutorial, there are more advanced ones out there, but this will help you understand the basics of what goes on under the hood when you use the SQL Lite commands in MTA.

Eventually when you understand the basic concepts, you can start learning about stuff like relational data, and learn how to link multiple tables together using JOINs, the concept of normalisation and relationships (one to many, many to many, one to one) etc. When you've got to that stage you'll probably outgrow MTA's SQLLite features and move onto using the MTA-MySQL module, but that's a lot further down the line

And you can always ask me, or anyone else on here with SQL background if you get stuck or don't understand something.

[Michael_Sund]

I don't get anythng about this SQL in MTA things

ignore the idea of SQL in MTA to begin with - read this:

http://www.w3schools.com/sql/default.asp (or start from http://www.w3schools.com/sql/sql_intro.asp)

read and understand about tables (The concept of SQL tables is a bit different to LUA tables, and the basic commands of SELECT, UPDATE, INSERT and DELETE, etc.

If you want to play with some example select statements, they provide you with this page to do a few basic SELECT statements of your own:

http://www.w3schools.com/sql/sql_tryit.asp

Just to make sure you understand, try their SQL test:

http://www.w3schools.com/quiztest/quizt ... ?qtest=SQL

If you get a good score on this, then you're ready to move to the next step.

This really is only a basic tutorial, there are more advanced ones out there, but this will help you understand the basics of what goes on under the hood when you use the SQL Lite commands in MTA.

Eventually when you understand the basic concepts, you can start learning about stuff like relational data, and learn how to link multiple tables together using JOINs, the concept of normalisation and relationships (one to many, many to many, one to one) etc. When you've got to that stage you'll probably outgrow MTA's SQLLite features and move onto using the MTA-MySQL module, but that's a lot further down the line

And you can always ask me, or anyone else on here with SQL background if you get stuck or don't understand something.

Something i managed to do in seconds only ^^

SELECT CompanyName, ContactName FROM customers 
ORDER BY ContactName ASC 

put this in the: http://www.w3schools.com/sql/sql_tryit.asp

[Deddalt]

Well I found out that my problem is that I have the tablecreate bug, now everything works since I'm using the SQLite Database Browser.

[Deddalt]

I take that back. Now I need to know how to check if it doesn't give me anything, because it apparently isn't returning false.

  
        local check = executeSQLQuery( "SELECT lastname, firstname FROM player WHERE lastname='"..lastname.."' AND firstname='"..firstname.."'" ) 
        if( check == false ) then 
            username[source].firstname = firstname 
            username[source].lastname = lastname 
            triggerClientEvent( source, "2register", getRootElement(), firstname.."_"..lastname ) 
        else 
            triggerClientEvent( source, "2login", getRootElement() ) 
            username[source].lastname = lastname 
            username[source].firstname = firstname 
        end 
end 

I need something to replace:

  
               if( check == false ) then 
  

[churchill]

Something i managed to do in seconds only ^^

SELECT CompanyName, ContactName FROM customers 
ORDER BY ContactName ASC 

put this in the: http://www.w3schools.com/sql/sql_tryit.asp

ok, so do you now understand about tables, rows and how to do basic sql statements like select [column1],[column2] from

where [someColumn] = [somevalue] etc, then it doesn't take much more to understand how the mta sql features work. Note that I'm talking about the built SQLLite db that comes with the server by default and can be accessed by some simplified MTA functions.

Note also that the server can only be accessed from the server scripts, not client side.

http://development.mtasa.com/index.php? ... _functions

All of the functions in here are quite well documented, in that they show you the SQL that they are supposed to execute when you follow the syntax of the functions.

So, using your example, if you had a table called customers in your MTA SQL Lite DB already, you could recreate:

"SELECT CompanyName, ContactName FROM customers ORDER BY ContactName ASC"

as

result = executeSQLSelect ( "customers", "CompanyName,ContactName")

which as SQL would become: "SELECT CompanyName, ContactName FROM Customers"

Note that executeSQLSelect doesn't allow you to do ORDERING from what I can see, but if you're only using SQL Lite for basic storage you might not need to worry about the order things come back in, as you'll more than likely only ever been selecting one row, e.g.

result = executeSQLSelect ( "Customers", "CompanyName,ContactName", "ContactName = '" .. somePlayersName .. "'")

which becomes: "SELECT CompanyName, ContactName FROM Customers WHERE ContactName = 'whatever the value of somePlayersName was'"

Makes sense? It's far easier to explain when you have an idea of WHAT you actually want to store, because then you can begin to understand how to CREATE tables (using executeSQLCreateTable), how to INSERT a row (executeSQLInsert) into those tables, how to UPDATE rows (executeSQLUpdate) on those tables and how DELETE rows (executeSQLDelete) from those tables.

Creating and dropping tables while developing your resource is far easier using the SQL Lite Browser which can be found here (http://sourceforge.net/projects/sqlitebrowser/), but if you plan to release it to the public afterwards, creating tables is usually done when the resource first starts (using executeSQLCreateTable), in order to create any tables that are needed by the resource when running on someone's server.

Dropping tables (executeSQLDropTable) is rarely used in the resource itself once it's released, unless it has an "uninstall" feature Mostly it's used for dropping your test tables while developing your resource.

Note also, that "executeSQLQuery" while more powerful has been known to be buggy as hell. At the point you know you need to use this function, you begin to realise that moving to the MySQL module is a good idea

[churchill]

I take that back. Now I need to know how to check if it doesn't give me anything, because it apparently isn't returning false.

  
        local check = executeSQLQuery( "SELECT lastname, firstname FROM player WHERE lastname='"..lastname.."' AND firstname='"..firstname.."'" ) 
        if( check == false ) then 
            username[source].firstname = firstname 
            username[source].lastname = lastname 
            triggerClientEvent( source, "2register", getRootElement(), firstname.."_"..lastname ) 
        else 
            triggerClientEvent( source, "2login", getRootElement() ) 
            username[source].lastname = lastname 
            username[source].firstname = firstname 
        end 
end 

I need something to replace:

  
               if( check == false ) then 
  

false only gets returned if the query couldn't execute for some reason. if it's returning a valid sql result but it's an EMPTY result then the result is still a table, just an empty one.

http://development.mtasa.com/index.php? ... teSQLQuery seems to have an example on checking if the result is empty or not, so that might help you.

In fact, re-reading that makes me wonder if something I tried previously could have been fixed using something I just read about on that page.

[Deddalt]

Well I know that, but I need an example as to how to check if it's empty... like...

  
local badtablevar = "{ 
  
    { firstname=' ', lastname=' ' }, 
    { username=' ', password=' ' }, 
  
}" 
  
if( check == badtablevar ) then 
        triggerClientEvent( source, "register", getRootElement(), firstname, lastname ) 
else 
        triggerClientEvent( source, "login", getRootElement(), firstname, lastname ) 
end 
  

Or something like that, I don't know what to do, so could someone please tell me how to check for an empty cell?

[churchill]

well as I said, in the example in the link i gave you, it shows this:

  local result = executeSQLQuery("SELECT money FROM players WHERE name=?", playerName) 
    if(#result == 0) then 
        outputConsole("No player named " .. playerName .. " is registered.", thePlayer) 
    else 
        outputConsole("Money amount of player " .. playerName .. " is " .. result[1].money, thePlayer) 
    end 

so when you've done your result, I'm assuming the "if (#result == 0) then" means if the result has no rows, then show the first message, else, we have a result with rows.

so, wrapped with the original code, it'd be more like:

  
 local result = executeSQLQuery("SELECT money FROM players WHERE name=?", playerName) 
if (result == false) then 
      outputDebugString("an error occured while retrieving data") 
else 
    if(#result == 0) then 
        outputDebugString("No player named " .. playerName .. " is registered.", thePlayer) 
    else 
        outputDebugString("Money amount of player " .. playerName .. " is " .. result[1].money, thePlayer) 
    end 
end 

you then need to get the value out of the result and put it in your local table/array.

[Ace_Gambit]

I've had many problems with the SQLite implementation in the past. Long story short, you can't rely on MTA SQLite for data storage because it is too buggy. But that is just my 2 cents.

[Deddalt]

/me is about to cry

So do I need to get a MySQL server or something for this?

[churchill]

/me is about to cry

So do I need to get a MySQL server or something for this?

I've found the ExecuteSQLQuery command to be buggy, which is what you're attempting to use. Usually I've seen that if you're sticking to the basic SELECT (executeSQLSelect), INSERT, and UPDATE functions then it seems to be stable, but then they really are basic, so anything like JOINs on multiple tables (or even ORDER BYs?) go out the window.

if you really need it, and know how to work with MySQL, then it's better in the long run, though if you're doing resources that you plan to release, it becomes a problem for the server owners who will then need mysql etc on their server.

[Deddalt]

I'm not planning on releasing the script to anyone, I just want to make this script work. It's unfortunate that the two things that I was hoping on using have to not work. setAccountData and SQL...

Is there a MySQL plugin for MTA, then? I suppose I'll have to stop using SQLite and learn how to use MySQL instead...

This is so annoying...

[churchill]

Did you try the method I proposed on the previous page, taken from the sample in the wiki, using "(if #result == 0) then" to check if the result is empty?

If you did try it but it didn't work, as you're only doing a basic select, did you try changing:

local check = executeSQLQuery( "SELECT lastname, firstname FROM player WHERE lastname='"..lastname.."' AND firstname='"..firstname.."'" ) 

to:

local check = executeSQLSelect("player" , "lastname,firstname", "lastname = '" .. lastname .. "' AND firstname = '" .. firstname .. "'") 

Which technically should create the same sql statement as above, but might be less buggy.

Also note that executeSQLSelect is different in that a "false" value means the table failed to execute OR THE TABLE IS EMPTY, as opposed to the executeSQLQuery method, which returns false if the query failed, or an empty table if the result is empty.

So, a simple "(if check == false) then" would mean the code only gets done if the table actually has a row. This is exactly what you're after.