Hello, I'm new to Baan LN and I used to program in Java before. I wanted to ask if there is a way to return a complex object when retrieving values ​​from a query of a custom table but without passing values ​​in reference.

function ??? get.row.value(string table.name, string field.name, string field.value) {

long sql.id
string sql.qry

| the fields could increase and I would not like to touch any functions already implemented
sql.qry = " select name:1 surname:2 ... "
" from " & table.name &
" where " & field.name & " = :" & field.value &
" as set with 1 rows "

sql.id = sql.parse(sql.qry)
sql.exec(sql.id)

sql.select.bind(sql.id, 1, ???)
sql.select.bind(sql.id, 2, ???)

sql.fatch(sql.id)
sql.close(sql.id)
}

In Java I would have created a HashMap <int, Object> or a class object because each field can have a different type, here I have no idea how to do it.

Thank you so much

Moved to the tools forum. Hopefully someone can answer your question.

Not really sure this answers your question or helps - what this did was basically build a query and then return records from tipgc520 and 521. What I had was a main session that would ask for some filters - like buyer, item, project group, etc. It would call this subsession to build a query to return the records to be displayed. Then in the subsession if you hit like the next record group it would navigates its way thru all of the records. This was done on 4c4 so for LN there could be better solutions.


function get_record()
{
domain tcbool record.found
domain tcorno original.order
long sql_id

| Use record.found to make sure a record is found.
record.found = false
original.order = tipgc521.orno

| Find all records greater than current record. Run through them
| looking for the first order that matches all filters.
| Use the >= sign because by the time this routine is called the main program
| has increased the order number.
build_sql_query()

| Setup the query
sql_id = sql.parse(sql_query)

| Bind the variables.
sql.where.bind(sql_id,1,original.order)
sql.where.bind(sql_id,2,ccot.filter)
sql.where.bind(sql_id,3,item.filter)
sql.where.bind(sql_id,4,suno.filter)
sql.where.bind(sql_id,5,buyr.filter)
| Execute the query.
sql.exec(sql_id)

| Fetch the first record.
e = sql.fetch(sql_id)
if e = 0 then
record.found = true
else
message("No more records.")
select tipgc521.*
from tipgc521
where tipgc521.orno = :hold.orno
as set with 1 rows
selectdo
endselect
endif
sql.close(sql_id)

}
|******************************************************************************
| Function to build the sql
|******************************************************************************
function build_sql_query()
{
| Same for all queries
sql_query =
"select tipgc520.*, tipgc521.* " &
"from tipgc520,tipgc521 "

on case record_event
case 1: | First record
case 4: | Last record
sql_query = sql_query &"where tipgc521.orno = tipgc520.orno "
break
case 2: | Next record
sql_query = sql_query &"where tipgc520.orno >= :1 " &
"and tipgc521.orno = tipgc520.orno "
break
case 3:
sql_query = sql_query &"where tipgc520.orno <= :1 " &
"and tipgc521.orno = tipgc520.orno "
break
endcase
if(ccot.filter<>"") then
sql_query = sql_query & "and tipgc520.ccot = :2 "
endif
if(item.filter<>"") then
sql_query = sql_query & "and tipgc520.item = :3 "
endif
if(suno.filter<>"") then
sql_query = sql_query & "and tipgc520.suno = :4 "
endif
if(buyr.filter<>0) then
sql_query = sql_query & "and tipgc520.buyr = :5 "
endif

on case record_event
case 1: | First record
sql_query = sql_query & "order by tipgc520.orno asc "
sql_query = sql_query & "as set with 1 rows "
break
case 2: | Next record
break
case 3: | Previous record
sql_query = sql_query & "order by tipgc521.orno desc"
break
case 4: | Last record
sql_query = sql_query & "order by tipgc520.orno desc "
sql_query = sql_query & "as set with 1 rows "
break
endcase
}

Maybe I have found a solution but not so if it is the best.
I needed this function to retrieve the value of various calculated fields for a Report Design.

function extern void ext.shfa.calculate()
{
ext.shfa = txextdll0001.get.string("shfa", component.name, get.logistic.company(), get.current.site())
}

I created a dll that uses the <bic_json>.
This exposes two functions depending on the type of data it returns.

| Retrieve the booleando from the json
function extern domain tcbool txextdll0001.get.boolean.from.tgyenox(domain tcmcs.str16 field, domain tcmcs.str16 component.name, domain tcncmp company, domain tcsite site)
{
long json_object
get.fields.values(json_object, component.name, company, site)
return (Json.getBoolean(json_object, field))
}

