MYSQL

[TheNightRider]

Hey guys ive set up a mysql script and a mysql server. I have setup the rights correctly but for some reason the mysql script cannot login into the mysql server.

Please note:-

Account on the mysql server was set up

host, port, password, username etc was set up correctly in the mysql script.

mysql modules etc was added into the modules folder and in the serverconf.

I am lost for words on this 1 lol. It just won't login

[BinSlayer1]

maybe the script is fooked

post it in here so people can take a look

[TheNightRider]

Nothing wrong with the script itself it worked in the past and hasn't been altered whatsoever.

[BinSlayer1]

So you're using the old modules.. Not sure, but they might be outdated or something. However, MTA now has built-in support for mysql (no need for modules anymore)

Download latest stable 1.1.1 build: https://nightly.multitheftauto.com/mtasa-1.1.1-rc ... 111213.exe

Rewrite your script to make use of the new db-functions

https://wiki.multitheftauto.com/wiki/DbConnect

Click this link and at the bottom of it you'll see some more db functions like dbQuery and all that

[TheNightRider]

OK cheers mate

[TheNightRider]

  
server = dbConnect( "sqlite", "Storedinfo.db" ) 
  
     
  
  
  
addEventHandler( "onResourceStop", resourceRoot, 
    function( ) 
        for key, value in pairs( results ) do 
            db_free_result( value.r ) 
            outputDebugString( "Query not free()'d: " .. value.q, 2 ) 
        end 
         
         
    end 
) 
  
  
  
  
  
local function query( str, ... ) 
     
     
    if ( ... ) then 
        local t = { ... } 
        for k, v in ipairs( t ) do 
            t[ k ] = escape_string( tostring( v ) ) or "" 
        end 
        str = str:format( unpack( t ) ) 
    end 
     
    local result = db_query( connection, str ) 
    if result then 
        for num = 1, max_results do 
            if not results[ num ] then 
                results[ num ] = { r = result, q = str } 
                return num 
            end 
        end 
        db_free_result( result ) 
        return false, "Unable to allocate result in pool" 
    end 
    return false, db_error( connection ) 
end 
  
function query_free( str, ... ) 
    if sourceResource == getResourceFromName( "runcode" ) then 
        return false 
    end 
     
     
    if ( ... ) then 
        local t = { ... } 
        for k, v in ipairs( t ) do 
            t[ k ] = escape_string( tostring( v ) ) or "" 
        end 
        str = str:format( unpack( t ) ) 
    end 
     
    local result = db_query( connection, str ) 
    if result then 
        db_free_result( result ) 
        return true 
    end 
    return false, db_error( connection ) 
end 
  
function free_result( result ) 
    if results[ result ] then 
        db_free_result( results[ result ].r ) 
        results[ result ] = nil 
    end 
end 
  
function query_assoc( str, ... ) 
    if sourceResource == getResourceFromName( "runcode" ) then 
        return false 
    end 
     
    local t = { } 
    local result, error = query( str, ... ) 
    if result then 
        for result, row in mysql_rows_assoc( results[ result ].r ) do 
            local num = #t + 1 
            t[ num ] = { } 
            for key, value in pairs( row ) do 
                if value ~= null then 
                    t[ num ][ key ] = tonumber( value ) or value 
                end 
            end 
        end 
        free_result( result ) 
        return t 
    end 
    return false, error 
end 
  
function query_assoc_single( str, ... ) 
    if sourceResource == getResourceFromName( "runcode" ) then 
        return false 
    end 
     
    local t = { } 
    local result, error = query( str, ... ) 
    if result then 
        local row = db_fetch_assoc( results[ result ].r ) 
        if row then 
            for key, value in pairs( row ) do 
                if value ~= null then 
                    t[ key ] = tonumber( value ) or value 
                end 
            end 
            free_result( result ) 
            return t 
        end 
        free_result( result ) 
        return false 
    end 
    return false, error 
end 
  
function query_insertid( str, ... ) 
    if sourceResource == getResourceFromName( "runcode" ) then 
        return false 
    end 
     
    local result, error = query( str, ... ) 
    if result then 
        local id = db_insert_id( connection ) 
        free_result( result ) 
        return id 
    end 
    return false, error 
end 
  
function query_affected_rows( str, ... ) 
    if sourceResource == getResourceFromName( "runcode" ) then 
        return false 
    end 
     
    local result, error = query( str, ... ) 
    if result then 
        local rows = db_affected_rows( connection ) 
        free_result( result ) 
        return rows 
    end 
    return false, error 
end 
  

I converted much as the code as I could from MYSQL to db but it won't work:(

I have added the database file to the resource and it starts up but script doesn't seem to be able to find it.

Edited December 16, 2011 by Guest

[AGENT_STEELMEAT]

Use

tags in your posts. Also, make sure you read about the knew DB functions to understand how they work. Report back with any errors you get.

[TheNightRider]

Thanks mate ive added the tags as requested and am having bit of trouble learning the db functions.

Not sure if am getting all the information I require or not

[Castillo]

What is this:

db_query 
db_free_result 
db_fetch_assoc 

? these functions doesn't exists.

You're messing DB functions with Ryden's MySQL module.

[TheNightRider]

Thanks for your help I forgot to remove that lot lol. Am not all that good with scripting on this level

I am a bit lost on the part of the code which does the dbconnect. It returns a nil value

  
addEventHandler( "onResourceStop", resourceRoot, 
    function( ) 
        local server = dbConnect( "sqlite", "C:\Program Files\MTA San Andreas 1.1\server\mods\deathmatch\internal.db" ) 
        for key, value in pairs( results ) do 
            dbQuery( value.r ) 
            outputDebugString( "Query not free()'d: " .. value.q, 2 ) 
        end 
         
         
    end 
) 
  

