1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. GTA Network forum is now in archive mode.

MySQL Connector

Discussion in 'Libraries/Wrappers/Web' started by Gomitung, Sep 29, 2016.

  1. Gomitung

    Gomitung Member

    Messages:
    44
    Likes Received:
    36
    Joined:
    Aug 2, 2016
    Hello!

    I have made this resource to help you out with linking with a MySQL database without any effort!

    You can call it through the following exports:
    • executeQueryWithResult(string sql) - Execute a query that will return a DataTable with the results
    • executePreparedQueryWithResult(string sql, Dictionary<string,string> parameters) - Execute a prepared query that will return a DataTable with the results
    • executeQuery(string sql) - Execute a query with no return
    • executePreparedQuery(string sql, Dictionary<string,string> parameters) - Execute a prepared query with no return
    • createDataTable(string sql, string unique_name) - returns a DataTable that is connected to a MySQLDataAdapter identified with unique_name
    • updateDataTable(string unique_name, DataTable updatedTable) - Updates the MySQLDataAdapter with the updatedTable
    • closeDataTable(string unique_name) - Deletes the MySQLDataAdapter identified with unique_name from storage and Disposes it.
    You can see the following examples to see how you can work with this resource:

    Code:
    using System;
    using System.Data;
    using GTANetworkServer;
    using GTANetworkShared;
    using System.Threading;
    using System.Collections.Generic;
    
    public class DatabaseTest : Script
    {
       public DatabaseTest()
        {
            API.onResourceStart += onResourceStart;
        }
    
        public void onResourceStart()
        {
            while (!API.exported.database.isReady())
            {
                Thread.Sleep(500);
            }
            /* Settings Table (varchar name, varchar value)
             * ROW 1: name = testing, value ="It works!"
             * ROW 2: name = testing2, value="This also works!"
             */
            string sql = "SELECT value FROM settings WHERE name=@name";
            Dictionary parameters = new Dictionary();
            parameters.Add("@name", "testing");
            DataTable result = API.exported.database.executePreparedQueryWithResult(sql, parameters);
            string value = result.Rows[0][0].ToString();
            API.consoleOutput(value); //Displays: It works!
            parameters["@name"] = "testing2";
            DataTable second_result = API.exported.database.executePreparedQueryWithResult(sql, parameters);
            string second_value = second_result.Rows[0][0].ToString();
            API.consoleOutput(second_value); //Displays: This also works!
        }
    }
    

    Code:
    using System;
    using System.Data;
    using GTANetworkServer;
    using GTANetworkShared;
    using System.Threading;
    using System.Collections.Generic;
    
    public class DatabaseTest : Script
    {
        public DatabaseTest()
        {
            API.onResourceStart += onResourceStart;
        }
    
        public void onResourceStart()
        {
            while (!API.exported.database.isReady())
            {
                Thread.Sleep(500);
            }
            /* Settings Table (varchar name, varchar value)
             * ROW 1: name = testing, value ="It works!"
             * ROW 2: name = testing2, value="This also works!"
             */
            API.exported.database.executeQuery("INSERT INTO settings (name,value) VALUES('testing3','Amazing')"); // If an error happens it throws an exception
        }
    }
    

    This resource comes in a rar file that includes a compiled version and a source one.

    This resource will be maintained and the following will be added:
    • Stored procedures

    Feel free to contribute or ask any questions!
     

    Attached Files:

    Last edited: Oct 1, 2016
  2. nobody

    nobody Well-Known Member Donator Language Board Moderator Tester

    Messages:
    108
    Likes Received:
    111
    Joined:
    Aug 25, 2016
    Nice, its always a struggle for 'not experienced programmers' to connect to the database. I am glad someone made something to make life easier for them :=D
     
  3. Adam

    Adam Staff Member Administrator Webmaster Developer

    Messages:
    275
    Likes Received:
    146
    Joined:
    Apr 3, 2016
    Nice one! understandable, easy and simple.
     
  4. Kemula

    Kemula Active Member Language Board Moderator

    Messages:
    86
    Likes Received:
    26
    Joined:
    Aug 6, 2016
    Nice resource. Do we need to escape or filter user's input when using prepared queries?
     
  5. Tim

    Tim Active Member Donator

    Messages:
    72
    Likes Received:
    16
    Joined:
    Aug 4, 2016
    I wouldn't keep a persistent connection/share connections. AFAIK both the MS SQL and MySQL features support connection pooling. Would rather wrap the connection in a using statement that discards it once the query is executed (if there's a connection in the connection pool it will use that connection for the query, is that connection "full of queries" it'll boot up a new connection). MysqlConnection implements IDisposable after all.

    Here's an article I found that details it a bit: http://derekwill.com/2015/02/17/proper-etiquette-for-using-mysql-in-c-part-of-1-of-3/
     
  6. Gomitung

    Gomitung Member

    Messages:
    44
    Likes Received:
    36
    Joined:
    Aug 2, 2016
    Updated

    Change log:
    • Fixed Pooling
    • Added SSL authentication
    • Added MySQLDataAdapters with DataTables
    • Removed isReady
     
  7. LapizTJ

    LapizTJ Member

    Messages:
    24
    Likes Received:
    6
    Joined:
    Sep 24, 2016
    How do you actually set it up and allow the exports to be useable within a resource? I've been trying for the past week to get it working, and no luck so far. If you could give some sort of tutorial or steps, that'd be great. Thanks
     
    Last edited: Oct 13, 2016
    StreetGT likes this.
  8. StreetGT

    StreetGT Senpai Global Moderator Wiki Moderator Developer

    Messages:
    201
    Likes Received:
    90
    Joined:
    Aug 8, 2016
    First methods need to be public, then you need to specify
    Code:
     <export class="Class" function="methodExported" />
    finally you need to import them using
    Code:
    <include resource="Resourcename" />
     
  9. Jstylezzz

    Jstylezzz Active Member Language Board Moderator

    Messages:
    63
    Likes Received:
    11
    Joined:
    Sep 23, 2016
    Great work! This will definitely make life easier for people. On a side note though, since the source is public anyway, you might find it useful to put it up on GitHub or something.
     
  10. Gomitung

    Gomitung Member

    Messages:
    44
    Likes Received:
    36
    Joined:
    Aug 2, 2016
  11. Jstylezzz

    Jstylezzz Active Member Language Board Moderator

    Messages:
    63
    Likes Received:
    11
    Joined:
    Sep 23, 2016
  12. Jefferson

    Jefferson Active Member

    Messages:
    53
    Likes Received:
    29
    Joined:
    Oct 15, 2016
  13. Theory

    Theory aka Austin Global Moderator Wiki Moderator Developer

    Messages:
    59
    Likes Received:
    25
    Joined:
    Aug 6, 2016
    Just noticed, this should be re-uploaded into a universal archive format, such as zip.
     
  14. Kempl

    Kempl Member

    Messages:
    19
    Likes Received:
    1
    Joined:
    Nov 22, 2016
    Hey guys, i´m new in sql. I took your files and edited them, all fine. The connection to the database was successful.
    Now my questions :=D.

    How can i use the function of this ressource in other ressources?
    How can i add values in the tables? With "INSERT INTO tablename VALUES (value,...);" maybe?
    I tried that but i got always errors. Maybe you can help me?

    For Example my goal is that i can save values from the Player in the table (e.g. Name, Age, Skin etc.)

    I know that i must load this data when the player connects to the server and safe it when the player leaves the server but i dont know how i can program that.
     
  15. Seaf

    Seaf Member

    Messages:
    8
    Likes Received:
    3
    Joined:
    Sep 18, 2016
    You can export functions in order to use them in other resources with API.exported and API.call. I can't help you more with this since I don't use that myself.

    Almost. The extact syntax is
    Code:
    INSERT INTO [Tablename](ColumnName1, ColumnName2, ...) VALUES(Value1,Value2, ...);
    So according to your example, it could be
    Code:
    INSERT INTO Player(Name, Age, Skin) VALUES('Kempl',20,'Abigail');
    Finally, in order to load and save data when the player connects and disconnects, you just have to code in OnPlayerConnected and OnPlayerDisconnected.
     
  16. Kempl

    Kempl Member

    Messages:
    19
    Likes Received:
    1
    Joined:
    Nov 22, 2016
    I tried to add something to my database into the table "People" with the Attributes (Name, Age).

    Code:
    API.exported.database.executeQuery("INSERT INTO People (Name,Age) VALUES('Marco',21)");
    And i got the following errors:
    //edit Ok now i fixed this error with the help from wiki and got the next error :=D


    Code:
    icrosoft.CSharp.RuntimeBinder.RuntimeBinderException: cant call a not delegatetype.
       bei CallSite.Target(Closure , CallSite , Object , String )
       bei System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
       bei CallSite.Target(Closure , CallSite , Object , String )
       bei System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2[T0,T1](CallSite site, T0 arg0, T1 arg1)
       bei GTANetworkServer.ScriptingEngine.<InvokeResourceStart>b__28_0()
       bei GTANetworkServer.ScriptingEngine.MainThreadLoop()
     
    Last edited: Nov 24, 2016
  17. Shawn

    Shawn New Member

    Messages:
    1
    Likes Received:
    0
    Joined:
    Sep 2, 2016
    Using prepared statements does not require escaping?
     
  18. Uncreative

    Uncreative Member

    Messages:
    21
    Likes Received:
    1
    Joined:
    Dec 5, 2016
    Code:
    [18:11:14] System.Reflection.TargetInvocationException: Exception has been throw
    n by the target of an invocation. ---> Microsoft.CSharp.RuntimeBinder.RuntimeBin
    derException: 'System.Dynamic.ExpandoObject' does not contain a definition for '
    isReady'
       at CallSite.Target(Closure , CallSite , Object )
       at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T
    0 arg0)
       at GTANETWORK.Chat.Register(Client sender, String password)
       --- End of inner exception stack trace ---
       at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments,
     Signature sig, Boolean constructor)
       at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Objec
    t[] parameters, Object[] arguments)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invoke
    Attr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
       at GTANetworkServer.ScriptingEngine.<>c__DisplayClass26_0.<InvokeVoidMethod>b
    __0()
       at GTANetworkServer.ScriptingEngine.MainThreadLoop()
    Got this exception thrown at me when I tried storing something in a table. Here's the code that does it:

    Code:
    while(!API.exported.database.isReady())
                    { Thread.Sleep(50); }
                    API.exported.database.executeQuery("INSERT INTO regusers(PlayerData) VALUES(255)");
    RegUsers holds only one column PlayerData, which stores data of type "VARBINARY".


    EDIT: We gave up on trying to use this and changed to a database better suited for object oriented programming.
     
    Last edited: Dec 10, 2016
  19. unSatisfied

    unSatisfied Member

    Messages:
    22
    Likes Received:
    4
    Joined:
    Dec 18, 2016
    Looks great, however I don't really see any reason to use this over Entity Framework.
     
  20. Kruzzel

    Kruzzel Active Member

    Messages:
    61
    Likes Received:
    2
    Joined:
    Dec 12, 2016

Share This Page