ubuntu笔记7_用ccode::blocks编写mysql程序

1)利用mysql_use_result提取查询结果

     //进行数据查询
    res=mysql_query(my_connection,"SELECT LAST_INSERT_ID()");
    if(res)
        cout<<"SELECT LAST INSERT failed"<<endl;
    else{
        //提取查询结果，范围查询结果的指针
        res_ptr=mysql_use_result(my_connection);
        if(res_ptr){
            while((sqlrow=mysql_fetch_row(res_ptr)))
            //将查询结果保存到xMYSQL_ROW这个行结构中
                cout<<"We inserted childno "<<sqlrow[0]<<endl;
            }
        mysql_free_result(res_ptr);
        }
 

2)利用mysql_use_result提取查询结果

 res=mysql_query(my_connection,"SELECT childnu,fname,age FROM children");
    if(res)
        cout<<"SELECT LAST INSERT failed"<<endl;
    else{
        //获取指向查询结果的指针传给res_ptr
        res_ptr=mysql_store_result(my_connection);
        if(res_ptr) 
            //获取查询结果集的行数
            cout<<"Retrieved "<<(long)mysql_num_rows(res_ptr)<<" rows"<<endl;
            cout<<"children info "<<endl;
            //从地址中逐行提取查询结果
            while((sqlrow=mysql_fetch_row(res_ptr)))
                //cout<<"Fetched data .."<<endl;
                cout<<sqlrow[0]<<" "<<sqlrow[1]<<" "<<sqlrow[2]<<endl;
        }

3)下面的是一个完整的查询并显示的代码

#include <iostream>
#include<stdlib.h>
#include<stdio.h>
#include"mysql.h"

using namespace std;

MYSQL *my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;

void display_header(MYSQL_RES *);
void display_row(MYSQL *,MYSQL_ROW );
int main(int argc,char *argv[])
{
    int res;
    int header=1;
    //初始化
    my_connection=mysql_init(NULL);
    if(my_connection)
        cout<<"init is successed!"<<endl;
        
    //链接数据库
    my_connection=mysql_real_connect(my_connection,
        "localhost","sazabi","12345","sazabi",0,NULL,0);
    cout<<"test1"<<endl;
    if(my_connection)
    {
        cout<<"connection successed!"<<endl;
        
        //执行查找操作
        res=mysql_query(my_connection,
            "SELECT childnu,fname,age FROM children WHERE age>15");
        if(res)
            cout<<"SELECT ERROR:"<<mysql_error(my_connection)<<endl;
        else{
        //提取数据返回结果地址
            res_ptr=mysql_use_result(my_connection);
            
            //逐行将查询结果出入本地变量
            if(res_ptr){
                while((sqlrow=mysql_fetch_row(res_ptr)))
                {
                    if(header)
                    {
                    //输出查询表的信息
                        display_header(res_ptr);
                        header=0;
                    }
                    //逐行输出
                    display_row(my_connection,sqlrow);
                    cout<<endl;
                }
                if(mysql_errno(my_connection)){
                    cout<<"Retrive error"<<mysql_error(my_connection)<<endl;
                }
                //告知数据哭完成操作
                mysql_free_result(res_ptr);
            }


        }
        //关闭链接
        mysql_close(my_connection);

    }
    else{
        cout<<"connection failed!"<<endl;
        if(mysql_errno(my_connection)){
            cout<<"connection error:"<<mysql_errno(my_connection)
                <<" "<<mysql_error(my_connection)<<endl;
        }
    }

    return EXIT_SUCCESS;
}

void display_row(MYSQL *my_connection,MYSQL_ROW sqlrow)
{
    int row_count=0;
    //每行逐列的输出数据，知道列最后
    while(row_count<(long)mysql_field_count(my_connection))
    {
        cout<<sqlrow[row_count]<<" ";
        row_count++;
    }
}

void display_header(MYSQL_RES *res_ptr)
{
    MYSQL_FIELD *field_ptr;

    cout<<"Column details: "<<endl;
    //获取所查表的信息
    while((field_ptr=mysql_fetch_field(res_ptr))!=NULL)
    {
        cout<<"name: "<<field_ptr->name<<endl;
        cout<<"Type: ";
        if(IS_NUM(field_ptr->type))
            cout<<"Numeric field "<<endl;
        else{
            switch(field_ptr->type){
                case FIELD_TYPE_STRING:
                    cout<<"VRCHAR "<<endl;
                    break;
                case FIELD_TYPE_LONG:
                    cout<<"LONG "<<endl;
                    break;
                default:
                    cout<<"Type is "<<field_ptr->type<<"check in mysql_com.h"<<endl;
            }
        }
        cout<<"Max width "<<field_ptr->length<<endl;
        if(field_ptr->flags & AUTO_INCREMENT_FLAG)
            cout<<"AUTO_INCERMENT_FLAG "<<endl;
    }
}