[MySQL] Writing problem...

[Vivalavido]

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)

[Ace_Gambit]

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

[Vivalavido]

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; 
  

[Ace_Gambit]

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

  
`ID` INT(9) NOT NULL AUTO_INCREMENT 
  

[Vivalavido]

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.

[Ace_Gambit]

Which one of these two is line 31?

  
mysql_free_result(select_the_applications_table) -- Freeing the result is IMPORTANT 
mysql_free_result(insert_the_app) -- Freeing the result is IMPORTANT 
  

[Vivalavido]

Which one of these two is line 31?

  
  
mysql_free_result(insert_the_app) -- Freeing the result is IMPORTANT 
  

That one

[Ace_Gambit]

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) .. "' ) ") 
  

[Vivalavido]

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) .. "' ) ")

[Ace_Gambit]

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).

[Vivalavido]

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)

[Ace_Gambit]

The syntax is wrong.

  
INSERT INTO applications ( User, IGN) VALUES ( '" ..tostring(name) .. "', '" ..tostring(ign).. "')