Retrieving a single value from MySQL

New comment
 

I am struggling with retrieving a single data value from MySQL. There are quite a few samples around both for MT4 and MT5. I have tried a few of them and they work.

But they all retrieve complete rows of data from MySQL. I want to retrieve a single values with qualified SQL-statements. Something like "Select Level, from Table1 where TradeID = 'zzzz'";

Spent hours searching the web.

I have the code below and it works but there is an issue with it.

The result of the query should be: 85.09. The actual result is:

2011.09.17 22:40:59 testmysql USDCHF,H1: row: ÀGÆG85.09

Remove garbage in front with Substr:

2011.09.17 22:40:59 testmysql USDCHF,H1: row2: |85.09|

Why is it garbage in front of the data? Tested other fields and they all contain garbage in front. Doing a retrieval with the

Command Line Client gives the correct value so the data in the database is correct



Code below.


#import "libmysql.dll"
int mysql_init(int db);
int mysql_errno(int TMYSQL);
int mysql_real_connect( int TMYSQL,string host,string user,string password, string DB,int port,int socket,int clientflag);
int mysql_real_query(int TMSQL,string query,int length);

void mysql_close(int TMSQL);

int mysql_store_result(int TMSQL);
string mysql_fetch_row(int result);
int mysql_num_rows(int result);
void mysql_free_result(int result);
int mysql_num_fields(int result);
#import

int mysql;

string query;


//+------------------------------------------------------------------+
//| expert initialization function |
//+------------------------------------------------------------------+
int init()
{
string row;
connect();

query = "Select KillLevel from FSTTrade Where ForexPair = 'CADJPY' AND TradeStatus = 'Pending Setup'";

int length=StringLen(query);
mysql_real_query(mysql,query,length);

Print("Query sent");
int result = mysql_store_result(mysql);

Print("Query stored");

Print("result: " +result);
if(result==0)
{
Print("result of query stored = 0!!");
return(0);
}

int numOfRows = mysql_num_rows(result);

Print("NumRows: " + numOfRows);
Print("Numfields: " + mysql_num_fields(result));

row = mysql_fetch_row(result);

Print("row: " + row);

Print("row2: |" + StringSubstr(row,8) + "|");

mysql_free_result(result);
return(0);
}
//+------------------------------------------------------------------+
//| expert deinitialization function |
//+------------------------------------------------------------------+
int deinit()
{
mysql_close(mysql);
return(0);
}
//+------------------------------------------------------------------+
//| expert start function |
//+------------------------------------------------------------------+
int start()
{
//----

//----
return(0);
}
//+------------------------------------------------------------------+

void connect() {

mysql = mysql_init(mysql);
if (mysql!=0) Print("allocated OK");

string host="localhost";
string user="FSTUser";
string password="fstuserpw";
string DB="ForexTrade";
int clientflag=0;
int port=3306;
string socket="";
int res=mysql_real_connect(mysql,host,user,password,DB,port,socket,clientflag);
int err=GetLastError();
if (res==mysql) Print("connected OK");
else Print("error=",mysql," ",mysql_errno(mysql)," ");

}


complete output:

2011.09.17 22:40:59 testmysql USDCHF,H1: row2: |85.09|
2011.09.17 22:40:59 testmysql USDCHF,H1: row: ÀGÆG85.09
2011.09.17 22:40:59 testmysql USDCHF,H1: Numfields: 1
2011.09.17 22:40:59 testmysql USDCHF,H1: NumRows: 1
2011.09.17 22:40:59 testmysql USDCHF,H1: result: 52135800
2011.09.17 22:40:59 testmysql USDCHF,H1: Query stored
2011.09.17 22:40:59 testmysql USDCHF,H1: Query sent
2011.09.17 22:40:59 testmysql USDCHF,H1: connected OK
2011.09.17 22:40:59 testmysql USDCHF,H1: allocated OK
2011.09.17 22:40:59 testmysql USDCHF,H1: loaded successfully

 

Please use this to post code . . . it makes it easier to read.

 
RaptorUK:

Please use this to post code . . . it makes it easier to read.

RaptoriUK. OK
#import "libmysql.dll"
int mysql_init(int db);
int mysql_errno(int TMYSQL);
int mysql_real_connect( int TMYSQL,string host,string user,string password, string DB,int port,int socket,int clientflag);
int mysql_real_query(int TMSQL,string query,int length);

void mysql_close(int TMSQL);

int mysql_store_result(int TMSQL);
string mysql_fetch_row(int result);
int mysql_num_rows(int result);
void mysql_free_result(int result);
int mysql_num_fields(int result);
#import

int mysql;

string query;


//+------------------------------------------------------------------+
//| expert initialization function                                   |
//+------------------------------------------------------------------+
int init()
  {
   string row;
   connect();
  
   query = "Select KillLevel from FSTTrade Where ForexPair = 'CADJPY' AND TradeStatus = 'Pending Setup'"; 
   
   int length=StringLen(query);
   mysql_real_query(mysql,query,length);
   
   Print("Query sent");
   int result = mysql_store_result(mysql);
   
   Print("Query stored");
   
   Print("result:  " +result);
   if(result==0)
    {
     Print("result of query stored = 0!!");
     return(0);
    }
    
   int numOfRows = mysql_num_rows(result);
   
   Print("NumRows:  " + numOfRows);
   Print("Numfields:  " + mysql_num_fields(result));
   
   row = mysql_fetch_row(result);
   
   Print("row:  " + row);
   
   Print("row2:  |" + StringSubstr(row,8) + "|");
  
    mysql_free_result(result);
   return(0);
  }
//+------------------------------------------------------------------+
//| expert deinitialization function                                 |
//+------------------------------------------------------------------+
int deinit()
  {
   mysql_close(mysql);
   return(0);
  }
//+------------------------------------------------------------------+
//| expert start function                                            |
//+------------------------------------------------------------------+
int start()
  {
//----
   
//----
   return(0);
  }
//+------------------------------------------------------------------+

void connect() {

   mysql = mysql_init(mysql);
   if (mysql!=0) Print("allocated OK");
   
   string host="localhost";
   string user="FSTUser";
   string password="fstuserpw";
   string DB="ForexTrade";
   int clientflag=0;
   int port=3306;
   string socket="";
   int res=mysql_real_connect(mysql,host,user,password,DB,port,socket,clientflag);
   int err=GetLastError();
   if (res==mysql) Print("connected OK");
   else Print("error=",mysql," ",mysql_errno(mysql)," ");

}
New comment
Reason: