Developer ToolsFree generatorPDF, Excel, Word exportBaan IV / Infor LNAll Infor LN and Baan tools

Infor LN and Baan 4GL Embedded SQL Select Generator

This generator writes Baan IV and Infor LN embedded SQL for 3GL and 4GL scripts: table declarations, a select with host variables, index ordering and a full selectdo, selectempty, selecterror, endselect loop. It also gives a join variant and a call example, so you can paste it into a UI script, DLL or DAL in Program Scripts / Libraries (ttadv2530m000).

Fill in

Main table (logical code)

Logical table code without the leading t, e.g. tdsls401 (sales order lines).

Columns to select

Where clause

Use :variable for host variables declared in the script.

Function argument (host variable)

Order by

Lock rows

Row limit

Join table (optional variant)

Join condition

Columns from join table

Generated code

Download:

Declaration

|******************************************************************************
|* Embedded SQL on tdsls401
|******************************************************************************
declaration:

	table	ttdsls401		| main table
	table	ttcibd001		| only needed for the join variant

Select loop in a function

functions:

function long read.tdsls401.rows(domain tcorno i.orno)
{
	long	row.count

	row.count = 0

	select	tdsls401.orno, tdsls401.pono, tdsls401.item, tdsls401.qoor
	from	tdsls401
	| read only, no lock
	where	tdsls401.orno = :i.orno
	order by tdsls401._index1
	| all matching rows
	selectdo
		row.count = row.count + 1
		| the selected columns of tdsls401 hold the current row here
		| with for update: change fields, then db.update(ttdsls401, db.retry)
	selectempty
		| no row matched the where clause
		row.count = 0
	selecterror
		| the query failed, log or report it
		row.count = -1
	endselect

	return(row.count)
}

Variant with a join

function long read.tdsls401.with.tcibd001(domain tcorno i.orno)
{
	long	row.count

	row.count = 0

	select	tdsls401.orno, tdsls401.pono, tdsls401.item, tdsls401.qoor, tcibd001.dsca
	from	tdsls401, tcibd001
	where	tdsls401.orno = :i.orno
	and	tcibd001.item = tdsls401.item
	order by tdsls401._index1
	selectdo
		row.count = row.count + 1
		| tcibd001.dsca is filled for the current tdsls401 row
	selectempty
		row.count = 0
	endselect

	return(row.count)
}

Calling it

| for example in a choice or field section of the session script
	long	lines.found

	lines.found = read.tdsls401.rows(tdsls401.orno)
	if lines.found <= 0 then
		| nothing to process
	endif

Check the generated statement against the SQL programming chapter of the Infor ES Programmer's Guide for your Tools version. The lock and row limit options insert either the clause or a comment line, so the statement stays valid either way. With for update, commit with commit.transaction() in a UI or 3GL script, but never commit inside a DAL hook. The example function argument and the call assume an order number domain; change both if you change the where clause.

Get your LN queries reviewed

Send us a slow or failing embedded SQL statement and your LN and Tools versions, and we will suggest an index-friendly rewrite.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Enter the logical table code (tdsls401, not ttdsls401100), the columns you need, and a where clause that uses host variables with a leading colon, such as :i.orno. Pick whether rows are locked for update and whether only the first row is read. The output declares the table with the t prefix, wraps the select in a function that returns the row count, and adds a join variant against a second table such as item general data (tcibd001). Delete the variant you do not need. Select only the columns you use: a narrow column list is cheaper, and columns you did not select are not filled in the record buffer inside selectdo.

Declare every table in the select with table t<code> in the declaration section
Host variables are script variables or function arguments prefixed with a colon
order by <table>._index1 orders by the table's first index

Embedded SQL versus database SQL

Embedded SQL uses logical LN names. The database driver translates them into physical names: on SQL Server or Oracle, tdsls401 in company 100 is the table ttdsls401100 and the column orno is t_orno. That is why an ad hoc query in SQL Server Management Studio looks different from the 4GL code, and why you should not copy t_ names into a script. Embedded SQL runs in the current company of the session; a query across companies has to say so explicitly, and you should test that against your own version before relying on it. References between tables (for example item on the order line pointing to tcibd001) are always expressed with logical table and field names.

Worked example

With the defaults the generator reads all lines of one sales order: select tdsls401.orno, tdsls401.pono, tdsls401.item, tdsls401.qoor from tdsls401 where tdsls401.orno = :i.orno order by tdsls401._index1. Inside selectdo, tdsls401.qoor holds the ordered quantity of the current line, so you can total it or test it. If the order has no lines, selectempty runs once and the function returns 0; if the query fails, selecterror runs and the function returns -1. Switch the row limit to as set with 1 rows when you only need to know whether a line exists, which avoids reading the whole set.

Common mistakes

Forgetting the table declaration gives a compile error on the select. Using for update without a following db.update and commit leaves rows locked until the transaction ends. Calling other database actions on the same table inside a long selectdo loop can disturb the set you are reading; collect keys first if you must update many rows. Writing a where clause on non-indexed fields over a large table such as tdsls401 forces a full scan, so filter on the leading fields of an index when you can. Finally, never commit inside a DAL hook: the DAL runs inside the caller's transaction.

Frequently Asked Questions

What is the difference between selectdo and selectempty in Baan 4GL?

selectdo is the body that runs once for every row the select returns. selectempty runs once when no row matches. selecterror runs when the query itself fails. The loop closes with endselect. Put row processing in selectdo and the not-found handling in selectempty instead of testing a counter afterwards.

What does as set with 1 rows do?

It limits the result set to one row, so the database stops after the first match. Combined with order by on an index it gives you the first or, with descending order, the last record by that index. Use it for existence checks and lookups instead of reading a full set and breaking out of the loop.

Why do column names start with t_ in SQL Server but not in my script?

LN stores each logical table as a physical table named t, the table code and the company number, for example ttdsls401100, and prefixes every column with t_. Embedded SQL in scripts uses the logical names and the driver translates them. Only direct database queries outside LN use the physical names.

Is this useful for Infor LN technical training?

Yes, as a starting point. New LN technical consultants usually learn the select loop first because it appears in nearly every UI script, DLL and DAL. Generate a few variants, compile them in a test VRC and step through them with the debugger to see when each section runs.

If your team wants to ask LN about sales orders and items in plain English, with the same logical tables and roles, we can run that on your own servers.

Talk to an Expert Explore More Free Tools