/*
* I wanted a function that would allow me to easily make a select statement from inside the MUD, so here it is..
* It will mostly format the output in a boxed setup. Todo - break this out so you can use select, insert, update, or create
* effectively becoming a remote interface to run queries of any type..
* -Syn
*/
void do_sqlgenselect(CHAR_DATA *ch, char *argument)
{
MYSQL_RES *res;
char query[4096*2];
snprintf(query, sizeof(query) -1, argument);
if(!mysql_safe_query(query))
{
stc("#W\n\rSomething went wrong, check the string and try again.\n\r#0",ch);
return;
}
res = mysql_store_result(&db);
unsigned int numfields = mysql_num_fields(res);
if((numfields == 0) || (numfields == NULL))
{
stc("#W\n\rSomething went wrong, check the string and try again.\n\r#0",ch);
return;
}
if(argument == NULL)
{
send_to_char("SQL SELECT query.. general syntax: SELECT <stuff> FROM <someplace> ORDER BY <root-order>\n\r",ch);
return;
}
if((res == NULL) || (!res))
{
send_to_char("It might help if you entered a valid SQL SELECT query.. general syntax: SELECT <stuff> FROM <someplace> ORDER BY <root-order>\n\r",ch);
return;
}
process_result_set(ch, &db, res);
mysql_free_result(res);
return;
}
/* #@ _PROCESS_CALL_RESULT_ */
static void process_call_result (MYSQL *conn, MYSQL_STMT *stmt)
{
int status;
int num_cols;
/*
* For each result, check number of columns. If none, the result is
* the final status packet and there is nothing to do. Otherwise,
* fetch the result set.
*/
do {
if ((num_cols = mysql_stmt_field_count (stmt)) > 0)
{
/* announce whether result set contains parameters or data set */
if (conn->server_status & SERVER_PS_OUT_PARAMS)
printf ("OUT/INOUT parameter values:\n");
else
printf ("Statement result set values:\n");
if (process_result_set (stmt, num_cols))
break; /* some error occurred */
}
/* status is -1 = done, 0 = more results, >0 = error */
status = mysql_stmt_next_result (stmt);
if (status > 0)
print_stmt_error (stmt, "Error checking for next result");
} while (status == 0);
}
void
process_multi_statement (MYSQL *conn, char *stmt_str)
{
MYSQL_RES *res_set;
int status;
int keep_going = 1;
if (mysql_query (conn, stmt_str) != 0) /* the statement(s) failed */
{
print_error (conn, "Could not execute statement(s)");
return;
}
/* the statement(s) succeeded; enter result-retrieval loop */
do {
/* determine whether current statement returned data */
res_set = mysql_store_result (conn);
if (res_set) /* a result set was returned */
{
/* process rows and free the result set */
process_result_set (conn, res_set);
mysql_free_result (res_set);
}
else /* no result set was returned */
{
/*
* does the lack of a result set mean that the statement didn't
* return one, or that it should have but an error occurred?
*/
if (mysql_field_count (conn) == 0)
{
/*
* statement generated no result set (it was not a SELECT,
* SHOW, DESCRIBE, etc.); just report rows-affected value.
*/
printf ("Number of rows affected: %lu\n",
(unsigned long) mysql_affected_rows (conn));
}
else /* an error occurred */
{
print_error (conn, "Could not retrieve result set");
keep_going = 0;
}
}
/* determine whether more results exist */
/* 0 = yes, -1 = no, >0 = error */
status = mysql_next_result (conn);
if (status != 0) /* no more results, or an error occurred */
{
keep_going = 0;
if (status > 0) /* error */
print_error (conn, "Could not execute statement");
}
} while (keep_going);
}
result_set db_do_select(DB_HANDLE conn,const char *stmt_str)
{
MYSQL_RES *res_set;
if (mysql_query(conn, stmt_str) != 0)
{
print_error(conn, "Could not execute statement");
return NULL;
}
res_set = mysql_store_result(conn);
if(res_set)
{
result_set result = process_result_set(conn, res_set);
mysql_free_result(res_set);
return result;
}
return NULL;
}
void
process_statement (MYSQL *conn, char *stmt_str)
{
MYSQL_RES *res_set;
if (mysql_query (conn, stmt_str) != 0) /* the statement failed */
{
print_error (conn, "Could not execute statement");
return;
}
/* the statement succeeded; determine whether it returned data */
res_set = mysql_store_result (conn);
if (res_set) /* a result set was returned */
{
/* process rows and free the result set */
process_result_set (conn, res_set);
mysql_free_result (res_set);
}
else /* no result set was returned */
{
/*
* does the lack of a result set mean that the statement didn't
* return one, or that it should have but an error occurred?
*/
if (mysql_field_count (conn) == 0)
{
/*
* statement generated no result set (it was not a SELECT,
* SHOW, DESCRIBE, etc.); just report rows-affected value.
*/
printf ("Number of rows affected: %lu\n",
(unsigned long) mysql_affected_rows (conn));
}
else /* an error occurred */
{
print_error (conn, "Could not retrieve result set");
}
}
}
static package bf_mysql_query( Var arglist, Byte next, void *vdata, Objid progr)
{
Var r;
char error_string[MOOSQL_ERROR_LEN];
MYSQL_CONN *wrapper;
MYSQL_RES *res_set;
#ifdef MOOSQL_MULTIPLE_STATEMENTS
Var tmp;
Var end;
int len = 0;
int continu = 1;
#endif
if (!is_wizard(progr))
{
free_var(arglist);
return make_error_pack(E_PERM);
}
Objid oid = arglist.v.list[1].v.obj;
wrapper = resolve_mysql_connection(oid);
if (wrapper == NULL || wrapper->conn == NULL || wrapper->active == 0)
{
free_var(arglist);
return make_error_pack(E_INVARG);
}
const char *query = arglist.v.list[2].v.str;
free_var(arglist);
/* we do the query now. */
if (mysql_query (wrapper->conn, query) != 0) /* failed */
{
/* there is an error, so we will return that string. similar to below which
* returns a string for a successful query with no result set which is handled in
* process_mysql_query */
snprintf(error_string,MOOSQL_ERROR_LEN,"ERR: %s",mysql_error(wrapper->conn));
r.type = TYPE_STR;
r.v.str = str_dup(error_string);
return make_var_pack(r);
}
wrapper->last_query_time = time(0);
#ifdef MOOSQL_MULTIPLE_STATEMENTS
r = new_list(1);
r.v.list[1].type = TYPE_INT;
r.v.list[1].v.num = 0;
end = new_list(0);
while (continu)
{
len++;
res_set = process_mysql_query(wrapper,error_string);
if (res_set == NULL) /* there was no result on this query */
{
tmp.type = TYPE_STR;
tmp.v.str = str_dup(error_string);
end = listappend(end,var_dup(tmp));
}
else
{
tmp = process_result_set(wrapper,res_set);
end = listappend(end,var_dup(tmp));
}
if (mysql_more_results(wrapper->conn))
{
mysql_next_result(wrapper->conn);
continu = 1;
}
else
continu = 0;
}
if (len <= 1) /* if there is only one result return it like in previous versions, a list of rows */
return make_var_pack(end.v.list[1]);
r.v.list[1].v.num = len;
/* if there are more return it in this format {X, Y} where X is the number of results and Y is a list of results */
r = listappend(r,end);
return make_var_pack(r);
#else
res_set = process_mysql_query(wrapper,error_string);
if (res_set == NULL) /* there was either an error / no result on this query */
{
r.type = TYPE_STR;
r.v.str = str_dup(error_string);
return make_var_pack(r);
}
r = process_result_set(wrapper,res_set);
return make_var_pack(r);
#endif
}
