Arrays fetched from PostgreSQL are unusable by Lucee

Hi,

In our next attempt to move our code from ACF to Lucee, we have stumbled
over another obstacle. We’re making use of PostgreSQLs array data type in a
couple of places, namely arrays of text[] and arrays of int[]. ACF didn’t
have any problems dealing with these as ordinary arrays, so we could for
example do this

SELECT myintegerarray FROM sometable LIMIT 1;

In Lucee this fails with the error message

“this method is no longer supported, use instead get(int,Object)”.

When I dump the query, local.qryGetIntArray.myintegerarray has the type “Native
Array (java.lang.Integer[])
”.

The quick and dirty hack around this is to ArrayMerge the returned value
with an empty array like so:

Apparently this copies the values from the native Java int[]-Array into the
shape that Lucee can deal with.

But this is fugly as hell, so I wonder if there was something I was missing
here? I’m very hesitant to refactor our code to incorporate a hack such as
this, it would be preferrable if Lucee would deal with Native Arrays
seamlessly, as does ACF.

It’s not an issue of the JDBC driver - I did try both the 8.3.0.jdbc4
driver that ships with Lucee and brewed my own PostgreSQL extension with
the more current 9.4.1209.jdbc41 driver, it doesn’t make a
difference. Lucee version is 5.0.0.252.

Should this be filed as a bug?

Kind regards

Markus

Sorry, I need to correct the findings for ArrayFirst: This is working as
expected, so it seems to be only ArrayToList and only when passing in the
query column. If the value is referenced in a variable and this is passed,
everything is working again:

SELECT ARRAY [1,15,9] AS foo foo=test.foo;

writeOutput(‘
’);
writeoutput('ArrayToList: ');
try {
foo = ArrayToList(test.foo);
writeOutput(‘working.’);
} catch (Any e) {
writeOutput(‘not working.’);
}

writeOutput(‘
’);
writeoutput('ArrayToList detached: ');
try {
foo = ArrayToList(foo);
writeOutput(‘working.’);
} catch (Any e) {
writeOutput(‘not working.’);
}

writeOutput(‘
’);
writeoutput('ArrayFirst: ');
try {
foo = ArrayFirst(test.foo);
writeOutput(‘working.’);
} catch (Any e) {
writeOutput(‘not working.’);
}

writeOutput(‘
’);
writeoutput('ArrayLast: ');
try {
foo = ArrayLast(test.foo);
writeOutput(‘working.’);
} catch (Any e) {
writeOutput(‘not working.’);
}

writeOutput(‘
’);
writeoutput('ArrayToStruct: ');
try {
foo = ArrayToStruct(test.foo);
writeOutput(‘working.’);
} catch (Any e) {
writeOutput(‘not working.’);
}

writeOutput(‘
’);
writeoutput('ArrayReverse: ');
try {
foo = ArrayReverse(test.foo);
writeOutput(‘working.’);
} catch (Any e) {
writeOutput(‘not working.’);
}

writeOutput(‘
’);
writeoutput('ArraySlice: ');
try {
foo = ArraySlice(test.foo,1);
writeOutput(‘working.’);
} catch (Any e) {
writeOutput(‘not working.’);
}

Output is

ArrayToList: not working.
ArrayToList detached: working.
ArrayFirst: working.
ArrayLast: working.
ArrayToStruct: working.
ArrayReverse: working.
ArraySlice: working.

Not sure how Adobe CF does it but that query does not work with Openbd or
Lucee and I don’t think it should?
Postgresql uses this as an array {1,2,3} vs [1,2,3] so maybe that would
cause the errors.

I would just use Postgres array
functions PostgreSQL: Documentation: 15: 9.19. Array Functions and Operators

In your case the array_to_string()On Thursday, July 28, 2016 at 7:48:33 AM UTC-5, Markus Wollny wrote:

Hi,

In our next attempt to move our code from ACF to Lucee, we have stumbled
over another obstacle. We’re making use of PostgreSQLs array data type in a
couple of places, namely arrays of text[] and arrays of int[]. ACF didn’t
have any problems dealing with these as ordinary arrays, so we could for
example do this

SELECT myintegerarray FROM sometable LIMIT 1;

In Lucee this fails with the error message

“this method is no longer supported, use instead get(int,Object)”.

When I dump the query, local.qryGetIntArray.myintegerarray has the type
Native Array (java.lang.Integer[])”.

The quick and dirty hack around this is to ArrayMerge the returned
value with an empty array like so:

Apparently this copies the values from the native Java int[]-Array into
the shape that Lucee can deal with.

But this is fugly as hell, so I wonder if there was something I was
missing here? I’m very hesitant to refactor our code to incorporate a hack
such as this, it would be preferrable if Lucee would deal with Native
Arrays seamlessly, as does ACF.

It’s not an issue of the JDBC driver - I did try both the 8.3.0.jdbc4
driver that ships with Lucee and brewed my own PostgreSQL extension with
the more current 9.4.1209.jdbc41 driver, it doesn’t make a
difference. Lucee version is 5.0.0.252.

Should this be filed as a bug?

Kind regards

Markus

Hi,

