Problem with SQL

BlueBerry · February 25, 2012

CREATE TABLE IF NOT EXISTS `accounts` ( 
  `id` int(100) NOT NULL AUTO_INCREMENT, 
  `username` varchar(80) NOT NULL, 
  `password` varchar(180) NOT NULL, 
  `serial` varchar(200) NOT NULL, 
  `rank` int(2) NOT NULL, 
  `adminduty` tinyint(1) NOT NULL DEFAULT '0', 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

SQLlite says this #1046 - No database selected

drk · February 25, 2012

Is this your whole code?

BlueBerry · February 25, 2012

This is the part that gave an error I can send the whole one if you need it

drk · February 25, 2012

You are using this code in ... ? executeSQLQuery? dbQuery? As far as I know to create tables for SQLite you have to use dbQuery or executeSQLQuery and if it's, your code is wrong

BlueBerry · February 25, 2012

-- phpMyAdmin SQL Dump 
-- version 3.4.5 
-- [url=http://www.phpmyadmin.net]http://www.phpmyadmin.net[/url] 
-- 
-- Machine: localhost 
-- Genereertijd: 11 feb 2012 om 13:12 
-- Serverversie: 5.5.16 
-- PHP-Versie: 5.3.8 
  
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; 
SET time_zone = "+00:00"; 
  
  
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 
/*!40101 SET NAMES utf8 */; 
  
-- 
-- Database: `rpvx` 
-- 
  
-- -------------------------------------------------------- 
  
-- 
-- Tabelstructuur voor tabel `accounts` 
-- 
  
CREATE TABLE IF NOT EXISTS `accounts` ( 
  `id` int(100) NOT NULL AUTO_INCREMENT, 
  `username` varchar(80) NOT NULL, 
  `password` varchar(180) NOT NULL, 
  `serial` varchar(200) NOT NULL, 
  `rank` int(2) NOT NULL, 
  `adminduty` tinyint(1) NOT NULL DEFAULT '0', 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 
  
  
  
-- -------------------------------------------------------- 
  
-- 
-- Tabelstructuur voor tabel `characters` 
-- 
  
CREATE TABLE IF NOT EXISTS `characters` ( 
  `charid` int(20) NOT NULL AUTO_INCREMENT, 
  `charactername` varchar(60) NOT NULL, 
  `accountid` int(20) NOT NULL, 
  `charskin` int(3) NOT NULL, 
  `age` int(5) NOT NULL, 
  `intelligence` int(5) NOT NULL, 
  `faction_id` int(3) NOT NULL DEFAULT '1', 
  `faction_name` varchar(70) NOT NULL DEFAULT 'San Andreas Government', 
  `faction_rank` int(5) NOT NULL DEFAULT '1', 
  `hospitalized` int(30) NOT NULL DEFAULT '0', 
  `driverslicense` int(1) NOT NULL DEFAULT '0', 
  `drivingexperience` int(30) NOT NULL, 
  `charlevel` int(3) NOT NULL, 
  `charexp` int(20) NOT NULL, 
  `perk1` varchar(20) NOT NULL, 
  `perk2` varchar(20) NOT NULL, 
  `cash` int(50) NOT NULL, 
  `savedX` varchar(100) NOT NULL, 
  `savedY` varchar(100) NOT NULL, 
  `savedZ` varchar(100) NOT NULL, 
  `health` int(3) NOT NULL DEFAULT '100', 
  `armor` int(3) NOT NULL DEFAULT '0', 
  `interior` int(5) NOT NULL DEFAULT '0', 
  `dimension` int(10) NOT NULL DEFAULT '0', 
  PRIMARY KEY (`charid`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1; 
  
-- -------------------------------------------------------- 
  
-- 
-- Tabelstructuur voor tabel `interiors` 
-- 
  
CREATE TABLE IF NOT EXISTS `interiors` ( 
  `id` int(100) NOT NULL AUTO_INCREMENT, 
  `name` varchar(60) NOT NULL, 
  `type` int(1) NOT NULL, 
  `interior` int(5) NOT NULL, 
  `dimension` int(50) NOT NULL, 
  `owner` varchar(120) NOT NULL, 
  `locked` int(1) NOT NULL, 
  `x` float NOT NULL, 
  `y` float NOT NULL, 
  `z` float NOT NULL, 
  `intx` float NOT NULL, 
  `inty` float NOT NULL, 
  `intz` float NOT NULL, 
  `cost` int(100) NOT NULL, 
  `rented` int(1) NOT NULL, 
  `renter` int(40) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 
  
  
-- -------------------------------------------------------- 
  
-- 
-- Tabelstructuur voor tabel `items` 
-- 
  
CREATE TABLE IF NOT EXISTS `items` ( 
  `id` int(255) NOT NULL AUTO_INCREMENT, 
  `itemid` int(100) NOT NULL, 
  `itemname` varchar(100) NOT NULL, 
  `owner` int(10) NOT NULL, 
  `itemvalue` int(10) NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 
  
  
-- -------------------------------------------------------- 
  
-- 
-- Tabelstructuur voor tabel `vehicles` 
-- 
  
CREATE TABLE IF NOT EXISTS `vehicles` ( 
  `vehicleID` int(100) NOT NULL AUTO_INCREMENT, 
  `model` int(5) NOT NULL, 
  `posX` float NOT NULL, 
  `posY` float NOT NULL, 
  `posZ` float NOT NULL, 
  `rotX` float NOT NULL, 
  `rotY` float NOT NULL, 
  `rotZ` float NOT NULL, 
  `numberplate` text NOT NULL, 
  `ownerID` int(100) NOT NULL, 
  `interior` int(30) NOT NULL, 
  `dimension` int(30) NOT NULL, 
  `enginestate` tinyint(1) NOT NULL, 
  `fuel` int(4) NOT NULL, 
  `tintedwindows` int(1) NOT NULL, 
  `faction` int(10) NOT NULL, 
  `locked` tinyint(1) NOT NULL, 
  `lights` int(1) NOT NULL, 
  `respawnX` float NOT NULL, 
  `respawnY` float NOT NULL, 
  `respawnZ` float NOT NULL, 
  `respawnRotX` float NOT NULL, 
  `respawnRotY` float NOT NULL, 
  `respawnRotZ` float NOT NULL, 
  `red1` int(3) NOT NULL, 
  `green1` int(3) NOT NULL, 
  `blue1` int(3) NOT NULL, 
  `red2` int(3) NOT NULL, 
  `green2` int(3) NOT NULL, 
  `blue2` int(3) NOT NULL, 
  `health` int(5) NOT NULL, 
  PRIMARY KEY (`vehicleID`) 
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 
  
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; 
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Full SQL script

--[[ 
---         |\-/| Roleplay VX Version one |\-/|  --- 
---          Developer: AeroXbird & Lukkaz       --- 
---             Special Thanks: Mabako           --- 
---                 SQL SYSTEM                   --- 
---        Copyright (c) 2010 MTA: Paradise      --- 
--]] 
  
local connection = nil 
local connection = nil 
local null = nil 
local results = { } 
local max_results = 128 
  
-- connection functions 
local function connect( ) 
    -- retrieve the settings 
    local server = get( "server" ) or "" -- server 
    local user = get( "user" ) or "" -- username 
    local password = get( "password" ) or "" -- password 
    local db = get( "database" ) or "" -- database 
    local port = get( "port" ) or 3306 
    local socket = get( "socket" ) or nil 
     
    -- connect 
    connection = mysql_connect ( server, user, password, db, port, socket ) 
    if connection then 
        if user == "root" then 
            setTimer( outputDebugString, 100, 1, "Connecting to your MySQL as 'root' is strongly discouraged.", 2 ) 
        end 
        return true 
    else 
        outputDebugString ( "Connection to MySQL Failed.", 1 ) 
        return false 
    end 
end 
  
local function disconnect( ) 
    if connection and mysql_ping( connection ) then 
        mysql_close( connection ) 
    end 
end 
  
local function checkConnection( ) 
    if not connection or not mysql_ping( connection ) then 
        return connect( ) 
    end 
    return true 
end 
  
addEventHandler( "onResourceStart", resourceRoot, 
    function( ) 
        if not mysql_connect then 
            if hasObjectPermissionTo( resource, "function.shutdown" ) then 
                shutdown( "MySQL module missing." ) 
            end 
            cancelEvent( true, "MySQL module missing." ) 
        elseif not hasObjectPermissionTo( resource, "function.mysql_connect" ) then 
            if hasObjectPermissionTo( resource, "function.shutdown" ) then 
                shutdown( "Insufficient ACL rights for mysql resource." ) 
            end 
            cancelEvent( true, "Insufficient ACL rights for mysql resource." ) 
        elseif not connect( ) then 
            if connection then 
                outputDebugString( mysql_error( connection ), 1 ) 
            end 
             
            if hasObjectPermissionTo( resource, "function.shutdown" ) then 
                shutdown( "MySQL failed to connect." ) 
            end 
            cancelEvent( true, "MySQL failed to connect." ) 
        else 
            null = mysql_null( ) 
        end 
    end 
) 
  
addEventHandler( "onResourceStop", resourceRoot, 
    function( ) 
        for key, value in pairs( results ) do 
            mysql_free_result( value.r ) 
            outputDebugString( "Query not free()'d: " .. value.q, 2 ) 
        end 
         
        disconnect( ) 
    end 
) 
  
-- 
  
function escape_string( str ) 
    if type( str ) == "string" then 
        return mysql_escape_string( connection, str ) 
    elseif type( str ) == "number" then 
        return tostring( str ) 
    end 
end 
  
local function query( str, ... ) 
    checkConnection( ) 
     
    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 = mysql_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 
        mysql_free_result( result ) 
        return false, "Unable to allocate result in pool" 
    end 
    return false, mysql_error( connection ) 
end 
  
function query_free( str, ... ) 
    if sourceResource == getResourceFromName( "runcode" ) then 
        return false 
    end 
     
    checkConnection( ) 
     
    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 = mysql_query( connection, str ) 
    if result then 
        mysql_free_result( result ) 
        return true 
    end 
    return false, mysql_error( connection ) 
end 
  
function free_result( result ) 
    if results[ result ] then 
        mysql_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 = mysql_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 = mysql_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 = mysql_affected_rows( connection ) 
        free_result( result ) 
        return rows 
    end 
    return false, error 
end

sql lua script

--[[ 
Copyright (c) 2010 MTA: Paradise 
]] 
  
local allowUpdate = get( 'auto_update' ) ~= 0 -- change this setting to 0 to only print the required queries to console but not actually change stuff, removing it or setting it to anything but 0 leaves it enabled 
  
-- 
  
local query_update = nil 
if allowUpdate then 
    query_update = query_free 
else 
    query_update = function( 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 
            outputServerLog( str ) 
            return true 
        end 
end 
local function getColumnString( value ) 
    local str = "`" .. escape_string( value.name ) .. "` " .. escape_string( value.type ) 
     
    if not value.null then 
        str = str .. " NOT NULL" 
    end 
     
    if value.default then 
        if value.default == 'CURRENT_TIMESTAMP' then 
            str = str .. " DEFAULT CURRENT_TIMESTAMP" 
        else 
            str = str .. " DEFAULT '" .. escape_string( tostring( value.default ) ) .. "'" 
        end 
    end 
     
    if value.auto_increment then 
        str = str .. " AUTO_INCREMENT" 
    end 
     
    return str 
end 
  
function create_table( name, columns ) 
    if sourceResource == getResourceFromName( "runcode" ) then 
        return false 
    end 
     
    if not query_assoc_single( "SHOW TABLES LIKE '%s'", name ) then 
        -- try to create the missing table 
        local cols = { } 
        local keys = { } 
        local autoIncrementValue = "" 
         
        for key, value in pairs( columns ) do 
            if value.primary_key then 
                table.insert( keys, "`" .. escape_string( value.name ) .."`" ) 
            end 
             
            if type( value.auto_increment ) == "number" then 
                autoIncrementValue = " AUTO_INCREMENT=" .. value.auto_increment 
            end 
             
            table.insert( cols, getColumnString( value ) ) 
        end 
         
        if #keys > 0 then 
            table.insert( cols, "PRIMARY KEY (" .. table.concat( keys, ", " ) .. ")" ) 
        end 
         
        if query_update( "CREATE TABLE `%s`(\n  " .. table.concat( cols, ",\n  " ) .. "\n) ENGINE=MyISAM" .. autoIncrementValue, name ) then 
            outputDebugString( "Created table " .. name, 3 ) 
            return true, true 
        else 
            outputDebugString( "Unable to create table " .. name, 1 ) 
            return false 
        end 
    else 
        -- make sure all columns do exist 
        local result = query_assoc( 'DESCRIBE ' .. name ) 
        local fields = { } 
        local primary_keys = { } 
        local has_primary_key = false 
        local change_primary_keys = false 
         
        for key, value in pairs( result ) do 
            fields[ value.Field ] = { name = value.Field, type = value.Type, null = value.Null == "YES", auto_increment = value.Extra == "auto_increment", primary_key = value.Key == 'PRI' or nil, default = value.Default } 
            if value.Key == 'PRI' then 
                has_primary_key = true 
            end 
        end 
         
        local insertWhere = "FIRST" 
        for key, value in ipairs( columns ) do 
            if not fields[ value.name ] then 
                if query_update( "ALTER TABLE `%s` ADD " .. getColumnString( value ) .. " " .. insertWhere, name ) then 
                    outputDebugString( "Created column " .. name .. "." .. value.name, 3 ) 
                else 
                    outputDebugString( "Unable to create column " .. name .. "." .. value.name, 1 ) 
                    return false 
                end 
            else 
                -- let's check if everything is alright 
                local f = fields[ value.name ] 
                local str = getColumnString( value ) 
                if getColumnString( f ) ~= str then 
                    if query_update( "ALTER TABLE `%s` MODIFY COLUMN " .. str, name ) then 
                        outputDebugString( "Changed field " .. name .. "." .. value.name, 3 ) 
                    else 
                        outputDebugString( "Changing field " .. name .. "." .. value.name .. " failed", 1 ) 
                        return false 
                    end 
                end 
                 
                -- verify our primary keys 
                if value.primary_key then 
                    table.insert( primary_keys, "`" .. escape_string( value.name ) .."`" ) 
                end 
                 
                if f.primary_key ~= value.primary_key then 
                    change_primary_keys = true 
                end 
            end 
            insertWhere = "AFTER `" .. escape_string( value.name ) .. "`" 
        end 
         
        -- change the primary key if we have to 
        if change_primary_keys then 
            outputDebugString( "Changing primary keys...", 3 ) 
            if has_primary_key then 
                if #primary_keys == 0 then 
                    if not query_update( "ALTER TABLE `%s` DROP PRIMARY KEY", name ) then 
                        outputDebugString( "Unable to drop primary key", 1 ) 
                        return false 
                    end 
                else 
                    if not query_update( "ALTER TABLE `%s` DROP PRIMARY KEY, ADD PRIMARY KEY(" .. table.concat( primary_keys, ", " ) .. ")", name ) then 
                        outputDebugString( "Unable to change primary key", 1 ) 
                        return false 
                    end 
                end 
            elseif #primary_keys > 0 then 
                if not query_update( "ALTER TABLE `%s` ADD PRIMARY KEY(" .. table.concat( primary_keys, ", " ) .. ")", name ) then 
                    outputDebugString( "Unable to add new primary key", 1 ) 
                    return false 
                end 
            end 
        end 
        return true, false 
    end 
end

Lay out

drk · February 25, 2012

It isn't SQL, but MySQL. And you have to put here your mySQL config:

    local server = get( "server" ) or "" -- server 
    local user = get( "user" ) or "" -- username 
    local password = get( "password" ) or "" -- password 
    local db = get( "database" ) or "" -- database 
    local port = get( "port" ) or 3306 
    local socket = get( "socket" ) or nil

BlueBerry · February 26, 2012

Works now thanks

drk · February 26, 2012

You're welcome

tim260 · February 26, 2012

You're welcome

He told that because he doesnt understand shit about it, i spoke with him today where working together on a server we get an error on the start of swl, its lemme look :

[2012-02-26 10:26:54] Starting sql 
[2012-02-26 10:26:54] WARNING: [gameplay]\sql\mysql.lua:55: Access denied @ 'shutdown' 
[2012-02-26 10:26:54] ERROR: [gameplay]\sql\mysql.lua:26: attempt to call global 'mysql_connect' (a nil value) 
[2012-02-26 10:26:54] ERROR: call: failed to call 'sql:query_assoc' [string "?"] 
[2012-02-26 10:26:54] ERROR: [gameplay]\interior\s_interior.lua:16: bad argument #1 to 'ipairs' (table expected, got boolean) 
[2012-02-26 10:26:54] Start up of resource sql cancelled by script 
[2012-02-26 10:26:54] Stopping sql 
[2012-02-26 10:26:54] start: Resource 'sql' start was requested (Start up of resource cancelled by script 
)

drk · February 26, 2012

First, you have to put resource 'sql' in acl group admin.

Second, edit this:

    local server = get( "server" ) or "" -- server 
    local user = get( "user" ) or "" -- username 
    local password = get( "password" ) or "" -- password 
    local db = get( "database" ) or "" -- database 
    local port = get( "port" ) or 3306 
    local socket = get( "socket" ) or nil

Put like this:

    local server = get( "server" )  
    local user = get( "user" )  
    local password = get( "password" ) 
    local db = get( "database" )  
    local port = get( "port" ) 
    local socket = get( "socket" )

And set in meta.xml of resource server setting, user, password, database, port and socket.

Or:

    local server = "Server IP" 
    local user = "MySQL User Name" 
    local password = "MySQL Password" 
    local db = "Database name" 
    local port = "Server Port" -- not your mta sa server port ( default: 3306 ) 
    local socket = "Server Socket"

For error "bad argument #1 to 'ipairs'" show s_interior.lua.

tim260 · February 26, 2012

And set in meta.xml of resource server setting, user, password, database, port and socket.

Or:
    local server = "Server IP" 
    local user = "MySQL User Name" 
    local password = "MySQL Password" 
    local db = "Database name" 
    local port = "Server Port" -- not your mta sa server port ( default: 3306 ) 
    local socket = "Server Socket" 
For error "bad argument #1 to 'ipairs'" show s_interior.lua.

thats the part i dont understand

drk · February 26, 2012

And set in meta.xml of resource server setting, user, password, database, port and socket.

Or:
    local server = "Server IP" 
    local user = "MySQL User Name" 
    local password = "MySQL Password" 
    local db = "Database name" 
    local port = "Server Port" -- not your mta sa server port ( default: 3306 ) 
    local socket = "Server Socket" 
For error "bad argument #1 to 'ipairs'" show s_interior.lua.
thats the part i dont understand

What you don't understand? This:

    local server = "Server IP" 
    local user = "MySQL User Name" 
    local password = "MySQL Password" 
    local db = "Database name" 
    local port = "Server Port" -- not your mta sa server port ( default: 3306 ) 
    local socket = "Server Socket"

Or the error Bad argument ... ?

tim260 · February 26, 2012

And set in meta.xml of resource server setting, user, password, database, port and socket.

Or:
    local server = "Server IP" 
    local user = "MySQL User Name" 
    local password = "MySQL Password" 
    local db = "Database name" 
    local port = "Server Port" -- not your mta sa server port ( default: 3306 ) 
    local socket = "Server Socket" 
For error "bad argument #1 to 'ipairs'" show s_interior.lua.
thats the part i dont understand
where to place it

What you don't understand? This:
    local server = "Server IP" 
    local user = "MySQL User Name" 
    local password = "MySQL Password" 
    local db = "Database name" 
    local port = "Server Port" -- not your mta sa server port ( default: 3306 ) 
    local socket = "Server Socket" 
Or the error Bad argument ... ?

drk · February 26, 2012

All?

Sign In

Problem with SQL

Recommended Posts

BlueBerry

Link to comment

drk

Link to comment

BlueBerry

Link to comment

drk

Link to comment

BlueBerry

Link to comment

drk

Link to comment

BlueBerry

Link to comment

drk

Link to comment

tim260

Link to comment

drk

Link to comment

tim260

Link to comment

drk

Link to comment

tim260

Link to comment

drk

Link to comment

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

Activity

MTA Network

Social Media