Thread: Not able to insert array of integers into column of type integer array
Hi, I am trying to insert a row to a column in a table which is of data type INT4[]. I am not sure what should be the code in java I should write so that I can write the insert query. Exception I am getting below: Help needed!!!! junit.framework.AssertionFailedError: PreparedStatementCallback; bad SQL grammar [INSERT INTO ORGANIZATION_STRUCTURE (ID_ORGANIZATION_STRUCTURE,ID_CLIENT,ORG_LEVEL,AR_ORG_MAP,ID_SUP_LEVEL,ORG_NAME,TIMEZONE_ABREV,ORG_CURRENCY_CODE_ALPHA) VALUES(NEXTVAL('SEQ_ORGANIZATION_STRUCTURE'),?,?,?,?,?,?,?)]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [Ljava.lang.Integer;. Use setObject() with an explicit Types value to specify the type to use. at junit.framework.Assert.fail(Assert.java:50) at com.ingenico.atlas.integration.eportal.dao.CustomerDAOTest.createOrgStructureTest(CustomerDAOTest.java:59) 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:45) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71) at org.junit.runners.ParentRunner.run(ParentRunner.java:300) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174) 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) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Not-able-to-insert-array-of-integers-into-column-of-type-integer-array-tp5725537.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
When setting or updating arrays in a ResultSet or PreparedStatement you can't use java array types (Integer[], ...) -- they need to be wrapped in a "java.sql.Array". This is done using the Connection-object. here's an example with Floats: Connection con = // your connection Double[] dbArr = // ... some java array Array sqlArray = con.createArrayOf("float8", dbArr); Now "sqlArray" can be used with setArray() (or even setObject() ). (not sure where, but there were some other examples in the JDK-docs or pg-JDBC-docs ...) pprotim schrieb: > Hi, > > I am trying to insert a row to a column in a table which is of data type > INT4[]. I am not sure what should be the code in java I should write so that > I can write the insert query. Exception I am getting below: > Help needed!!!! > > > junit.framework.AssertionFailedError: PreparedStatementCallback; bad SQL > grammar [INSERT INTO ORGANIZATION_STRUCTURE > (ID_ORGANIZATION_STRUCTURE,ID_CLIENT,ORG_LEVEL,AR_ORG_MAP,ID_SUP_LEVEL,ORG_NAME,TIMEZONE_ABREV,ORG_CURRENCY_CODE_ALPHA) > VALUES(NEXTVAL('SEQ_ORGANIZATION_STRUCTURE'),?,?,?,?,?,?,?)]; nested > exception is org.postgresql.util.PSQLException: Can't infer the SQL type to > use for an instance of [Ljava.lang.Integer;. Use setObject() with an > explicit Types value to specify the type to use. > at junit.framework.Assert.fail(Assert.java:50) > at > com.ingenico.atlas.integration.eportal.dao.CustomerDAOTest.createOrgStructureTest(CustomerDAOTest.java:59) > 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:45) > at > org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) > at > org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42) > at > org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) > at > org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74) > at > org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83) > at > org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72) > at > org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231) > at > org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47) > at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231) > at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60) > at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229) > at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50) > at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222) > at > org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) > at > org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71) > at org.junit.runners.ParentRunner.run(ParentRunner.java:300) > at > org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174) > 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) > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Not-able-to-insert-array-of-integers-into-column-of-type-integer-array-tp5725537.html > Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com. > >
From: Stefan Reiser [via PostgreSQL] <[hidden email]>
To: pprotim <[hidden email]>
Sent: Wednesday, September 26, 2012 9:40 PM
Subject: Re: Not able to insert array of integers into column of type integer array
When setting or updating arrays in a ResultSet or PreparedStatement you
can't use java array types (Integer[], ...) -- they need to be wrapped
in a "java.sql.Array". This is done using the Connection-object.
here's an example with Floats:
Connection con = // your connection
Double[] dbArr = // ... some java array
Array sqlArray = con.createArrayOf("float8", dbArr);
Now "sqlArray" can be used with setArray() (or even setObject() ).
(not sure where, but there were some other examples in the JDK-docs or
pg-JDBC-docs ...)
pprotim schrieb:
> Hi,
>
> I am trying to insert a row to a column in a table which is of data type
> INT4[]. I am not sure what should be the code in java I should write so that
> I can write the insert query. Exception I am getting below:
> Help needed!!!!
>
>
> junit.framework.AssertionFailedError: PreparedStatementCallback; bad SQL
> grammar [INSERT INTO ORGANIZATION_STRUCTURE
> (ID_ORGANIZATION_STRUCTURE,ID_CLIENT,ORG_LEVEL,AR_ORG_MAP,ID_SUP_LEVEL,ORG_NAME,TIMEZONE_ABREV,ORG_CURRENCY_CODE_ALPHA)
> VALUES(NEXTVAL('SEQ_ORGANIZATION_STRUCTURE'),?,?,?,?,?,?,?)]; nested
> exception is org.postgresql.util.PSQLException: Can't infer the SQL type to
> use for an instance of [Ljava.lang.Integer;. Use setObject() with an
> explicit Types value to specify the type to use.
> at junit.framework.Assert.fail(Assert.java:50)
> at
> com.ingenico.atlas.integration.eportal.dao.CustomerDAOTest.createOrgStructureTest(CustomerDAOTest.java:59)
> 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:45)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
> at
> org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
> at
> org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
> at
> org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
> at
> org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
> at
> org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
> at
> org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
> at
> org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
> 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)
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Not-able-to-insert-array-of-integers-into-column-of-type-integer-array-tp5725537.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
>
--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
can't use java array types (Integer[], ...) -- they need to be wrapped
in a "java.sql.Array". This is done using the Connection-object.
here's an example with Floats:
Connection con = // your connection
Double[] dbArr = // ... some java array
Array sqlArray = con.createArrayOf("float8", dbArr);
Now "sqlArray" can be used with setArray() (or even setObject() ).
(not sure where, but there were some other examples in the JDK-docs or
pg-JDBC-docs ...)
pprotim schrieb:
> Hi,
>
> I am trying to insert a row to a column in a table which is of data type
> INT4[]. I am not sure what should be the code in java I should write so that
> I can write the insert query. Exception I am getting below:
> Help needed!!!!
>
>
> junit.framework.AssertionFailedError: PreparedStatementCallback; bad SQL
> grammar [INSERT INTO ORGANIZATION_STRUCTURE
> (ID_ORGANIZATION_STRUCTURE,ID_CLIENT,ORG_LEVEL,AR_ORG_MAP,ID_SUP_LEVEL,ORG_NAME,TIMEZONE_ABREV,ORG_CURRENCY_CODE_ALPHA)
> VALUES(NEXTVAL('SEQ_ORGANIZATION_STRUCTURE'),?,?,?,?,?,?,?)]; nested
> exception is org.postgresql.util.PSQLException: Can't infer the SQL type to
> use for an instance of [Ljava.lang.Integer;. Use setObject() with an
> explicit Types value to specify the type to use.
> at junit.framework.Assert.fail(Assert.java:50)
> at
> com.ingenico.atlas.integration.eportal.dao.CustomerDAOTest.createOrgStructureTest(CustomerDAOTest.java:59)
> 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:45)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
> at
> org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
> at
> org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
> at
> org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
> at
> org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
> at
> org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
> at
> org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
> at
> org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
> 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)
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Not-able-to-insert-array-of-integers-into-column-of-type-integer-array-tp5725537.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
>
--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Not-able-to-insert-array-of-integers-into-column-of-type-integer-array-tp5725537p5725550.html To unsubscribe from Not able to insert array of integers into column of type integer array, click here.
NAML
NAML
View this message in context: Re: Not able to insert array of integers into column of type integer array
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
Re: Re: Not able to insert array of integers into column of type integer array
From
Craig Ringer
Date:
pprotim: It looks like you tried to reply here, but just posted the quoted original message. I've been looking at the JDBC standard to see if PgJDBC can legally accept arrays of primitive types and strings, not just java.sql.Array . While this is something that the standard does not require it seems to be something people expect the driver to do, and something other drivers do. If you work with and have tested with other drivers it'd be helpful to know how they behave for as many different drivers as you have access to. -- Craig Ringer On 10/02/2012 12:08 AM, pprotim wrote: > > ------------------------------------------------------------------------ > *From:* Stefan Reiser [via PostgreSQL] <[hidden email]> > *To:* pprotim <[hidden email]> > *Sent:* Wednesday, September 26, 2012 9:40 PM > *Subject:* Re: Not able to insert array of integers into column of type > integer array > > When setting or updating arrays in a ResultSet or PreparedStatement you > can't use java array types (Integer[], ...) -- they need to be wrapped > in a "java.sql.Array". This is done using the Connection-object. > > here's an example with Floats: > > Connection con = // your connection > Double[] dbArr = // ... some java array > Array sqlArray = con.createArrayOf("float8", dbArr); > > Now "sqlArray" can be used with setArray() (or even setObject() ). > (not sure where, but there were some other examples in the JDK-docs or > pg-JDBC-docs ...)