Invalid column name error

[Robert]

All,

I'm relatively new to ActiveJDBC.  Recently, I've come across an error that states I have an invalid column name.  I've double-checked the name and it is correct.  The only trouble is that ActiveJDBC has set the name to lower-case.  The column name in the database is upper-case. I'm not sure if that makes any difference, but I suspect the upper vs. lower case is the problem here.  Any clue or ideas as to how I might workaround this would be much appreciated.

My test code

public void createProjectRecord() {

System.out.println();

System.out.println("Creating a new Project record ...");

// product_number=020095 (PETER PAN)

ProjectTable project = new ProjectTable();

project.set("CREATED_BY", "SPT.QA.PMO");

project.set("ASSIGNED_TO", "SPT.QA.PMO");

project.set("PROJECT_TEMPLATE_DISPLAY", "DigiQ");

project.set("TITLE", "PETER PAN");

project.set("NAME", "PETER PAN");

project.set("PRODUCT_NUMBER", "020095");

project.set("HIBERNATE_VERSION", "3");

project.set("LAST_MODIFIED_BY", "SPT.QA.PMO");

project.set("PROJECT_STATE", "NEW");

project.set("ASPECT_RATIO", "AR_2_40");

project.set("WMLS_PRODUCT_TYPE", "F");

project.set("WMLS_ELEMENT_VERSION", "1");

project.set("PICTURE_SOURCE", "D5");

project.set("PROJECT_TEMPLATE_ID", "1");

project.set("PROJECT_DUE_DATE", "2012-12-31");

project.set("FILMMAKER_INVOLVEMENT", "false");

project.set("REASON", "44");

project.set("DEPARTMENT", "WPPO-123");

project.saveIt();

}

The Stack Trace

org.javalite.activejdbc.DBException: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'created_by'., Query: INSERT INTO PROJECT (created_by, picture_source, project_template_display, reason, wmls_element_version, department, wmls_product_type, filmmaker_involvement, assigned_to, title, last_modified_by, project_state, name, hibernate_version, product_number, project_template_id, project_due_date, aspect_ratio) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), params: SPT.QA.PMO,D5,DigiQ,44,1,WPPO-123,F,false,SPT.QA.PMO,PETER PAN,SPT.QA.PMO,NEW,PETER PAN,3,020095,1,2012-12-31,AR_2_40

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314)

at org.javalite.activejdbc.DB.execInsert(DB.java:521)

at org.javalite.activejdbc.Model.insert(Model.java:2109)

at org.javalite.activejdbc.Model.save(Model.java:2021)

at testme.ProjectTable.save(ProjectTable.java)

at org.javalite.activejdbc.Model.saveIt(Model.java:1948)

at testme.ProjectTable.saveIt(ProjectTable.java)

at testme.TestActiveJDBCExample.createProjectRecord(TestActiveJDBCExample.java:53)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)

at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)

at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)

at org.junit.runners.ParentRunner.run(ParentRunner.java:236)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'created_by'.

... 39 more

[igor]

Robert, try to isolate this to a very small test of SQL running by hand and verify  that this indeed the issue. Post results here so we can make further decisions. 

thanks

igor

[Robert]

Igor,

I opened up my SQL Server client and successfully executed the following 

select count(ASSIGNED_TO) from PROJECT

which returned a count of "3"

However, when I attempted to run 

select count(assigned_to) from PROJECT

I received the following error: "Invalid column name 'assigned_to'".  I also spoke with our DBA and he pointed out that when the developers set up the database, they did so, with collation(?) set to true -- apparently, this forces the user to execute SQL commands using upper-cased column names.

Is there some sort of workaround for this in ActiveJDBC, or will I need to speak with our DBA/Dev team to see if they can make column names case-insensitive?

[igor]

It is best of course to talk to the DBA to have this case sensitivity turned off. There is absolutely no need to identifiers to be case sensitive. SQL Standard is somewhat vague on this topic. With case sensitive identifiers you will run into many problems which can be easily avoided. 

If they absolutely refuse, come back and we will try to help you. 

thanks

igor

[Robert]

Igor,

Just thought I'd let you know that I spoke with the DBA and Devs and we agreed to set the DB to case-insensitive.  All is well, now. Thanks.

[Igor Polevoy]

Fantastic, good luck.
However, looking back retrospectively it probably was not a smart thing to lowercase things
--
Igor