#include <string.h>
#include <stdio.h>
#include <cbinter.h>

struct item
{   char descr[26];
	char price[6];
} tabdata[] =   {       "CINNAMON",     "1.00",
						"SAGE",         ".95",
						"BASIL",        ".89",
						"OREGANO",      ".89",
						"SAFFRON",      "3.00",
						"GINSENG",      "4.95",
						"",             ""
				};

static char note[] = "csample.txt";
static char dbnam[] = "SYSADM/SYSADM";
static char crtitem[] = "CREATE TABLE ITEM (ITEM_NO NUMBER NOT NULL, DESCRIPTION CHAR(25), PRICE DECIMAL(5,2), NOTES LONG VARCHAR)";

char notebuf[200];
char itembuf[26];
char pricebuf[25];
unsigned int itemno;

SqlManager mgr;
SqlHandle h1, h2;

void failure(char*), itemins(), priceupd(), clrbuf(char*, int);

main()
{
	puts("SqlManager test start...");

	mgr = sql_init(dbnam);

	if(sql_perform(mgr, crtitem) == FALSE)
		failure("cannot create table");
	if(sql_commit(mgr) == FALSE)
		failure("cannot commit after create");
	itemins();
	if(sql_commit(mgr) == FALSE)
		failure("cannot commit after insert");
	priceupd();
	if(sql_commit(mgr) == FALSE)
		failure("cannot commit after price update");

	sql_terminate(mgr);

	puts("Test end.");
}

void itemins()
{
	unsigned int maxitem = 50;
	FILE* fp;
	struct item* datap = &tabdata[0];
	char dum;

	if((fp = fopen(note, "r")) == (FILE* )NULL)
		failure("cannot open data file.");

	clrbuf(itembuf, 26);
	clrbuf(pricebuf, 25);
	clrbuf(notebuf, 200);

	if(!(h1 = sql_open(mgr, "INSERT INTO ITEM VALUES( :UINT, :STRING, :STRING, :LSTRING )", &maxitem, itembuf, pricebuf, notebuf)))
		failure("cannot open insert statement.");
	while(*(datap->descr) != '\0')
	{
		maxitem++;
		strcpy(itembuf, datap->descr);
		strcpy(pricebuf, datap->price);
		if(fgets(notebuf, 200, fp) == (char* )NULL)
		{   puts("not enough items in sample.txt file - quiting input.");
			break;
		}
		printf("NOTE# %d %s\n", maxitem, notebuf);
		if(sql_execute(mgr, h1) == FALSE)
			failure("insert execute failed.");
		datap++;
	}
	if(sql_close(mgr, &h1) == FALSE)
		failure("close insert failed.");
	fclose(fp);
	puts("all items inserted.");
}

#define CB_STATUS_EOF 1

void priceupd()
{
	int len;
	char newprice[10];
	char line[80];

	clrbuf(itembuf, 26);
	clrbuf(pricebuf, 25);
	clrbuf(notebuf, 200);
	clrbuf(newprice, 10);

	if(!(h1 = sql_open(mgr, "SELECT * AS :UINT, :STRING, :STRING, :LSTRING FROM ITEM", &itemno, itembuf, pricebuf, notebuf)))
		failure("cannot open select statement.");
	if(!(h2 = sql_open(mgr, "UPDATE ITEM SET PRICE = :STRING WHERE ITEM_NO = :UINT", newprice, &itemno)))
		failure("cannot open update statement.");
	if(sql_execute(mgr, h1) == FALSE)
		failure("select execute failed.");
	while(sql_fetch(mgr, h1) == TRUE)
	{   printf("\n%d %s %s\n%s\n\n", itemno, itembuf, pricebuf, notebuf);
		for(;;)
		{   printf("Enter new price for %s or . for no price change: ", itembuf);
			fflush(stdout);
			fgets(newprice, 10, stdin);
			if(*newprice == '.')
				break;
			*(newprice + strlen(newprice) - 1) = '\0';
			printf("\nnew price = %s\n", newprice);
			if(sql_execute(mgr, h2) == FALSE)
				failure("update execute failed.");
		}
	}
	if(sql_status(mgr, h1) != CB_STATUS_EOF)
		failure("fetch data failed.");

	if(sql_close(mgr, &h1) == FALSE || sql_close(mgr, &h2) == FALSE)
		failure("close select or update failed.");
	else
		puts("all items updated.");
}

void failure(char* msg)
{
	printf("Test error: %s\ntrying cleanup....\n", msg);
	if((h1 && sql_close(mgr, &h1) == FALSE) || (h2 && sql_close(mgr, &h2) == FALSE))
		puts("cannot close all cursors.");
	if(sql_perform(mgr, "DROP TABLE ITEM") == TRUE && sql_commit(mgr) == TRUE)
		puts("cleanup successful.");
	else
		puts("cleanup failed.");
	sql_terminate(mgr);
	puts("\nexiting.");
	exit(1);
}

void clrbuf(char* buf, int sz)
{
	int i;

	for(i = 0; i < (sz - 1); i++)
		*(buf + i) = ' ';
	*(buf + sz - 1) = '\0';
}