[JR10]

This is wrong, the file path can be the global folder for databases or the resource or any other resource.

If I'm right you can't connect to internal.db or registry.db.

Make a new .db.

Also where did 'results' variable come from, it's not defined in your code.

And you must specify the connection in dbQuery.

addEventHandler( "onResourceStop", resourceRoot, 
    function( ) 
        local server = dbConnect( "sqlite", ":/myDB.db" ) 
        for key, value in pairs( results ) do 
            dbFree ( dbQuery( server , value.r ) ) 
            outputDebugString( "Query not free()'d: " .. value.q, 2 ) 
        end 
        
        
    end 
) 

It's also recommended that you connect to the database on start.

Not connect each time.

[TheNightRider]

Thanks your right ive missed that part out stupid me anyways I have corrected it but still the dbconnect line still returns a nil value.

  
  
addEventHandler( "onResourceStart", resourceRoot, 
    function( ) 
        local server = dbConnect( "sqlite", "Storedinfo.db" ) 
        local results = dbPoll( qh, 10 )  
        for key, value in pairs( results ) do 
            dbQuery( value.r ) 
            outputDebugString( "Query not free()'d: " .. value.q, 2 ) 
        end 
         
         
    end 
) 
  
  

[AGENT_STEELMEAT]

Your not using the DB functions properly. 'qh' is not defined, and dbQuery is not used properly.

[TheNightRider]

Okay could you give me an example please??

I am a total noob when it comes to database scripting

[Castillo]

addEventHandler( "onResourceStart", resourceRoot, 
    function( ) 
        local server = dbConnect( "sqlite", "Storedinfo.db" ) 
        local qh = dbQuery(server, "SELECT * FROM tableName") 
        local results = dbPoll( qh, -1 ) 
        for key, value in pairs( results ) do 
            --do something 
        end 
    end 
) 

[TheNightRider]

thanks mate

[Castillo]

You're welcome.

[TheNightRider]

JR10 if couldn't use the db files you say then how scripts such as certain fuel scripts, bank, house systems, business system etc. store their information in there?

[Castillo]

You can't open the default MTA SQLITE databases.

[JR10]

JR10 if couldn't use the db files you say then how scripts such as certain fuel scripts, bank, house systems, business system etc. store their information in there?

They use the old sqlite functions.

executeSQLCreateTable 
executeSQLInsert 
executeSQLUpdate 
executeSQLQuery 

[TheNightRider]

Thanks mate but how do i create the table?

[JR10]

You have to connect to a database, and execute a query.

local connection = dbConnect("sqlite","newDB.db") 
dbFree ( dbQuery ( connection,"CREATE TABLE IF NOT EXISTS newTable (column1 TEXT,column2 TEXT)")) 

This will create a table called 'newTable'

If you're unfamiliar with that, check the old SQLite functions wiki page.

executeSQLCreateTable 
executeSQLInsert 
executeSQLUpdate 
executeSQLQuery 

[TheNightRider]

ok no worries that explains it can they still be used or is their an alternative?

[TheNightRider]

So could I do it something like this??

  
  
 function( ) 
        local server = dbConnect( "sqlite", "Storedinfo.db" ) 
        local qh = dbQuery(server, "SELECT * FROM vehicles") 
        local results = dbPoll( qh, -1 ) 
        for key, value in pairs( results ) do 
        if server=false then 
           dbFree ( dbQuery ( connection,"CREATE TABLE IF NOT EXISTS newTable (  
                { name = 'vehicleID', type = 'int(10) unsigned', auto_increment = true, primary_key = true }, 
                { name = 'model', type = 'int(10) unsigned' }, 
                { name = 'posX', type = 'float' }, 
                { name = 'posY', type = 'float' }, 
                { name = 'posZ', type = 'float' }, 
                { name = 'rotX', type = 'float' }, 
                { name = 'rotY', type = 'float' }, 
                { name = 'rotZ', type = 'float' }, 
                { name = 'interior', type = 'tinyint(3) unsigned', default = 0 }, 
                { name = 'dimension', type = 'int(10) unsigned', default = 0 }, 
                { name = 'respawnPosX', type = 'float' }, 
                { name = 'respawnPosY', type = 'float' }, 
                { name = 'respawnPosZ', type = 'float' }, 
                { name = 'respawnRotX', type = 'float' }, 
                { name = 'respawnRotY', type = 'float' }, 
                { name = 'respawnRotZ', type = 'float' }, 
                { name = 'respawnInterior', type = 'int(10) unsigned', default = 0 }, 
                { name = 'respawnDimension', type = 'int(10) unsigned', default = 0 }, 
                { name = 'numberplate', type = 'varchar(8)' }, 
                { name = 'health', type = 'int(10) unsigned', default = 1000 }, 
                { name = 'color1', type = 'tinyint(3) unsigned', default = 0 }, 
                { name = 'color2', type = 'tinyint(3) unsigned', default = 0 }, 
                { name = 'characterID', type = 'int(11)', default = 0 }, 
                { name = 'locked', type = 'tinyint(3) unsigned', default = 0 }, 
                { name = 'engineState', type = 'tinyint(3) unsigned', default = 0 }, 
                { name = 'lightsState', type = 'tinyint(3) unsigned', default = 0 }, 
                { name = 'tintedWindows', type = 'tinyint(3) unsigned', default = 0 }, 
                { name = 'fuel', type = 'float unsigned', default = 100 }, 
            } )) then cancelEvent( ) return end 
  
  
  
  
  
 

[Castillo]

That's just a mess.

Suggestion: Don't use MTA paradise .