| Retrieve the string from the json
function extern domain tcmcs.str50m txextdll0001.get.string(domain tcmcs.str16 field, domain tcmcs.str16 component.name, domain tcncmp company, domain tcsite site)
{
long json_object
get.fields.values(json_object, component.name, company, site)
return (Json.getString(json_object, field))
}

Both call a single function passing the json id in reference.

| Retrieve all values from the table and put them in a json
function void get.fields.values(ref long json_object, domain tcmcs.str16 component.name, domain tcncmp company, domain tcsite site)
{

domain tgyenox prdt, pdta, pbil, psls, ptax, pout, pcus, pshf
domain tcmcs.str50m shfa, shfb, shfc, shfd

select txext000.prdt:prdt, txext000.pdta:pdta, txext000.pbil:pbil, txext000.psls:psls,
txext000.ptax:ptax, txext000.pout:pout, txext000.pcus:pcus, txext000.pshf:pshf,
txext000.shfa:shfa, txext000.shfb:shfb, txext000.shfc:shfc, txext000.shfd:shfd
from txext000
where txext000.name = {:component.name}
and txext000.cpny = {:company}
and txext000.site = {:site}
selectdo
endselect

| Popolo il json
json_object = Json.newObject()
Json.setBoolean(json_object, "prdt", tgyenox.to.boolean(prdt))
Json.setBoolean(json_object, "pdta", tgyenox.to.boolean(pdta))
Json.setBoolean(json_object, "pbil", tgyenox.to.boolean(pbil))
Json.setBoolean(json_object, "psls", tgyenox.to.boolean(psls))
Json.setBoolean(json_object, "ptax", tgyenox.to.boolean(ptax))
Json.setBoolean(json_object, "pout", tgyenox.to.boolean(pout))
Json.setBoolean(json_object, "pcus", tgyenox.to.boolean(pcus))
Json.setBoolean(json_object, "pshf", tgyenox.to.boolean(pshf))
Json.setString(json_object, "shfa", shfa)
Json.setString(json_object, "shfb", shfb)
Json.setString(json_object, "shfc", shfc)
Json.setString(json_object, "shfd", shfd)
}

| Function that converts tgyenox to boolean
function domain tcbool tgyenox.to.boolean(domain tgyenox current)
{
domain tcbool bool
| Need true by default
bool = true
if current = tgyenox.no then
bool = false
endif
return (bool)
}

However new types of implementation are welcome !!!

This morning I tried to find an alternative solution and I think more standard.

Always starting from this situation.
function extern void ext.shfa.calculate()
{
ext.shfa = txextdll0001..get.field.value.str50m("shfa", component.name, get.logistic.company(), get.current.site())
}


I created two functions that call functions sequentially and only retrieve a point value.

function extern domain tcbool txextdll0001.get.field.value.tgyenox(domain tcmcs.str16 field.name, domain tcmcs.str16 component.name, domain tcncmp company, domain tcsite site)
{
domain tgyenox field.value
long sql.id
| open connection
start.connection(sql.id, field.name, component.name, company, site)
| bind value
sql.select.bind(sql.id, 1, field.value)
| close connection
close.connection(sql.id)

return(txextdll0001.tgyenox.to.boolean(field.value, true))
}

function extern domain tcbool txextdll0001.tgyenox.to.boolean(domain tgyenox yenox, domain tcbool initial.value)
{
domain tcbool bool
bool = initial.value
if yenox = tgyenox.no then
bool = false
endif
return (bool)
}

function extern domain tcmcs.str50m txextdll0001.get.field.value.str50m(domain tcmcs.str16 field.name, domain tcmcs.str16 component.name, domain tcncmp company, domain tcsite site)
{
domain tcmcs.str50m field.value
long sql.id
| open connection
start.connection(sql.id, field.name, component.name, company, site)
| bind value
sql.select.bind(sql.id, 1, field.value)
| close connection
close.connection(sql.id)

return (field.value)
}


function void start.connection(ref long sql.id, domain tcmcs.str16 field.name, domain tcmcs.str16 component.name, domain tcncmp company, domain tcsite site)
{
string query.string.mb(3000)
query.string.mb = " select txext000." & trim$(field.name) & ":1 "
& " from txext000 "
& " where txext000.name = '" & trim$(component.name) & "' "
& " and txext000.cpny = " & str$(company)
& " and txext000.site = '" & trim$(site) & "' "
& " as set with 1 rows "
sql.id = sql.parse(query.string.mb)
}


