module mysqlpltest;

 //
 // mysqlpltest.d
 //
 // Copyright (c) 2007 Martin Fuchs <martin-fuchs@gmx.net>
 //

 /// \file mysqlpltest.d
 /// MySQLPL/D example file


/*

  All rights reserved.

  Redistribution and use in source and binary forms, with or without
  modification, are permitted provided that the following conditions are met:

  * Redistributions of source code must retain the above copyright
	notice, this list of conditions and the following disclaimer.
  * Redistributions in binary form must reproduce the above copyright
	notice, this list of conditions and the following disclaimer in
	the documentation and/or other materials provided with the
	distribution.

  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
  AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
  ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
  LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
  CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
  SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
  INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
  CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
  ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
  POSSIBILITY OF SUCH DAMAGE.

*/


import xmlstorage;
import mysqlpl;

import std.stdio;
import std.string;	// format()
import std.cstream; // dout


int main(char[][] args)
{
	writefln();	// insert an empty line after unit test output


	try {
		MSqlEnv env = new MSqlEnv();

		MSqlConnection connect = new MSqlConnection(env, "", "", "test");

		connect.print_warnings(dout);


		SqlStatement stmt = new SqlStatement(connect);

		 // insert a new row into inno1
		stmt.execute_fetch("select max(id)+1 from inno1");

		int id = stmt().get_int(0);

		stmt.prepare("insert into inno1(id) values(?)");
		stmt.bind_param(0, &id);//@@stmt.bind_param(0, id);
		stmt.execute();

		stmt.execute("select * from inno1");	// limit 10
		stmt.print_results(dout);

		 // rollback to remove the new row again
		connect.rollback();	// nur wirksam bei Tabellen mit Transaction-Support (-> InnoDB)

		dout.flush();


		stmt.execute("select * from test3");
		//while(stmt.fetch()) dout.writeString(stmt().get_double(3) ~ '\n');
		stmt.print_results(dout);


		stmt.execute_fetch("select max(id)+1 from test_types");
		id = stmt().get_int(0);

		stmt.prepare("insert into test_types(id, blob_val) values(?, ?)");

	  version(MYSQLPL_PREP_STMT) {
		MSqlBlob blob = new MSqlBlob("1234567890".ptr, 10);
		blob.ind().set();

		stmt.bind_param(0, &id);
		stmt.bind_param(1, blob);

		stmt.execute();
	  }

		stmt.execute("select * from test_types");
		stmt.print_results(dout);
		dout.flush();

		connect.rollback();	// nur wirksam bei Tabellen mit Transaction-Support (-> InnoDB)

		stmt.execute("select * from test_types");
		stmt.print_results(dout);
		dout.flush();


		stmt.execute("delete from a where 1=2;");

		connect.print_warnings(dout);


		stmt.prepare("select * from B");
		stmt.execute();

		while(stmt.fetch()) {
			dout.writeString(format("%f - %f\n", stmt().get_double(0), stmt().get_double(1)));
		}

		dout.flush();

  version(MYSQLPL_PREP_STMT) {
		stmt.prepare("select ? as p1, ? as p2, ? as p3, ? as p4, PI(), ? as str1, 'Hallo?' as str2");

		SqlString para2 = new SqlString("Test123");
		SqlNumber para3 = new SqlNumber(3.141592654);
		double para4 = 5.25;

		int idx = 0;
		int int_const = 123;
		stmt.bind_param(idx++, &int_const);
		stmt.bind_param(idx++, para2);
		stmt.bind_param(idx++, para3);
		stmt.bind_param(idx++, &para4);
		stmt.bind_param(idx++, "Sophie's Welt \"Wunderland\"");

		stmt.execute();

		stmt.print_results(dout);

		dout.flush();

		double x = para3.get_double();
		dout.writeString(format("\nx = %f \n", x));
  } else {
		stmt.prepare("select ? as p1, ? as p4, PI(), ? as str1, 'Hallo?' as str2");

		double para4 = 5.25;

		int idx = 0;
		int int_const = 123;
		stmt.bind_param(idx++, &int_const);
		stmt.bind_param(idx++, &para4);
		stmt.bind_param(idx++, "Sophie's Welt \"Wunderland\"");

		stmt.execute();

		stmt.print_results(dout);
  }


		stmt.execute_fetch("select 1234567890123456789");
		long big_num = stmt().get_int64(0);
		char buffer[40];
		dout.writeString(format("\nBig number: %d\n\n", big_num));


		stmt.print_results("select NR_A, NR_B, 1/NR_B from B", dout);

		dout.writeString("\n\nAnalysts:\n\n");
		stmt.print_results("select * from EMP where JOB = 'ANALYST'", dout, false);


  version(MYSQLPL_PREP_STMT) {
		SqlNumber pi = new SqlNumber("3.14159");
		dout.writeString(pi.str() ~ '\n');

  version(MYSQLPL_PREP_STMT) {	//@@
		SqlDate date = new SqlDate("1.1.2005");
		dout.writeString(date.str() ~ '\n');
  }
  }


  version(XMLSTORAGE) {
		XMLStorage.XMLDoc xml_doc;
		XMLStorage.XMLPos pos = new XMLStorage.XMLPos(&xml_doc);

		pos.create("Resultset");
		stmt.dump_results("select * from EMP", pos, "EMP");
		pos.back();

		dout ~ "\n\n";
		xml_doc.write(dout);
		dout ~ "\n\n";
  }


		stmt.execute();

		while(stmt.fetch()) {
			int empno = stmt().get_int("EMPNO");
			double d = stmt().get_double(0);

			dout.writeString(format("%d",empno) ~ " - " ~
					stmt().get_string("ENAME") ~ " - " ~
					stmt().get_string("JOB") ~ '\n');
		}


		dout.write('\n');
	} catch(Object o) {
		dout.flush();
		derr.writeString(o.toString());
		return 1;
	}

	return 0;
}