#include <Core/Core.h>
#include <plugin/sqlite3/Sqlite3.h>

using namespace Upp;

CONSOLE_APP_MAIN
{
	Cout() << "Start" << EOL;
	
	Sqlite3Session myDB;	// <<<<<<<<<<<<<< ADOPT TO YOUR MS_Sql_Server.....
	if(!myDB.Open(ConfigFile("DATA.db"))) {
		LOG("Can't create or open database file\n");
		return;
	}
#ifdef _DEBUG
	myDB.SetTrace();
#endif

	SQL = myDB;	
	// ======================= initial data
	String s = "[{\"A\":\"valA1\", \"B\":\"valB1\"}, {\"A\":\"valA2\", \"B\":\"valB2\"}]";
	ValueMap js=ParseJSON(s);   // frankly speaking ValueArray should be perhaps, but it works for me...
	int jsz=js.GetCount();
	
	// ======================= create tbl
	SQL.Execute("drop table IF EXISTS tbl;");
	SQL.Execute("create table IF NOT EXISTS tbl (A STRING , B STRING, PRIMARY KEY (A));");
	
	// ======================= insert into tbl
	Sql query(myDB);		// (my_db)
	query.SetStatement("INSERT INTO tbl ( A, B) VALUES ( ?, ?) ");
	// initialize transaction
	query.Begin();
        // loop to insert rows
	for(size_t i = 0; i < jsz; i++) {			
	   	query.SetParam(0,~js[i]["A"]);
	   	query.SetParam(1,~js[i]["B"]);	   	
		query.Execute();			
	}
	// error behavior
	if(query.WasError()){
		Cout() << query.GetLastError();
		query.Rollback();
		return;		// false;
	}
	//Save data & unlock db
	query.Commit();
	Cout() << "Done" << EOL;
	
	// ======================= select to console_view	
	if (query.Execute("SELECT * FROM tbl"))
	{
		Cout() << "Call succeeded" << EOL;
		
		for (int i = 0; i < query.GetColumnCount(); ++i) {
			Cout() << Format("%d: %s", i, query.GetColumnInfo(i).name) << EOL;
		}
		
		while (query.Fetch()) {
			Cout() << query[0] << " ; " << query[1] << EOL;
		}
	}
	else
	{
		Cout() << "Call failed" << EOL;
		Cout() << SQL.GetLastError() << EOL;
	}
}