function void close.connection(ref long sql.id)
{
sql.exec(sql.id)
|This reads a single query result and stores the values retrieved in the variables specified in the SELECT list.
on case sql.fetch(sql.id)
case 0:
|* Set not empty
case EENDFILE:
|* Reached the end result set
break
case ENOREC:
|* Set is empty
message("txextdll0001: No record found.")
break
endcase
sql.break(sql.id)
sql.close(sql.id)
}

Being new to LN I think it is better because it keeps the table types ... actually I have not found documentation on json so I don't know if it could alter some values.

Hello,

JSON overview
INTRODUCTION
JavaScript Object Notation (JSON) is a lightweight, text-based, language-independent data interchange format. It was derived from the ECMAScript Programming Language Standard. JSON defines a small set of formatting rules for the portable representation of structured data.

JSON is built on two structures:

A collection of name/value pairs. In JSON this is called an object.
An ordered list of values. In JSON this is called an array.
OBJECT
An object is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma).

ARRAY
An array is an ordered collection of values. An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by , (comma).

VALUE
A value can be a string in double quotes, or a number, or true or false or null, or an object or an array. These structures can be nested.

STRING
A string is a sequence of zero or more Unicode characters, wrapped in double quotes, using backslash escapes. A character is represented as a single character string. A string is very much like a C or Java string.

NUMBER
A number is very much like a C or Java number, except that the octal and hexadecimal formats are not used.

WHITESPACE HANDLING
Whitespace can be inserted between any pair of tokens.

JSON API
The 3GL JSON API supports reading, writing, creating, manipulating and destroying JSON value instances. These instances can be of one of the following types:

JSON_TYPE_NULL
JSON_TYPE_BOOLEAN
JSON_TYPE_NUMBER
JSON_TYPE_OBJECT
JSON_TYPE_ARRAY
JSON_TYPE_STRING
The JSON API provides functions to test whether a value is a JSON value and to get the type of the JSON value instance.

DEALING WITH JSON TYPES
Most JSON API functions accept a JSON value instance as the first parameter. It depends on the JSON type whether the function can deal with the passed JSON value. E.g. the Json.set() function can only deal with JSON values of type JSON_TYPE_OBJECT and the Json.add() function can only deal with JSON values of type JSON_TYPE_ARRAY.

In case a function cannot handle the passed JSON type, a runtime Assert message is given.

DETACHING JSON INSTANCES
In order to prevent JSON structures from being corrupted, the JSON API is very strict regarding setting, adding, putting a JSON value in another JSON value, or deleting a JSON value. In case a JSON value is part of another JSON value, the operation is prevented and a runtime Assert message is given.

A JSON value must therefore be detached from its parent using Json.detach(), before such operations are done.

ITERATING JSON STRUCTURES
For cases where the structure of the JSON value is not known, the JSON API provides functions for iterating a JSON structure.

Using an iterator is also the recommended way to traverse all values in a JSON array.

READING AND WRITING JSON
The 3GL JSON API provides functions for reading JSON text from and writing JSON values to a string, a file or a stream.

SUPPORTED ENCODINGS
Both UTF-8 and TSS encodings are supported for reading and writing JSON values.

EXAMPLE USAGE
{
"id": "ABC12345",
"name": "ABC Bike",
"supplier": {
"name": "ACME_COMP",
"address": {
"street": "High Street",
"number": 235,
"zip code": "1234 AB"
}
},
"price": 534.99,
"packed": true,
"currencies": [
"EUR",
"USD",
"AUD"
],
"color": null,
"active": false
}
The JSON document above can be constructed as follows:

long item
long supplier
long address
long currencies

item = Json.newObject()
Json.setString(item, "id", "ABC12345")
Json.setString(item, "name", "ABC Bike")
supplier = Json.set(item, "supplier", Json.newObject())
Json.setString(supplier, "name", "ACME_COMP")
address = Json.set(supplier, "address", Json.newObject())
Json.setString(address, "street", "High Street")
Json.setNumber(address, "number", 235)
Json.setString(address, "zip code", "1234 AB")
Json.setNumber(item, "price", 534.99)
Json.setBoolean(item, "packed", true)
currencies = Json.set(item, "currencies", Json.newArray())
Json.add(currencies, "EUR")
Json.add(currencies, "USD")
Json.add(currencies, "AUD")
Json.setNull(item, "color")
Json.setBoolean(item, "active", false)
Retrieving data:

