my_mysql.c

这是在linux下利用GPS MODERM通过串口进行短信发送的源代码

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/time.h>
#include <mysql/mysql.h>
#include <mysql/errmsg.h>
#include "my_db.h"

MYSQL my_connection;  /* 用于mysql的连接 */
MYSQL_RES *res_ptr;   /* mysql查询的返回结果值 */
MYSQL_ROW sql_row;    /* mysql结果的行数组 */

unsigned long *field_len; /* 行中字段长度的数组 */
char logtmp[100];

/* begin e_log.o 的函数 */
void e_log( char *logstr )
{
    FILE *lfp;
    time_t timep;
    
    char filename[30];
    memset( filename,0,sizeof(filename) );
    strcpy( filename, getenv("HOME") );
    strcat( filename,"/log/elog" );

    time( &timep );
    
    lfp = fopen( filename, "a+" );
    if( lfp == NULL )
    {
        return;
    }
    fprintf( lfp, "//---------%s%s---------//\n", ctime( &timep), logstr );
    fclose( lfp );
    return;
}


void my_sql_execut( struct my_sql *sqlstring )
{
  int result;
  int error_no;
  
  int error_flag = 0;
  unsigned long result_lines;
  int need_lines;
  int return_lines;
  unsigned long next_line_number;
  int field_number;
  char field_dec;
  char line_dec;

  int postion = 0;
  int i,j; /* 循环时用来计数,没有特别的要求 */
  
  /* 连接数据库服务器 ,得到连接数组 my_connection */
  if( strlen( sqlstring->sql_str ) == 0 )
  {
      memset( logtmp, 0x0, 100 );
      sprintf( logtmp, "%d:Sql is empty \n",sqlstring->source );
      e_log( logtmp );
      return;
  }
//  e_log("开始MYSQL调用");
  mysql_init( &my_connection );
  mysql_real_connect( &my_connection, "localhost", "inspector",
			  "audit", "remit", 0, NULL, 0 );
  if( ( error_no = mysql_errno( &my_connection )) != 0 )
  {
    switch(error_no){
      case CR_CONNECTION_ERROR :
          memset( logtmp, 0x0, 100 );
	      sprintf( logtmp, "%s\n", "不能连接本地MySQL服务器." );
          e_log( logtmp );
	      break;
      case CR_OUT_OF_MEMORY :
          memset( logtmp, 0x0, 100 );
	      sprintf( logtmp, "%s\n", "内存溢出." );
          e_log( logtmp );
      	      break;
      default :
          memset( logtmp, 0x0, 100 );
	      sprintf( logtmp, "%d:%s\nsql:%s", error_no,"连接错误.", mysql_stat( &my_connection ) );
          e_log( logtmp );
    }
    e_log( sqlstring->sql_str );
    error_flag=1;
    sqlstring->error_flag = error_flag;
    mysql_close( &my_connection );
//   e_log("结束MYSQL调用");
    return;
  }
		  
  /* 利用my_connection,执行sql语句 */
  result = mysql_query( &my_connection, sqlstring->sql_str );
  if( result != 0 ){
    switch( error_no = mysql_errno( &my_connection ) ){
      case CR_COMMANDS_OUT_OF_SYNC :
          memset( logtmp, 0x0, 100 ); 
	      sprintf( logtmp, "%s\n", "命令以一个不适当的次序被执行." );
          e_log( logtmp );
	      break;
      case CR_SERVER_GONE_ERROR :
          memset( logtmp, 0x0, 100 ); 
	      sprintf( logtmp, "%s\n", "MySQL服务器关闭了." );
          e_log( logtmp );
	      break;
      case CR_SERVER_LOST:
          memset( logtmp, 0x0, 100 ); 
	      sprintf( logtmp, "%s\n", "到服务器的连接在查询期间失去." );
          e_log( logtmp );
	      break;
      case CR_UNKNOWN_ERROR :
          memset( logtmp, 0x0, 100 ); 
	      sprintf( logtmp, "%s\n", "发生一个未知的错误." );
          e_log( logtmp );
	      break;
     default:
          memset( logtmp, 0x0, 100 ); 
	      sprintf( logtmp, "%s\n", "ERROR UNKNOW,Maybe sql is wrong" );
          e_log( logtmp );
    }
    e_log( sqlstring->sql_str );
    //error_flag = error_no;
    error_flag = 1;
  }

  sqlstring->error_flag = error_flag;

  /* 1,没有返回值，程序结束 */
  if( sqlstring->sql_back == 0 )
  {
      /* 对于insert语句，得到id号 */
      if( strstr( sqlstring->sql_str, "insert" ) != NULL ||
              strstr( sqlstring->sql_str, "INSERT" ) != NULL )
      {
        /*
        result = mysql_query( &my_connection, "SELECT LAST_INSERT_ID()" );
        res_ptr = mysql_store_result( &my_connection );
     	sql_row = mysql_fetch_row( res_ptr );
        sqlstring->last_recode_id = atol(sql_row[i]);
        mysql_free_result( res_ptr);
        */
        sqlstring->last_recode_id = mysql_insert_id( &my_connection );
      }
	  mysql_close( &my_connection );
//      e_log("结束MYSQL调用");
	  /*
	  printf(" myqsl connection close normally\n");
	  fflush(stdout );
          */
	  return;
  }
  /* 2,有返回值，判断查询结果，如果不成功，返回 */
  else if ( sqlstring->error_flag != 0 )
  {
	  mysql_close( &my_connection );
//      e_log("结束MYSQL调用");
	  return;
  }
  /* 3,读取查询结果，按要求将结果放到字符串中 */
  else
  {
    need_lines = sqlstring->need_lines;
    next_line_number=sqlstring->next_line_number;
    
    res_ptr = mysql_store_result( &my_connection );
    if( res_ptr == NULL )
    {
	    mysql_free_result( res_ptr );
	    mysql_close( &my_connection );
//        e_log("结束MYSQL调用");
	    return;
    }
    if( !mysql_field_count( &my_connection ) )
    {
	    mysql_free_result( res_ptr );
	    mysql_close( &my_connection );
//        e_log("结束MYSQL调用");
    	return;
    }
    result_lines = mysql_num_rows( res_ptr );

    if( result_lines == 0 )
    {
	    sqlstring->result_lines = 0;
	    mysql_free_result( res_ptr );
	    mysql_close( &my_connection );
//        e_log("结束MYSQL调用");
	    return;
    }
    if( mysql_errno( &my_connection ) )
    {
        e_log( sqlstring->sql_str );
        memset( logtmp, 0x0, 100 ); 
	    sprintf( logtmp, "%s\n", 
			    "结果集合太大,数据不能被读取,查询没有返回数据." );
        e_log( logtmp );
	    
        sqlstring->error_flag = 1;
	    mysql_free_result( res_ptr );
	    mysql_close( &my_connection );
//        e_log("结束MYSQL调用");
	    return;
    }

    /* 计算实际要返回的行数 return_lines */
    if( result_lines == next_line_number )
    {
	//  fprintf( stderr, "%s\n", "没有数据可以读取." );
	    sqlstring->error_flag = 2;
	    mysql_free_result( res_ptr );
	    mysql_close( &my_connection );
//        e_log("结束MYSQL调用");
	    return;
    }
    if( result_lines > need_lines + next_line_number ) 
	    return_lines = need_lines;
    else
	    return_lines = result_lines - next_line_number;
    
    sqlstring->result_lines = result_lines;
    sqlstring->return_lines = return_lines;
    sqlstring->next_line_number = next_line_number + return_lines;

    /* 得到字段分割符，行分割符，字段数 */
    field_number = mysql_field_count( &my_connection );
    if( sqlstring->field_dec == ' ' || sqlstring->field_dec == '\0'
		    || sqlstring->field_dec == 0x0 )
    {
	    field_dec = '|';
    	    sqlstring->field_dec = field_dec; 
    }else field_dec = sqlstring->field_dec;
    
    if( sqlstring->line_dec == ' ' || sqlstring->line_dec == '\0'
		    || sqlstring->line_dec == 0x0 )
    {
	    line_dec= ';';
	    sqlstring->line_dec = line_dec;
    }else line_dec = sqlstring->line_dec;
  

    /* 设置返回字符串，及其他相关参数 */
    mysql_data_seek( res_ptr, next_line_number );
    for( j=0; j < return_lines; j++ )
    {
     	sql_row = mysql_fetch_row( res_ptr );
	    if( sql_row == NULL )
	    {
	    	sqlstring->error_flag = 1;
	    	mysql_free_result( res_ptr );
	    	mysql_close( &my_connection );
//            e_log("结束MYSQL调用");
		    return;
	    }
    
	    field_len = mysql_fetch_lengths( res_ptr );
	    for( i=0; i < field_number; i++ )
	    {
		   strcat( sqlstring->result_str, sql_row[i] );
		   postion = postion + field_len[i];
		   strncat( sqlstring->result_str, &field_dec, 1 );
		   postion++;
	    }
	    strncat( sqlstring->result_str, &line_dec, 1 );
	    postion++;
    }
    if( error_no = mysql_errno( &my_connection ) )
    {
	    if( error_no == CR_SERVER_LOST )
        {
            memset( logtmp, 0x0, 100 ); 
		    sprintf( logtmp, "%s\n", "对服务器的连接在查询期间失去." );
            e_log( logtmp );
        }
	    else
        {
            memset( logtmp, 0x0, 100 ); 
	    	sprintf( logtmp, "%s\n", "发生一个未知的错误." );
            e_log( logtmp );
        }
	    sqlstring->error_flag = 1;
	    mysql_free_result( res_ptr );
	    mysql_close( &my_connection );
//        e_log("结束MYSQL调用");
	    return;
    }
    //fprintf( stderr, "return string lengths is %d\n", postion );
    sqlstring->error_flag = 0;
    mysql_free_result( res_ptr );
    mysql_close( &my_connection );
//    e_log("结束MYSQL调用");
    return;
  }
}

