Jump to content

[MySQL] Writing problem...


Vivalavido

Recommended Posts

Hello everyone!

Got a nice lil script together here.. With some errors with it where I cant seem to get ahold off..

  
local con_my_sql = mysql_connect("meh")  
  
function loadDatabaseAndMakeItWriteAble(text) 
  
        select_the_applications_database = mysql_select_db (con_my_sql, "DKRApplication") 
        select_the_applications_table = mysql_query(con_my_sql, "SELECT User, IGN, Email, Country, IP FROM applications ") 
     
        if ( not select_the_applications_database ) then 
            outputChatBox("The database is unreachable, im sorry.") 
        else 
            outputChatBox("I connected to your database!") 
            insert_the_app = mysql_query(con_my_sql, "INSERT INTO applications ( User) VALUES ( '" ..tostring(text) .. "' ) ") 
            mysql_close(con_my_sql) 
  
        if ( not insert_the_app ) then 
                    outputChatBox("I couldnt set the details..") 
             outputDebugString("Error executing the query: (" .. mysql_errno(con_my_sql) .. ") " .. mysql_error(con_my_sql)) 
        else 
            outputChatBox("Your SQL should be written!") 
        end 
    end 
        mysql_free_result(select_the_applications_table) -- Freeing the result is IMPORTANT 
        mysql_free_result(insert_the_app) -- Freeing the result is IMPORTANT 
    end 
     
end 
  

Thats the code, and I get these errors:

INFO: Error executing the query: (1062) Duplicate entry '' for key 1 <--- Coming from: outputDebugString("Error executing the query: (" .. mysql_errno(con_my_sql) .. ") " .. mysql_error(con_my_sql))

And:

ERROR: ...mods/deathmatch/resources/DKRJoinform/server/xml.lua:33: bad argument #1 to 'mysql_free_result' (LuaBook.mysqlResult expected, got nil)

Link to comment
The problem is probably related to your database table structure (unique indexes or keys). What does the "applications" table structure look like?

The table:

  
CREATE TABLE `applications` ( 
  `ID` varchar(10) NOT NULL, 
  `User` text NOT NULL, 
  `IGN` text NOT NULL, 
  `Email` text NOT NULL, 
  `Country` text NOT NULL, 
  `IP` text NOT NULL, 
  PRIMARY KEY  (`ID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
  

Link to comment
I'd recommend changing the ID field to type INT and auto-increment the primary key.

  
`ID` INT(9) NOT NULL AUTO_INCREMENT 
  

Well.. Im not using the ID table yet though.

SQL Now:

  
CREATE TABLE `applications` ( 
  `ID` int(10) NOT NULL auto_increment, 
  `User` text NOT NULL, 
  `IGN` text NOT NULL, 
  `Email` text NOT NULL, 
  `Country` text NOT NULL, 
  `IP` text NOT NULL, 
  PRIMARY KEY  (`ID`) 
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; 
  

EDIT: If I restart resource, and execute, it writes! BUT! When I do it instantly after it, I get the error under here:

ERROR: ...mods/deathmatch/resources/DKRJoinform/server/xml.lua:16: bad argument #1 to 'mysql_select_db' (Expected a valid MySQL link)

EDIT 2: Okay uhm. It works all times now. Code:

  
local con_my_sql = mysql_connect("meh")  
local select_the_applications_database = mysql_select_db (con_my_sql, "DKRApplication") 
local select_the_applications_table = mysql_query(con_my_sql, "SELECT User, IGN, Email, Country, IP FROM applications ") 
  
function loadDatabaseAndMakeItWriteAble(text, text2, text3, country, thePlayer) 
    if ( not select_the_applications_database ) then 
        outputChatBox("The database is unreachable, im sorry.") 
    else 
        outputChatBox("I connected to your database!") 
        local insert_the_app = mysql_query(con_my_sql, "INSERT INTO applications ( User) VALUES ( '" ..tostring(text) .. "' ) ") 
  
        if ( not insert_the_app ) then 
        outputChatBox("I couldnt set the details..") 
        outputDebugString("Error executing the query: (" .. mysql_errno(con_my_sql) .. ") " .. mysql_error(con_my_sql)) 
        else 
        outputChatBox("Your SQL should be written!") 
        end 
    end 
    mysql_free_result(select_the_applications_table) -- Freeing the result is IMPORTANT 
    mysql_free_result(insert_the_app) -- Freeing the result is IMPORTANT 
    mysql_close(con_my_sql)  
        end 
         
end 
  
  

Basicly, I added local infront off almost everything, the ERROR I get now:

ERROR: ...mods/deathmatch/resources/DKRJoinform/server/xml.lua:31: bad argument #1 to 'mysql_free_result' (LuaBook.mysqlResult expected, got nil)

Confused as shit atm.

Link to comment

I think it is a scope problem. Variable "insert_the_app" is declared as local inside your if/then/else clause. It is not known outside of that scope.

  
function loadDatabaseAndMakeItWriteAble(text, text2, text3, country, thePlayer) 
    local insert_the_app = false 
    if ( not select_the_applications_database ) then 
        outputChatBox("The database is unreachable, im sorry.") 
    else 
        outputChatBox("I connected to your database!") 
        insert_the_app = mysql_query(con_my_sql, "INSERT INTO applications ( User) VALUES ( '" ..tostring(text) .. "' ) ") 
  

Link to comment
I think it is a scope problem. Variable "insert_the_app" is declared as local inside your if/then/else clause. It is not known outside of that scope.
  
function loadDatabaseAndMakeItWriteAble(text, text2, text3, country, thePlayer) 
    local insert_the_app = false 
    if ( not select_the_applications_database ) then 
        outputChatBox("The database is unreachable, im sorry.") 
    else 
        outputChatBox("I connected to your database!") 
        insert_the_app = mysql_query(con_my_sql, "INSERT INTO applications ( User) VALUES ( '" ..tostring(text) .. "' ) ") 
  

It writes the first time --> Doesnt the second time. Error:

ERROR: ...mods/deathmatch/resources/DKRJoinform/server/xml.lua:23: bad argument #1 to 'mysql_query' (Expected a valid MySQL link)

Line 23: insert_the_app = mysql_query(con_my_sql, "INSERT INTO applications ( User) VALUES ( '" ..tostring(text) .. "' ) ")

Link to comment
Well obviously because you close the connection after the queries without re-establishing the connection. So you either have to figure out if you want to connect every routine or open the connection once in your script and use that connection handle (the method I would recommend).

Yea okay, it works perfeclty now.

Theres another problem. I was writing in 1 table, but im trying to write multiple this time. I keep getting errors:

Code:

        insert_the_app = mysql_query(con_my_sql, "INSERT INTO applications ( User, IGN) VALUES ( '" ..tostring(name) .. "' ), ( '" ..tostring(ign).. "')") 

Error:

INFO: Error executing the query: (1136) Column count doesn't match value count at row 1

ERROR: ...mods/deathmatch/resources/DKRJoinform/server/xml.lua:34: bad argument #1 to 'mysql_free_result' (LuaBook.mysqlResult expected, got nil)

Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...