ProLinga

This will be the logic where will test if a value exist in the database. If it exist then show all the details on the screen, so they can be modified and if it does not exist, provide a blank data entry screen for a new Device.

The mechanism here is the we execute an SQL statement that returns with a DataModel (sometimes also referred to as 'Cursor' or 'ResultSet' in other environments). This DataModel is populated automatically, so we do not have to design them first setting column names etc. The only thing that is required that the DataModel as an object is available in ProLinga.

To create a DataModel, logon to the ProLinga Developer and select "DataModel" from the Object Palette.

Figure 92. Data Model On Object Palette

Create a new DataModel and save it as "Device". Leave the setting "AutoClear" as it is.

Figure 93. DataModel Device

Further we need a setting so we can keep track if a Device record was present or not. We need to know when updating if we need to trigger an "insert" or "update" action. The easiest way to keep track of this is by using a (global) Variable object called isPresentDevice.

Before we can create this Variable "isPresentDevice", we first need to create the underlying data dictionary "isPresentDevice". Create a this data dictionary of type "Boolean" and length of 5 (so it can hold the values True and False. Since we have created Data Dictionaries before, only the end result is shown below:

Figure 94. Data Dictionary isPresentDevice

To create the Variable "isPresentDevice" open the Variable Editor in the ProLinga Developer.

Figure 95. Variable On Object Palette

Enter the just created Data Dictionary "isPresentDevice" and set the default value to "False". Save the Variable with the name "isPresentDevice".

Figure 96. Variable isPresentDevice

Everything is now present to write the logic "DeviceRead".

LET V-isPresentDevice = FALSE()

COMMENT --- No validation on empty key
IF F-deviceNumber.Device = 0 
	RETURN
ENDIF

CLEAR DATAMODEL Device
DATAMODEL SELECT Device TABLE Device CONDITION "where \"deviceNumber\" = '" F-deviceNumber.Device "'"
IF SQLSTATUS() <> 0
	ERROR "Can not read Device."
	RETURN
ENDIF

IF SQLRETURN() = 0
	LET V-isPresentDevice = FALSE()
	LET L-deviceNumber = F-deviceNumber.Device
	CLEAR RECORD Device
	LET F-deviceNumber.Device = L-deviceNumber
	DISPLAY ALL
	MESSAGE "New Device can be inserted."
	FOCUS entryDescription
ELSE
	LET V-isPresentDevice = TRUE()
	FOREACH Device
		LET R-Device = O-Device
		BREAK
	ENDFOR
	DISPLAY ALL 
	MESSAGE "Device can be updated."
	FOCUS entryDescription
ENDIF

At first this may look complicated to you, but if you look a bit closer, you will recognise that we already used most of the ProLinga logic commands in previous examples. Some commands here do need some additional explanation.

We prevent the user from using the value 0. In that case an error message will be triggered.

The DATAMODEL SELECT command executes a "Select * from" statement on a table in the database and will place the result in an updatable DataModel Device. Notice in this example the " around the column names and the " around the column names and ' around the values. This is the only place where the underlying database determines how to construct your SQL, mainly because most databases have their own SQL flavour. In this example we are using the PostgreSQL database. If you are using MySQL then you should surround the column names with ` and the data with ". Knowledge of the flavour of SQL you are using is required here.

At the end of the command, you see "DATAMODEL Device". This means that if the query produces any results, load them into the DataModel "Device" that we created a little bit earlier.

A little but further in the we test for SQLRETURN(). If this is set to 0 that means that a Device with the requested Device Number does NOT exist. In that case we clear the table buffer and the user can type the details of the new Device to be added. In case SQLRETURN() is set to a value, that represents the number of rows the query returned.

With the FOREACH ProLinga command, all entries in a datamodel can be "walked through". Since we are only interested in the first entry (and only entry), we can use BREAK to break out of the loop after the first read. With the command LET R-Device = O-Device, the current DataModel entry can be assigned to the table buffer, so we can use it to reference the fields.

Carefully study this logic. It may look difficult at first, but after analyzing it, it is not that diffult

Since the screen "Device" is called from the logic "Device", it is wise to initialize the Variable "V-isPresentDevice" in this logic as well. Open your logic "Device" and change it to this:

CLEAR TABLE Device
LET V-isPresentDevice = FALSE()
LET F-dateInstall.Device = DATE()
SCREEN Device CONTAINER frame_main SOURCE device_include 
DISPLAY ALL
MESSAGE "Device can be updated."
FOCUS entryDeviceNumber

Notice the new second line.

You can run your application now, to see if you do not get any unexpected errors. Since the table in the database is still empty, you will not see any data yet.