Arrays fetched from PostgreSQL are unusable by Lucee

[Markus Wollny]

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

<cfquery name="local.qryGetIntArray" datasource="mydatasource">

SELECT myintegerarray FROM sometable LIMIT 1;

</cfquery>

<cfset local.lstIntegers = ArrayToList(local.qryGetIntArray.myintegerarray) />

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:

<cfset local.lstIntegers = ArrayToList(ArrayMerge([],local.qryGetIntArray.myintegerarray)) />

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

[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 https://www.postgresql.org/docs/current/static/functions-array.html

In your case the array_to_string()

[Markus Wollny]

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:

<cfquery name="test" datasource="pgcbox">

SELECT ARRAY [1,234,2] AS foo

</cfquery>

<cfdump var="#ArrayToList(test.foo)#" />

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

Kind regards

  Markus

[Markus Wollny]

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 https://www.postgresql.org/docs/current/static/functions-array.html

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

[Markus Wollny]

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:

<cfquery name="test" datasource="pgcbox">

SELECT ARRAY [1,15,9] AS foo

</cfquery>

<cfscript>

foo=test.foo;

writeOutput('<br />');

writeoutput('ArrayToList: ');

try {

foo = ArrayToList(test.foo);

writeOutput('working.');

} catch (Any e) {

writeOutput('not working.');

}

writeOutput('<br />');

writeoutput('ArrayToList detached: ');

try {

foo = ArrayToList(foo);

writeOutput('working.');

} catch (Any e) {

writeOutput('not working.');

}

writeOutput('<br />');

writeoutput('ArrayFirst: ');

try {

foo = ArrayFirst(test.foo);

writeOutput('working.');

} catch (Any e) {

writeOutput('not working.');

}

writeOutput('<br />');

writeoutput('ArrayLast: ');

try {

foo = ArrayLast(test.foo);

writeOutput('working.');

} catch (Any e) {

writeOutput('not working.');

}

writeOutput('<br />');

writeoutput('ArrayToStruct: ');

try {

foo = ArrayToStruct(test.foo);

writeOutput('working.');

} catch (Any e) {

writeOutput('not working.');

}

writeOutput('<br />');

writeoutput('ArrayReverse: ');

try {

foo = ArrayReverse(test.foo);

writeOutput('working.');

} catch (Any e) {

writeOutput('not working.');

}

writeOutput('<br />');

writeoutput('ArraySlice: ');

try {

foo = ArraySlice(test.foo,1);

writeOutput('working.');

} catch (Any e) {

writeOutput('not working.');

}

</cfscript>

Output is 

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

[mark]

Sorry you may be right but Openbd does not see native array and a dump does include the notation.

[Markus Wollny]

Hi,

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

Kind regards

  Markus

[Markus Wollny]

Hi,

I just noticed that Micha fixed this 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