I rewrite a DOC based BBS using MySQL quite awhile back.
Couple of suggestions:
Make these global in a struct.
MYSQL MySQLHelpConn;
MYSQL_RES * result;
MYSQL_ROW row;
And create a function that you can pass the query to, returning the struct
as the result.
This will save you much time in the future, not having to do as much
manually.
here is some of my code, which is easily adaptable.
typedef struct database {
MYSQL_RES *result; /* The result from the query */
MYSQL_ROW row; /* the row info */
int rows; /* how many rows were returned */
int fields; /* how many fields are returned */
} *db_struct;
void connect_db(void)
{
if (!mysql_connect(&mybbs,"localhost","username","passowrd"))
error_db(0);
if ((mysql_select_db(&mybbs,"databasename"))<0)
error_db(0);
}
struct database *send_query(char *command)
{
struct database *db=NULL;
sigset_t set;
/* mysql gets REALLY pissy if it receives signals during a query
execution.
I learned this the hard way, and this block popular signals
while
the query executes, then re-enable them when done */
if (sigemptyset(&set)<0)
perror("sigempyset");
sigaddset(&set, SIGUSR1);
sigaddset(&set, SIGIO);
sigaddset(&set, SIGALRM);
sigaddset(&set, SIGUSR2);
sigaddset(&set, SIGINT);
sigaddset(&set, SIGQUIT);
sigaddset(&set, SIGHUP);
sigaddset(&set, SIGTERM);
sigaddset(&set, SIGABRT);
if(sigprocmask(SIG_BLOCK,&set,NULL)<0)
perror("sigprocmask");
db = (struct database *) calloc(1, sizeof(struct database ));
if (mysql_query(&mybbs, command)){
error_db(command);
}
if (!(db->result=mysql_store_result(&mybbs))){
error_db(command);
}
db->rows=mysql_num_rows(db->result);
db->fields=mysql_num_fields(db->result);
if (sigprocmask(SIG_UNBLOCK,&set, NULL)<0)
perror("sigprocmask");
return(db);
}
int send_update(char *command)
{
sigset_t set;
if (sigemptyset(&set)<0)
perror("sigempyset");
sigaddset(&set, SIGUSR1);
sigaddset(&set, SIGIO);
sigaddset(&set, SIGALRM);
sigaddset(&set, SIGUSR2);
sigaddset(&set, SIGINT);
sigaddset(&set, SIGQUIT);
sigaddset(&set, SIGHUP);
sigaddset(&set, SIGTERM);
sigaddset(&set, SIGABRT);
if(sigprocmask(SIG_BLOCK,&set,NULL)<0)
perror("sigprocmask");
if (mysql_query(&mybbs, command)){
error_db(command);
}
if (sigprocmask(SIG_UNBLOCK,&set, NULL)<0)
perror("sigprocmask");
return(mysql_affected_rows(&mybbs));
}
void error_db(char *sql)
{
FILE *fp;
char *error=NULL;
error=mysql_error(&mybbs);
fp=fopen("dberrors", "a");
if (!fp) {
perror("fopen");
}
if (error){
fprintf(fp, "Error:\n%s (error length: %i)\n", error,
strlen(error));
if (sql){
fprintf(fp, "executing:\n%s\n", sql);
}
fclose(fp);
disconnect_db();
exit(0);
}
else return;
}
void disconnect_db(void)
{
mysql_close(&mybbs);
return;
}
===================================================
Then you can change your code to do something like this:
void do_help(CHAR_DATA * ch, char *argument) {
BUFFER * output;
char argone[MIL];
char cleaned[MIL*2];
int records;
char sql[MSL*2];
struct database *db=NULL;
if (argument[0] == '\0') argument = "summary";
connect_db();
output = new_buf();
result = NULL;
mysql_escape_string(cleaned, argument, strlen(argument));
while ( argument[0] != '\0' ) {
argument = one_argument( argument, argone );
sprintf(sql, "SELECT level, keyword, htext FROM helps WHERE
keyword LIKE '%%%s%%' AND level <= %d", argone, ch->level);
db=send_query(sql);
if (!db->rows){
stc("No helpfile found.\n\r", ch);
return;
}
/* you'll have to do the rest with mysql_fetch_row */
disconnect_db();
return;
}
Hope that helps.
Note, that connecting and disconnecting everytime someone reads a helpfile
is pretty innefficient.
Make the connection once at mud startup, and then you can throw SQL
statements at it whenever you want.
Jason
>
> bool open_close_db( MYSQL MySQLConn, bool open ) {
> if (open) {
> if (!mysql_init(&MySQLConn)) {
> logf("For some reason, open_close_db couldn't init a mysql
> connection.
> exitting (BEAUTIFYME)");
> return FALSE;
> }
> if ((mysql_real_connect(&MySQLConn, MY_SERVER, MY_USER,
> MY_PWD, MY_DB, 0,
> NULL, 0)) == NULL) {
> logf("open_close_db died connecting to the mysql server");
> mysql_close(&MySQLConn);
> return FALSE;
> }
> }
> else
> mysql_close(&MySQLConn);
> return TRUE;
> }
>
> And here are the first few lines of my do_help function which
> returns the
> help entry entered by the user:
> void do_help(CHAR_DATA * ch, char *argument) {
> BUFFER * output;
> char argone[MIL];
> char cleaned[MIL*2];
> int records;
> char sql[MSL*2];
> MYSQL MySQLHelpConn;
> MYSQL_RES * result;
> MYSQL_ROW row;
>
> if (argument[0] == '\0') argument = "summary";
>
> if (!open_close_db( MySQLHelpConn, TRUE ) ) {
> send_to_char( "ERROR: Could not open the database for
> opening. Please
> contact an Imm.\n\r", ch );
> return;
> }
>
> output = new_buf();
> result = NULL;
> mysql_escape_string(cleaned, argument, strlen(argument));
> while ( argument[0] != '\0' ) {
> argument = one_argument( argument, argone );
>
> sprintf(sql, "SELECT level, keyword, htext FROM helps WHERE
> keyword LIKE
> '%%%s%%' AND level <= %d", argone, ch->level);
> if (mysql_real_query(&MySQLHelpConn, sql, strlen(sql)) != 0) {
> logf("do_help died attempting query for %s", argone);
> send_to_char( "Error while getting a query from the
> database. Please
> inform an Imm.\n\r", ch );
> mysql_close(&MySQLHelpConn);
> return;
> }
>
>
> The problem with this, is that the if statement I have here,
> returns -1 as
> it's value. I don't know what I'm doing wrong here. I've also
> tried putting
> mysql_init( &MySQLHelpConn ); b4 the open_close_db function call. That
> didn't help. Any suggestions would be appreciated for this.
>
> Yep, me again,
> Rheede :p
>
>
>
> --
> ROM mailing list
> [email protected]
> http://www.rom.org/cgi-bin/mailman/listinfo/rom
>
