TinTin++ Mud Client The TinTin++ message board

 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
TinTin++ Mud Client

Generic MySQL Connector.

 
Post new topic   Reply to topic    The TinTin++ message board Forum Index -> Finished Scripts
View previous topic :: View next topic  
Author Message
balthus



Joined: 07 Jul 2009
Posts: 39

PostPosted: Mon Mar 08, 2010 9:03 am    Post subject: Generic MySQL Connector. Reply with quote

Generic MySQL Connector.

Quote:
db_submit {outputVar} {select * from main_table where field_a = '%s' and field_b = '%s'} {field 'a' match string} {field 'b' match string}


The alias takes parameters in the same style as the #FORMAT command, one output variable, one main SQL statement with %s etc substitutions and then a list of further parameters to substitute into the main statement. By working this way, the script is able to deal with quotes within whatever input data is passed to avoid breaking the SQL command sent to the database.

The output data from a select is put into the specified output variable as an associative array for ease of use within Tintin++

All comments/improvements gratefully received Smile

Code:

#CLASS {cDBConnector} {kill};
#CLASS {cDBConnector} {open};

#NOP /********************************************************************/;
#NOP /* Generic MySQL Database Connector                                 */;
#NOP /* By Balthus - 8th March 2010                                      */;
#NOP /*                                                                  */;
#NOP /* db_submit works the same way as #FORMAT                          */;
#NOP /*                                                                  */;
#NOP /* db_submit {returnvar}                                            */;
#NOP /* {select * from tablename where fieldname = '%s'} {match text}    */;
#NOP /*                                                                  */;
#NOP /* Parses the output and puts it into an associative array:         */;
#NOP /* $returnvar[recordnumber][fieldname]                              */;
#NOP /*                                                                  */;
#NOP /* E.G. to get the data from HP field of the first record returned: */;
#NOP /* #VAR {varname} {$returnvar[1][HP]}                               */;
#NOP /*                                                                  */;
#NOP /********************************************************************/;

#NOP **** Database Connection Info ****;
#VAR {dbSettings[user]} {put database user here};
#VAR {dbSettings[pass]} {put database password here};
#VAR {dbSettings[database]} {put database name here};
#VAR {dbSettings[server]} {localhost};

#NOP **** Constants ****;
#FORMAT {dbSettings[tab]} {%a} {9};

#ALIAS {db_submit}
{
    #IF {"%0" != ""}
    {
        #VAR {db} {};

        #NOP /* Start constructing #FORMAT command to construct full SQL query string */;
        #VAR {dbFormat} {#FORMAT {db[Command]} {%2}};
        #VAR {db[Count]} {0};

        #NOP /* Parse parameters 3->n, remove quotes, and append to #FORMAT command string */;
        #FOREACH {%0} {db[Temp]}
        {
            #MATH {db[Count]} {$db[Count] + 1};
            #IF {$db[Count] > 2}
            {
                #REPLACE {db[Temp]} {"} {'};
                #REPLACE {db[Temp]} {'} {''};
                #IF {"$db[Temp]" != ""}
                {

                    #VAR {dbFormat} {${dbFormat}{${db[Temp]}}};
                };
            };
        };

        #NOP /* Perform built #FORMAT command to construct full SQL command in db[Command] */;
        ${dbFormat};

        #NOP /* Send constructed SQL command to the database, collecting returned raw output (if any) into db[Return] */;
        #SCRIPT {db[return]} {mysql -h${dbSettings[server]} -u${dbSettings[user]} -p${dbSettings[pass]} -D${dbSettings[database]} -Be "${db[Command]}"};

        #VAR {db[output]} {};
        #VAR {db[temp]} {};

        #NOP /* Loop through returned tab-delimited records (if any) and process */;
        #FOREACH {$db[return][]} {i}
        {
            #VAR {db[fieldindex]} {0};
            #MATH {db[recordindex]} {$i - 1};

            #NOP /* Loop through every character in the current record...split into fields on tab */;
            #PARSE {$db[return][$i]} {j}
            {
                #IF {"$j" == "$dbSettings[tab]"}
                {
                    #MATH {db[fieldindex]} {$db[fieldindex] + 1};

                    #NOP /* If its the first record (header record) set the field names */;
                    #NOP /* Otherwise populate the field contents                       */;
                    #IF {$i == 1}
                    {
                        #VAR {db[fields][$db[fieldindex]]} {$db[temp]};
                    }
                    {
                        #VAR {db[output][$db[recordindex]][$db[fields][$db[fieldindex]]]} {$db[temp]};
                    };
                    #VAR {db[temp]} {};
                }
                {
                    #VAR {db[temp]} {$db[temp]${j}};
                };
            };

            #NOP /* Process the data remaining after the last tab on a record */;
            #MATH {db[fieldindex]} {$db[fieldindex] + 1};
            #IF {$i == 1}
            {
                #VAR {db[fields][$db[fieldindex]]} {$db[temp]};
            }
            {
                #VAR {db[output][$db[recordindex]][$db[fields][$db[fieldindex]]]} {$db[temp]};
            };
            #VAR {db[temp]} {};
        };
        #IF {"%1" != ""}
        {
            #VAR {%1} {$db[output]};
        };
        #UNVAR {db};
        #UNVAR {dbFormat};
    };
};

#CLASS {cDBConnector} {close};
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    The TinTin++ message board Forum Index -> Finished Scripts All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Get TinTin++ Mud Client at SourceForge.net. Fast, secure and Free Open Source software downloads Get TinTin++ Mud Client at SourceForge.net. Fast, secure and Free Open Source software downloads
TinTin++ Homepage

Powered by phpBB © 2001, 2002 phpBB Group