package com.sap.dbmtk.demo;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;

public class NumericRepro
{
	public static void main(String[] args)
	{
		if (args.length != 3)
		{
			System.out.println();
			System.out.println("Usage: user passwd url");
			System.out.println();
			System.out.println("URL example: [jdbc:sybase:Tds:]hostname:port[/dbname]");
			System.out.println();
			System.exit(1);
		}
		String user   = args[0];
		String passwd = args[1];
		String url    = args[2];
		
		if ("null".equalsIgnoreCase(passwd))
			passwd = "";

		if ( ! url.startsWith("jdbc:sybase:Tds:") )
			url = "jdbc:sybase:Tds:" + url;
		
		try
		{
//			Driver driverC = (Driver)Class.forName("com.sybase.jdbc.SybDriver").newInstance();
//			DriverManager.registerDriver(driverC);

			Properties props = new Properties();
			props.put("user",      user);
			props.put("password",  passwd);

			System.out.println("Connectiong to URL: "+url);
			Connection conn = DriverManager.getConnection(url, props);
			
			System.out.println("Using Driver Version: "+conn.getMetaData().getDriverVersion());

			
			/////////////////////////////////////////////////////////////
			Thread.sleep(50);
			try
			{
				System.out.println();
				System.out.println("###############################################################");
				System.out.println("Test: 1 - numeric(8,2) - THAT WORKS");
				System.out.println("###############################################################");
				

				    conn.createStatement().execute("use tempdb");
				try{conn.createStatement().execute("  drop procedure dummy_p1"); } catch (SQLException ignore) {}
//				    conn.createStatement().execute("CREATE PROCEDURE dummy_p1(@p_title VARCHAR(75), @p_type CHAR(75), @p_price INT,          @p_advance INT,          @p_total_sales INT, @p_notes VARCHAR(75), @p_pubdate BIGDATETIME, @p_contract INT, @p_isbn CHAR(75), @p_au_id INT, @p_pub_id INT, @p_picture IMAGE) as select @p_title, @p_type, @p_price, @p_advance, @p_total_sales, @p_notes, @p_pubdate, @p_contract, @p_isbn, @p_au_id, @p_pub_id, @p_picture");
//				    conn.createStatement().execute("CREATE PROCEDURE dummy_p1(@p_title VARCHAR(75), @p_type CHAR(75), @p_price numeric,      @p_advance numeric,      @p_total_sales INT, @p_notes VARCHAR(75), @p_pubdate BIGDATETIME, @p_contract INT, @p_isbn CHAR(75), @p_au_id INT, @p_pub_id INT, @p_picture IMAGE) as select @p_title, @p_type, @p_price, @p_advance, @p_total_sales, @p_notes, @p_pubdate, @p_contract, @p_isbn, @p_au_id, @p_pub_id, @p_picture");
				    conn.createStatement().execute("CREATE PROCEDURE dummy_p1(@p_title VARCHAR(75), @p_type CHAR(75), @p_price numeric(8,2), @p_advance numeric(8,2), @p_total_sales INT, @p_notes VARCHAR(75), @p_pubdate BIGDATETIME, @p_contract INT, @p_isbn CHAR(75), @p_au_id INT, @p_pub_id INT, @p_picture IMAGE) as select @p_title, @p_type, @p_price, @p_advance, @p_total_sales, @p_notes, @p_pubdate, @p_contract, @p_isbn, @p_au_id, @p_pub_id, @p_picture");
			
				CallableStatement stmnt = conn.prepareCall("{call dummy_p1(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
				
				stmnt.setObject    (1,  new String("aaaaaaaaa"),               Types.VARCHAR);  // title
				stmnt.setObject    (2,  new String("bbbbbbbbb"),               Types.VARCHAR);  // type
				stmnt.setObject    (3,  new BigDecimal("1.1"),                 Types.NUMERIC);  // price
//				stmnt.setObject    (4,  null,                                  Types.NUMERIC);  // advance     *** This works (if @p_advance is INT) BUT NOT (if @p_advance is NUMERIC or NUMERIC(8,2)) 
				stmnt.setObject    (4,  new BigDecimal("2.2"),                 Types.NUMERIC);  // advance     *** This always works
//				stmnt.setNull      (4,                                         Types.NUMERIC);  // advance     *** This works (if @p_advance is INT) BUT NOT (if @p_advance is NUMERIC or NUMERIC(8,2))
				stmnt.setObject    (5,  new Integer(11),                       Types.INTEGER);  // total_sales
				stmnt.setObject    (6,  new String("some note"),               Types.VARCHAR);  // notes
				stmnt.setObject    (7,  new Date(System.currentTimeMillis()),  Types.TIMESTAMP);// pubdate
				stmnt.setObject    (8,  new Integer(1),                        Types.INTEGER);  // contract
				stmnt.setObject    (9,  new String("ISBN"),                    Types.VARCHAR);  // ISBN
				stmnt.setObject    (10, new Integer(1),                        Types.INTEGER);  // author ID
				stmnt.setObject    (11, new Integer(1),                        Types.INTEGER);  // pub ID
				stmnt.setObject    (12, null,                                  Types.BLOB);     // picture
				
				ResultSet rs = stmnt.executeQuery();
				int colCount = rs.getMetaData().getColumnCount();
				while (rs.next())
				{
					for (int c=1; c<=colCount; c++)
					{
						String str = rs.getString(c);
						if (str == null)
							str="(NULL)";
						
						System.out.println("COL["+c+"]=|"+str+"|.");
					}
				}
				rs.close();
				stmnt.close();
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}

			

			/////////////////////////////////////////////////////////////
			Thread.sleep(50);
			try
			{
				System.out.println();
				System.out.println("###############################################################");
				System.out.println("Test: 2 - @p_advance INT and Types.INTEGER == WORKS");
				System.out.println("###############################################################");
				

				    conn.createStatement().execute("use tempdb");
				try{conn.createStatement().execute("  drop procedure dummy_p1"); } catch (SQLException ignore) {}
				    conn.createStatement().execute("CREATE PROCEDURE dummy_p1(@p_title VARCHAR(75), @p_type CHAR(75), @p_price INT,          @p_advance INT,          @p_total_sales INT, @p_notes VARCHAR(75), @p_pubdate BIGDATETIME, @p_contract INT, @p_isbn CHAR(75), @p_au_id INT, @p_pub_id INT, @p_picture IMAGE) as select @p_title, @p_type, @p_price, @p_advance, @p_total_sales, @p_notes, @p_pubdate, @p_contract, @p_isbn, @p_au_id, @p_pub_id, @p_picture");
			
				CallableStatement stmnt = conn.prepareCall("{call dummy_p1(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
				
				stmnt.setObject    (1,  new String("aaaaaaaaa"),               Types.VARCHAR);  // title
				stmnt.setObject    (2,  new String("bbbbbbbbb"),               Types.VARCHAR);  // type
				stmnt.setObject    (3,  new BigDecimal("1.1"),                 Types.INTEGER);  // price
				stmnt.setObject    (4,  null,                                  Types.INTEGER);  // advance     *** This works (if @p_advance is INT) BUT NOT (if @p_advance is NUMERIC or NUMERIC(8,2)) 
//				stmnt.setObject    (4,  new BigDecimal("2"),                   Types.INTEGER);  // advance     *** This always works
//				stmnt.setNull      (4,                                         Types.INTEGER);  // advance     *** This works (if @p_advance is INT) BUT NOT (if @p_advance is NUMERIC or NUMERIC(8,2))
				stmnt.setObject    (5,  new Integer(11),                       Types.INTEGER);  // total_sales
				stmnt.setObject    (6,  new String("some note"),               Types.VARCHAR);  // notes
				stmnt.setObject    (7,  new Date(System.currentTimeMillis()),  Types.TIMESTAMP);// pubdate
				stmnt.setObject    (8,  new Integer(1),                        Types.INTEGER);  // contract
				stmnt.setObject    (9,  new String("ISBN"),                    Types.VARCHAR);  // ISBN
				stmnt.setObject    (10, new Integer(1),                        Types.INTEGER);  // author ID
				stmnt.setObject    (11, new Integer(1),                        Types.INTEGER);  // pub ID
				stmnt.setObject    (12, null,                                  Types.BLOB);     // picture
				
				ResultSet rs = stmnt.executeQuery();
				int colCount = rs.getMetaData().getColumnCount();
				while (rs.next())
				{
					for (int c=1; c<=colCount; c++)
					{
						String str = rs.getString(c);
						if (str == null)
							str="(NULL)";
						
						System.out.println("COL["+c+"]=|"+str+"|.");
					}
				}
				rs.close();
				stmnt.close();
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}

			
			
			/////////////////////////////////////////////////////////////
			Thread.sleep(50);
			try
			{
				System.out.println();
				System.out.println("###############################################################");
				System.out.println("Test: 3 -  @p_advance numeric and Types.NUMERIC and NULL    THIS ONE FAILES");
				System.out.println("###############################################################");
				

				    conn.createStatement().execute("use tempdb");
				try{conn.createStatement().execute("  drop procedure dummy_p1"); } catch (SQLException ignore) {}
				    conn.createStatement().execute("CREATE PROCEDURE dummy_p1(@p_title VARCHAR(75), @p_type CHAR(75), @p_price numeric,      @p_advance numeric,      @p_total_sales INT, @p_notes VARCHAR(75), @p_pubdate BIGDATETIME, @p_contract INT, @p_isbn CHAR(75), @p_au_id INT, @p_pub_id INT, @p_picture IMAGE) as select @p_title, @p_type, @p_price, @p_advance, @p_total_sales, @p_notes, @p_pubdate, @p_contract, @p_isbn, @p_au_id, @p_pub_id, @p_picture");
//				    conn.createStatement().execute("CREATE PROCEDURE dummy_p1(@p_title VARCHAR(75), @p_type CHAR(75), @p_price numeric(8,2), @p_advance numeric(8,2), @p_total_sales INT, @p_notes VARCHAR(75), @p_pubdate BIGDATETIME, @p_contract INT, @p_isbn CHAR(75), @p_au_id INT, @p_pub_id INT, @p_picture IMAGE) as select @p_title, @p_type, @p_price, @p_advance, @p_total_sales, @p_notes, @p_pubdate, @p_contract, @p_isbn, @p_au_id, @p_pub_id, @p_picture");

				CallableStatement stmnt = conn.prepareCall("{call dummy_p1(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
				
				stmnt.setObject    (1,  new String("aaaaaaaaa"),               Types.VARCHAR);  // title
				stmnt.setObject    (2,  new String("bbbbbbbbb"),               Types.VARCHAR);  // type
				stmnt.setObject    (3,  new BigDecimal("1.1"),                 Types.NUMERIC);  // price
				stmnt.setObject    (4,  null,                                  Types.NUMERIC);  // advance     *** This works (if @p_advance is INT) BUT NOT (if @p_advance is NUMERIC or NUMERIC(8,2)) 
//				stmnt.setObject    (4,  new BigDecimal("2.2"),                 Types.NUMERIC);  // advance     *** This always works
//				stmnt.setNull      (4,                                         Types.NUMERIC);  // advance     *** This works (if @p_advance is INT) BUT NOT (if @p_advance is NUMERIC or NUMERIC(8,2))
				stmnt.setObject    (5,  new Integer(11),                       Types.INTEGER);  // total_sales
				stmnt.setObject    (6,  new String("some note"),               Types.VARCHAR);  // notes
				stmnt.setObject    (7,  new Date(System.currentTimeMillis()),  Types.TIMESTAMP);// pubdate
				stmnt.setObject    (8,  new Integer(1),                        Types.INTEGER);  // contract
				stmnt.setObject    (9,  new String("ISBN"),                    Types.VARCHAR);  // ISBN
				stmnt.setObject    (10, new Integer(1),                        Types.INTEGER);  // author ID
				stmnt.setObject    (11, new Integer(1),                        Types.INTEGER);  // pub ID
				stmnt.setObject    (12, null,                                  Types.BLOB);     // picture
				
				ResultSet rs = stmnt.executeQuery();
				int colCount = rs.getMetaData().getColumnCount();
				while (rs.next())
				{
					for (int c=1; c<=colCount; c++)
					{
						String str = rs.getString(c);
						if (str == null)
							str="(NULL)";
						
						System.out.println("COL["+c+"]=|"+str+"|.");
					}
				}
				rs.close();
				stmnt.close();
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
			
			
			
			
			/////////////////////////////////////////////////////////////
			Thread.sleep(50);
			try
			{
				System.out.println();
				System.out.println("###############################################################");
				System.out.println("Test: 4 - @p_advance numeric(8,2) and Types.NUMERIC and NULL    THIS ONE FAILES");
				System.out.println("###############################################################");
				

				    conn.createStatement().execute("use tempdb");
				try{conn.createStatement().execute("  drop procedure dummy_p1"); } catch (SQLException ignore) {}
//				    conn.createStatement().execute("CREATE PROCEDURE dummy_p1(@p_title VARCHAR(75), @p_type CHAR(75), @p_price numeric,      @p_advance numeric,      @p_total_sales INT, @p_notes VARCHAR(75), @p_pubdate BIGDATETIME, @p_contract INT, @p_isbn CHAR(75), @p_au_id INT, @p_pub_id INT, @p_picture IMAGE) as select @p_title, @p_type, @p_price, @p_advance, @p_total_sales, @p_notes, @p_pubdate, @p_contract, @p_isbn, @p_au_id, @p_pub_id, @p_picture");
				    conn.createStatement().execute("CREATE PROCEDURE dummy_p1(@p_title VARCHAR(75), @p_type CHAR(75), @p_price numeric(8,2), @p_advance numeric(8,2), @p_total_sales INT, @p_notes VARCHAR(75), @p_pubdate BIGDATETIME, @p_contract INT, @p_isbn CHAR(75), @p_au_id INT, @p_pub_id INT, @p_picture IMAGE) as select @p_title, @p_type, @p_price, @p_advance, @p_total_sales, @p_notes, @p_pubdate, @p_contract, @p_isbn, @p_au_id, @p_pub_id, @p_picture");
			
				CallableStatement stmnt = conn.prepareCall("{call dummy_p1(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
				
				stmnt.setObject    (1,  new String("aaaaaaaaa"),               Types.VARCHAR);  // title
				stmnt.setObject    (2,  new String("bbbbbbbbb"),               Types.VARCHAR);  // type
				stmnt.setObject    (3,  new BigDecimal("1.1"),                 Types.NUMERIC);  // price
				stmnt.setObject    (4,  null,                                  Types.NUMERIC);  // advance     *** This works (if @p_advance is INT) BUT NOT (if @p_advance is NUMERIC or NUMERIC(8,2)) 
//				stmnt.setObject    (4,  new BigDecimal("2.2"),                 Types.NUMERIC);  // advance     *** This always works
//				stmnt.setNull      (4,                                         Types.NUMERIC);  // advance     *** This works (if @p_advance is INT) BUT NOT (if @p_advance is NUMERIC or NUMERIC(8,2))
				stmnt.setObject    (5,  new Integer(11),                       Types.INTEGER);  // total_sales
				stmnt.setObject    (6,  new String("some note"),               Types.VARCHAR);  // notes
				stmnt.setObject    (7,  new Date(System.currentTimeMillis()),  Types.TIMESTAMP);// pubdate
				stmnt.setObject    (8,  new Integer(1),                        Types.INTEGER);  // contract
				stmnt.setObject    (9,  new String("ISBN"),                    Types.VARCHAR);  // ISBN
				stmnt.setObject    (10, new Integer(1),                        Types.INTEGER);  // author ID
				stmnt.setObject    (11, new Integer(1),                        Types.INTEGER);  // pub ID
				stmnt.setObject    (12, null,                                  Types.BLOB);     // picture
				
				ResultSet rs = stmnt.executeQuery();
				int colCount = rs.getMetaData().getColumnCount();
				while (rs.next())
				{
					for (int c=1; c<=colCount; c++)
					{
						String str = rs.getString(c);
						if (str == null)
							str="(NULL)";
						
						System.out.println("COL["+c+"]=|"+str+"|.");
					}
				}
				rs.close();
				stmnt.close();
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
			

			
			/////////////////////////////////////////////////////////////
			Thread.sleep(50);
			try
			{
				System.out.println();
				System.out.println("###############################################################");
				System.out.println("Test: 5");
				System.out.println("###############################################################");

				PreparedStatement stmnt = conn.prepareStatement("select ?, ?, ?, ?, ?, ?");
				
				int p = 1;
				stmnt.setString    (p++, "str1");
				stmnt.setString    (p++, "str2");
				stmnt.setBigDecimal(p++, new BigDecimal("1"));
				stmnt.setObject    (p++, null, Types.NUMERIC);
				stmnt.setObject    (p++, new BigDecimal("2"), Types.NUMERIC);
				stmnt.setNull      (p++, Types.NUMERIC);
				
				ResultSet rs = stmnt.executeQuery();
				int colCount = rs.getMetaData().getColumnCount();
				while (rs.next())
				{
					for (int c=1; c<=colCount; c++)
					{
						String str = rs.getString(c);
						if (str == null)
							str="(NULL)";

						System.out.println("COL["+c+"]=|"+str+"|.");
					}
				}
				rs.close();
				stmnt.close();
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
			

			System.out.println("Closing connection");
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}

		System.out.println("Exiting...");
		
	}
}