/* 将mysql的初始数值赋予结构体my_sql */
void init_sql( struct my_sql *sql,char *sql_str,int sql_back, 
		int need_lines, unsigned long packet_no )
{
	memset( sql, 0x0, sizeof( struct my_sql ) );

	sprintf( sql->sql_str, "%s", sql_str);
	sql->sql_back = sql_back;
	sql->need_lines = need_lines;
	sql->line_dec = ';';
	sql->field_dec = '|';	
	sql->next_line_number = 0;
	sql->packet_no = packet_no;
    sql->last_recode_id = 0;
    sql->source = (int)packet_no;

	return;
}
/* 显示my_sql结构体中的数据库操作结果，用于调试用 */
out_put(struct my_sql *sql)
{
	printf("error_flag is %d\n",sql->error_flag);
	printf("result_lines is %d\n",sql->result_lines);
	printf("return_lines is %d\n",sql->return_lines);
	printf("next_line_number is %d\n",sql->next_line_number);
	printf("%d:result_str is %s\n",strlen(sql->result_str),
			sql->result_str);
	printf("sql is %s\n",sql->sql_str);
    printf("last_recode_id is %d\n", sql->last_recode_id );
	//fflush(stdout);
	return;
}
/* my_mysql.o的函数体到此结束 */
/*
main()
{
	struct my_sql select_sql,insert_sql;
    char sql[1000];
	memset(&insert_sql,0x0,sizeof(struct my_sql));
	sprintf(insert_sql.sql_str,"%s",
			"insert into czytb values('3140002','01','1')");
//	init_sql(&insert_sql,0);
//	my_sql_execut( &insert_sql );
//	out_put( &insert_sql);

	memset(&select_sql,0x0,sizeof(struct my_sql));
	sprintf(sql,"%s","select IFNULL(0,max(xh)) as mxh from tmp_data_tb where do_flag = 1 and data_kind = 1");
	init_sql(&select_sql,sql,1,50,0);
	my_sql_execut( &select_sql);
	out_put( &select_sql);
}
*/