/***********************************************************************
*
*   案例三、驗證 DDL和DML中的insert, update, delete語句的使用
*
*   拓展：1、當插入的值中有單引號該如何處理
*         2、如何獲得更新、刪除影響的記錄數
*
*
************************************************************************/

#pragma comment(lib, "ntwdblib.lib")   //導入非標準的庫函數文件
#define DBNTWIN32
#include <stdio.h>
#include <windows.h>
#include <sqlfront.h>
#include <sqldb.h>

PDBPROCESS dbconnect(const char *user, const char *password, const char *dbserver);
// Forward declarations of the error handler and message handler. 
int err_handler(PDBPROCESS, INT, INT, INT, LPCSTR, LPCSTR);
int msg_handler(PDBPROCESS, DBINT, INT, INT, LPCSTR, LPCSTR,
                LPCSTR, DBUSMALLINT);

int main()
{
    PDBPROCESS  dbproc;    // 與SQL Server的連接結構體指針. 
	int retcode;           // 返回代碼
    char id[10] = {0};
	char name[20]={0};
	char address[200] = {0};
    char sql[1000] = {0};
    int i;

	// 注冊錯誤和消息處理回調函數.
    dberrhandle (err_handler);
    dbmsghandle (msg_handler);

    dbproc = dbconnect("sa", "", "127.0.0.1");
	if(dbproc == NULL)
	{
        printf ("連接數據庫失敗\n");
		return 0;
	}

	dbcmd(dbproc, "drop table pubs..student1");
	retcode = dbsqlexec (dbproc);
	if (retcode == SUCCEED)
    {
        printf ("刪除表成功\n");
    }
	else
	{
        printf ("刪除表失敗\n");
	}

//////////////////////
		 dbproc = dbconnect("sa", "", "127.0.0.1");
		if(dbproc == NULL)
		{
		    printf ("連接數據庫失敗\n");
			return 0;
		}
		////////////////////////////



    // 創建表student. 
    dbcmd (dbproc, "create table pubs..student1(ID numeric(3) primary key, name char(8) not null, address varchar(300))");

    retcode = dbsqlexec (dbproc);   // 發送命令到SQL Server然后開始執行. 
    if (retcode == SUCCEED)
    {
        printf ("創建表成功\n");
    }
	else
	{
        printf ("創建表失敗\n");
	}

	//插入數據
	
	for(i=0;i<5;i++)
	{
		//////////////////////
		 dbproc = dbconnect("sa", "", "127.0.0.1");
		if(dbproc == NULL)
		{
		    printf ("連接數據庫失敗\n");
			return 0;
		}
		////////////////////////////
		scanf("%s%s%s", id,name,address);
		sprintf(sql,"insert into pubs..student1 values(%s, '%s','%s')", id, name,address);
		printf("%s\n",sql);

		dbcmd(dbproc, sql);
    retcode = dbsqlexec (dbproc);
    if (retcode == SUCCEED)
    {
        printf ("插入數據成功\n");
    }
	else
	{
        printf ("插入數據失敗\n");
	}
	}

	//更新數據
	dbproc = dbconnect("sa", "", "127.0.0.1");
		if(dbproc == NULL)
		{
		    printf ("連接數據庫失敗\n");
			return 0;
		}
	dbcmd(dbproc, "update pubs..student1 set name='wang123'");
    retcode = dbsqlexec (dbproc);
    if (retcode == SUCCEED)
    {
        printf ("更新數據成功\n");
    }
	else
	{
        printf ("更新數據失敗\n");
	}

	//刪除數據
	dbproc = dbconnect("sa", "", "127.0.0.1");
		if(dbproc == NULL)
		{
		    printf ("連接數據庫失敗\n");
			return 0;
		}
	dbcmd(dbproc, "delete pubs..student1 where name='wang123'");
    retcode = dbsqlexec (dbproc);
    if (retcode == SUCCEED)
    {
        printf ("刪除數據成功\n");
    }
	else
	{
        printf ("刪除數據失敗\n");
	}

    dbexit ();    // 關閉數據庫連接. 
    return (0);
}

PDBPROCESS dbconnect(const char *user, const char *password, const char *dbserver)
{
    PDBPROCESS  dbproc;    // 與SQL Server的連接結構體指針. 
    PLOGINREC   login;     // 登錄信息。 

    dbinit();              // 初始化 DB-Library.

    login = dblogin ();                   //獲得LOGINREC.
    DBSETLUSER (login, user);             //設置數據庫登錄用戶
    DBSETLPWD (login, password);          //設置數據庫登錄用戶密碼
	
    dbproc = dbopen (login, dbserver);  //連接數據庫
	return dbproc;
}

int err_handler (PDBPROCESS dbproc, INT severity,
    INT dberr, INT oserr, LPCSTR dberrstr, LPCSTR oserrstr)
{
    printf ("DB-Library 錯誤 %i: %s\n", dberr, dberrstr);
    if (oserr != DBNOERR)
    {
        printf ("操作系統錯誤%i: %s\n", oserr, oserrstr);
    }
    return (INT_CANCEL);
}

int msg_handler (PDBPROCESS dbproc, DBINT msgno, INT msgstate,
    INT severity, LPCSTR msgtext, LPCSTR server,
    LPCSTR procedure, DBUSMALLINT line)
{
    printf ("SQL Server 消息 %ld: %s\n", msgno, msgtext);
    return (0);
}