fmpro.executeSqlArray : Problems with result count and field names

[John A]

Two interesting (for me) problems discovered tonight after hours of experimentation. I would really really love it if I am doing it wrong.

1) Using fmpro.executeSqlArray, if a field is empty it just not returned. This makes reliable parsing of the resulting array impossible. Example:

String[][] site = fmpro.executeSqlArray("select id,customer,address1,address2,city,state,zip,plan,customer from site where id = " + siteIn);

if address2 is empty you get this as a result

[[37197, 58320, 30 Oak Dr., Napa, CA, 55555, 675, 55555]]

if address2 is populated you get this as a result

[[37197, 58320, 30 Oak Dr., suite 512, Napa, CA, 55555, 675, 55555]]

Note the missing address2 field in the first example. This shifts the array down one making static references to site[n][n] impossible.

Now, this would be ok if this next problem was not there

2) fmpro.executeSqlArray returns fieldnames in one case and not in another. I don't know why.

This query

String[][] config = fmpro.executeSqlArray("select token,tokenvalue from Configuration where token IN ('subscription_created_email','subscription_created_email_to','test_billing','test_billing_amount')");

returns something like this:

[[bc_companyid, thecompany], [bc_username, admin], [bc_password, thepassword], [test_billing, true]]

but this query

String[][] site = fmpro.executeSqlArray("select id,customer,address1,address2,city,state,zip,plan,customer from site where id = " + siteIn);

returns something like

[[37197, 58320, 30 Oak Dr., suite 512, Napa, CA, 55555, 675, 55555]]

Is this common knowledge? Any work-arounds?

Tx!

John-

[John A]

I also tested returning delimited text. In this configuration the empty fields are properly returned

37197|58320|30 Oak Dr.||Napa|CA|55555|675|55555

Muy mal :(

[John A]

See related thread: http://fmforums.com/forum/topic/74162-executesql-arrayindexoutofboundsexception/

No solution there yet, just cross-linking for future reference.

[john renfrew]

Is it not that internally if you add a 'null' value to an ArrayList in Groovy it will show null in the results inside SM but if this is 'literally' being converted to text so the only return options are to have the word 'null' in your returned String. It makes sense that the delimiter will apparently return the null value because the comma you see is the result of an Array to String conversion in Groovy not a concatenation.

What about someone testing this by using ',' as the delimiter and seeing if it produces the expected result???

List aa

list = [1,2,aa,4,5]

return list[2]

will return absolutely nothing not "null", null is only the word that Groovy uses to demonstrate that something is a non-existent value