balthus
Joined: 07 Jul 2009
Posts: 16
|
 Posted: Mon Mar 08, 2010 9:03 am Post subject: Generic MySQL Connector. |
 |
|
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 
| 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};
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