string itemName(30)
string zipCode(8)
string currency(3)
string color(12)
long type

|* To get the item name
itemName = Json.getString(item, "name")

|* To get the value of the "zip code" field starting at the item level:
zipCode = Json.getString(Json.get(Json.get(item, "supplier"), "address"), "zip code")

|* The same, using the path
zipCode = Json.string(Json.path(item, "supplier", "address", "zip code"))

|* To get the 3rd currency:
currency = Json.stringAt(Json.get(item, "currencies"), 3)

|* The same, using the path
currency = Json.string(Json.path(item, "currencies", 3))

|* To get the value of color, which might be null
if not Json.isNull(Json.get(item, "color")) then
color = Json.getString(item, "color"))
endif

|* To get the type of the price field
type = Json.type(Json.get(item, "price"))

if type = JSON_TYPE_NUMBER then
|* ...
endif
Manipulating data:

|* To replace the 2nd currency with GBP
Json.putString(Json.get(item, "currencies"), 2, "GBP)

|* To delete the packed field
Json.del(item, "packed")

|* To delete the currencies array, it first needs to be detached from the item object
currencies = Json.get(item, "currencies)
Json.detach(currencies)
Json.delete(currencies)



JSON synopsis
General JSON value functions
long Json.newObject ()
long Json.newArray ()
long Json.newBoolean ( boolean value )
boolean Json.boolean ( long json_value )
long Json.newNull ()
boolean Json.isNull ( long json_value )
long Json.newNumber ( long|double value )
double Json.number ( long json_value )
long Json.newString ( const string value )
string Json.string ( long json_value )
long Json.copy ( long json_value )
long Json.copyToProcess ( long json_value, long process_id )
void Json.delete ( long json_value )
[long] Json.detach ( long json_value )
long Json.type ( long json_value )
boolean Json.isJson ( long json_value )
long Json.path ( long json_value, ... )

JSON object functions
[long] Json.set ( long json_object, const string key, long json_value )
boolean Json.has ( long json_object, const string key )
long Json.get ( long json_object, const string key )
void Json.del ( long json_object, const string key )
[long] Json.setBoolean ( long json_object, const string key, boolean value )
[long] Json.setNull ( long json_object, const string key )
[long] Json.setNumber ( long json_object, const string key, long|double value )
[long] Json.setString ( long json_object, const string key, const string value )
boolean Json.getBoolean ( long json_object, const string key )
double Json.getNumber ( long json_object, const string key )
string Json.getString ( long json_object, const string key )

JSON array functions
[long] Json.add ( long json_array, long json_value )
[long] Json.put ( long json_array, long idx, long json_value )
long Json.at ( long json_array, long idx )
long Json.count ( long json_array )
[long] Json.addBoolean ( long json_array, boolean value )
[long] Json.addNull ( long json_array )
[long] Json.addNumber ( long json_array, long|double value )
[long] Json.addString ( long json_array, const string value )
[long] Json.putBoolean ( long json_array, long idx, boolean value )
[long] Json.putNull ( long json_array, long idx )
[long] Json.putNumber ( long json_array, long idx, long|double value )
[long] Json.putString ( long json_array, long idx, const string value )
boolean Json.booleanAt ( long json_array, long idx )
double Json.numberAt ( long json_array, long idx )
string Json.stringAt ( long json_array, long idx )

JSON iterator functions
long Json.iter ( long json_array|json_object )
string Json.iterKey ( long json_object, long iter )
long Json.iterNext ( long json_array|json_object, long iter )
long Json.iterValue ( long json_array|json_object, long iter )

JSON read/write functions
long Json.read ( long stream, ref string error_str, [long options] )
long Json.readFile ( const string path, ref string error_str, [long options] )
long Json.readString ( const string json_str, ref string error_str, [long options] )
long Json.write ( long json_value, long stream, [long options] )
long Json.writeFile ( long json_value, const string path, [long options] )
long Json.writeString ( long json_value, ref string str, [long options] )


Regards

Thanks, it's really useful. But did you write this documentation yourself? Why else can I ask you where did you find it? these days I have searched a lot online but I have not found anything.

Hello,
search at Infor support:

Welcome
The Infor ES Programmers Guide provides the latest information about the Infor ES 3GL and 4GL
programming languages, including reference material and in-depth articles on several aspects of design
and development. You can navigate via content listings or use the index to look up keywords.
Via the search page you can enter specific queries.


Should be found at Infor Consierge Support Site

Regards