No, it really should work. The array notation you may see in PostgreSQL
admin interface or whatnot is just that, a notation. The JDBC driver is
fully aware of the array type and returns it accordingly as a java int[],
so we don’t just get a string with brackets, we get a proper array object
with all the bells and whistles. We’d really like to be able to process
those array values further in the application, ArrayToList is not the only
thing wer’re doing with array elements of query results, so it would be not
really a clean way of dealing with the issue to simply flatten this on the
database side. As you can see in the example, ArrayMerge has no issues with
this, so in a quick and dirty hack it can be employed to work around the
problem, as the resulting object is a proper CF-array.

I have provided a simpler test case in the post above, which illustrates
the issue more clearly. And I think that there’s really nothing wrong with
array values being processed as query result members by Lucee as such, it’s
just a couple of array-functions that have a glitch that prevents them from
dealing with this situation.

Kind regards

MarkusAm Donnerstag, 28. Juli 2016 17:28:25 UTC+2 schrieb mark:

Not sure how Adobe CF does it but that query does not work with Openbd or
Lucee and I don’t think it should?
Postgresql uses this as an array {1,2,3} vs [1,2,3] so maybe that would
cause the errors.

I would just use Postgres array functions
PostgreSQL: Documentation: 15: 9.19. Array Functions and Operators

In your case the array_to_string()

Sorry, I had better post the full stack trace. I believe that it may be
some problem in ArrayToList, though more functions seem to be affected:

“ERROR”,“ajp-nio-8009-exec-1”,“07/28/2016”,“13:04:17”,“”,";this method is
no longer supported, use instead
get(int,Object);lucee.runtime.exp.DeprecatedException: this method is no
longer supported, use instead get(int,Object)
at lucee.runtime.type.QueryColumnImpl.get(QueryColumnImpl.java:279)
at lucee.runtime.type.QueryColumnImpl.getE(QueryColumnImpl.java:799)
at
lucee.runtime.functions.list.ArrayToList.call(ArrayToList.java:55)
at
lucee.runtime.functions.list.ArrayToList.call(ArrayToList.java:35)
at
some.package.path.myclass_cfc$cf.udfCall8(/var/www/somepath/some/package/path/myclass.cfc:12345)

The problem here seems to me in ArrayToList, line 55:

if(len==1)return Caster.toString(array.getE(1));

This is calling getE(1) on the array that was passed. For an ordinary
array, this would probably resolve to the getE(int key) on line 155 of
ArrayImpl.java and we’d be fine, however in case of an array that was
fetched as part of a query result, we end up in QueryColumnImpl.java
instead, where getE(int row) is probably not what we want - this does a
get(row) wich in turn throws the DeprecatedException in line 279, advising
to use the method just below.

I don’t actually know how to build Lucee, nor am I much of a Java developer
and I’m a bit out of my depth when it comes to deciding how to resolve the
issue here. The getE(int)-method of QueryColumnImpl.java seems to me
completely broken, as we’d always end up in the deprecated get(int). So I
assume that there was some work going on here, but it didn’t get quite
finished.

There are a couple more matches for this getE-call in the
functions.arrays-folder, which may lead to the same result. In my tests, I
have got the same error with ArrayFirst. On the other hand, ArrayLast,
ArrayReverse and ArraySlice are not affected. As ArrayLast is extremely
similar, I am really quite at a loss, but again, I am no Java developer.

Here’s a very short test case to reproduce this:

SELECT ARRAY [1,234,2] AS foo

I hope this helps to pinpoint the issue. Please tell me if I can be of
assistance.

Kind regards

Markus

Sorry you may be right but Openbd does not see native array and a dump does
include the notation.On Thursday, July 28, 2016 at 10:43:00 AM UTC-5, Markus Wollny wrote:

Am Donnerstag, 28. Juli 2016 17:28:25 UTC+2 schrieb mark:

Not sure how Adobe CF does it but that query does not work with Openbd or
Lucee and I don’t think it should?
Postgresql uses this as an array {1,2,3} vs [1,2,3] so maybe that would
cause the errors.

I would just use Postgres array functions
PostgreSQL: Documentation: 15: 9.19. Array Functions and Operators

In your case the array_to_string()

Hi,

No, it really should work. The array notation you may see in PostgreSQL
admin interface or whatnot is just that, a notation. The JDBC driver is
fully aware of the array type and returns it accordingly as a java int[],
so we don’t just get a string with brackets, we get a proper array object
with all the bells and whistles. We’d really like to be able to process
those array values further in the application, ArrayToList is not the only
thing wer’re doing with array elements of query results, so it would be not
really a clean way of dealing with the issue to simply flatten this on the
database side. As you can see in the example, ArrayMerge has no issues with
this, so in a quick and dirty hack it can be employed to work around the
problem, as the resulting object is a proper CF-array.

I have provided a simpler test case in the post above, which illustrates
the issue more clearly. And I think that there’s really nothing wrong with
array values being processed as query result members by Lucee as such, it’s
just a couple of array-functions that have a glitch that prevents them from
dealing with this situation.

Kind regards

Markus

Hi,

I filed this as a bug now (LDEV-949).

Kind regards

Markus

Hi,

I just noticed that Micha fixed this
https://github.com/lucee/Lucee/commit/9b16f8df36b947186503264d4800cc6b864af02c
with 5.0.1.42 - thank you! We’re not in production mode yet, so I’m in the
comfortable position to switch to the snapshots and don’t need to wait for
the next stable release, but it’s good to see this has been adressed in
such a speedy fashion - great job!

Kind regards

Markus