dcsimg
Inserting null value in record for decimal datatype in the MS SQLServer7 through PreparedStatements setObject
0 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Anonymous
Posted On:   Thursday, August 9, 2001 02:53 AM

Dear JGuru, I want to insert record in the data irrespective of the database.I used PreparedStatement's setObject method to insert the record. The code below worked fine If I inserted values other than null for a datatype decimal in SQLServer7.with JDK1.2.1 and the JDBC that came with that version of kit. import java.sql.*; import java.util.*; public class QueryManager { //Constants private static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver"; private static final String DATASOURCENAME = "jdbc:odbc:traceback"; private static final String USERNAME = "sa"; private static final String PASSWORD = "";    More>>

Dear JGuru,
I want to insert record in the data irrespective of the database.I used PreparedStatement's setObject method to insert the record.
The code below worked fine If I inserted values other than null for a datatype decimal in SQLServer7.with
JDK1.2.1 and the JDBC that came with that version of kit.

			

import java.sql.*;
import java.util.*;

public class QueryManager
{
//Constants
private static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
private static final String DATASOURCENAME = "jdbc:odbc:traceback";
private static final String USERNAME = "sa";
private static final String PASSWORD = "";

//Variables
private Connection connection;
private Statement statement;
private ResultSet resultSet;
private ResultSetMetaData resultSetMetaData;
private int noOfColumns;
private int[] columnTypes;
private String[] columnNames,columnTypeNames;
static String nullValue;

public static void main(String as[]) throws Exception
{
QueryManager qm = new QueryManager();

Vector v= new Vector();
Vector v1=new Vector();
java.util.Date dt = new java.util.Date();
v.add("ENGINEER");
v.add("101");
v.add(null);
v.add(null);
v.add("123445.786");
v.add(new java.sql.Timestamp(dt.getYear(),dt.getMonth(),dt.getDate(),0,0,0,0));
v.add(null);
qm.insertRow(v);
v1.add("ENGINEER");
v1.add("102");
v1.add("Vishnu");
v1.add(null);
v1.add("234.33");
v1.add("15-Aug-2001");
v1.add(null);
qm.insertRow(v1);*/
}
public void insertRow(Vector columnValues) throws Exception
{
String tableName = columnValues.firstElement().toString();
getTableInfo(tableName);
Connection connection = connectToDataBase();
String questionMarks ="(";
for(int questionMarkIndex=0;questionMarkIndex <(columnValues.size()-1);questionMarkIndex++)
questionMarks+="?,";
questionMarks = questionMarks.substring(0,questionMarks.lastIndexOf(","))+")";
String preparedInsertionQuery = "INSERT INTO "+tableName+" VALUES "+questionMarks;
PreparedStatement preparedStatement = connection.prepareStatement(preparedInsertionQuery);
for(int colValue=1;colValue <= (columnValues.size()-1);colValue++)
{
System.out.println(colValue+" "+columnNames[colValue-1]+" "+columnTypeNames[colValue-1]);
if(columnValues.get(colValue)==null)
preparedStatement.setNull(colValue,columnTypes[colValue-1]);
else
preparedStatement.setObject(colValue,columnValues.get(colValue));
}
preparedStatement.executeUpdate();
preparedStatement.close();
connection.close();
System.out.println("Row Successfully inserted in Table : "+tableName);
}
private Connection connectToDataBase() throws Exception
{
Class.forName(DRIVER);
connection = DriverManager.getConnection(DATASOURCENAME,USERNAME,PASSWORD);
return connection;
}
private void getTableInfo(String tableName) throws Exception
{
Connection connection = connectToDataBase();
statement = connection.createStatement();
String selectQuery = "SELECT * FROM "+tableName;
System.out.println(selectQuery);
resultSetMetaData = statement.executeQuery(selectQuery).getMetaData();
columnTypes = new int[resultSetMetaData.getColumnCount()];
columnNames = new String[resultSetMetaData.getColumnCount()];
columnTypeNames = new String[resultSetMetaData.getColumnCount()];
System.out.println(resultSetMetaData.getColumnCount());
for(int columnIndex = 0;columnIndex < columnTypes.length;columnIndex++)
{
columnNames[columnIndex] = resultSetMetaData.getColumnName(columnIndex+1);
columnTypes[columnIndex] = resultSetMetaData.getColumnType(columnIndex+1);
columnTypeNames[columnIndex]=resultSetMetaData.getColumnTypeName(columnIndex+1);
}
connection.close();
}
}


The Following Exception has been triggered.
			
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Column or parameter #4: Specified column precision 38 is greater than the maximum precision of 28.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:4089)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:4246)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:1211)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:206)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:150)
at QueryManager.insertRow(QueryManager.java:66)
at QueryManager.main(QueryManager.java:36)
   <<Less
About | Sitemap | Contact