Database Connection Details

Database Vendor

The type of database we wish to communicate with. This will be set automatically based on the type of database we chose in the right-click menu.

We can change this at any time of course, but please be aware that dong so means we must take careful note of the values we set for the Database Field Details block.

Important! It's very important to note that by default the SQL+ Automator, if not provided with a Database Host Name, will attempt to use the same connection properties we set when RackForms was installed.

This can and will lead to problems if we set the database driver to anything other than your default database driver without also setting full connection properties. In other words, if you installed RackForms using the MySQLi extension, setting the Database Vendor to PDO will cause the SQL+ Automation process to fail unless we also set full and proper values for Database Host Name, Database User Name, and Database Password.

This restriction may be relaxed in future versions, but for now, please know that if we set the Database Vendor to anything other than the one we installed RackForms with, we must set full connection details.

* Table Name

An important and always required field, this tells the SQL+ creation process what we wish to call the table we create.

Database Host Name

If left blank the default database RackForms was installed to is used.

Of course we can change this to anything we choose, but if so, we must also supply a database user and password, and possibly other details such as port.

* Database Name

The second always required field, this tells RackForms what the name of the database we wish to communicate with is.

Database User Name

The user name of the database we wish to connect to. Can be left blank if Database Host Name is left blank.

Database Password

The password of the database we wish to connect to. Can be left blank if Database Host Name is left blank.

Database Socket

The socket of the database we wish to connect to. Can be left blank if Database Host Name is left blank.

Database Port

The port of the database we wish to connect to. Can be left blank if Database Host Name is left blank.

Database DSN

Used for ODBC data sources.

Database Connector File

If supplied, defines the connection details RackForms uses for the database connection in the form of an external file.

The path of this file must be relative to the job folder, that is:

RackForms/output/forms/job-name

Where job-name is the name of the job currently being used.

Database Field Details

Once we have specified, at minimum, a Table Name and Database Name, we can modify, if needed, the form elements that will be used in the SQL+ creation process.

It's important to note the items in this list come from the full form page of items if no selection was made, or from a specific subset of items if in free form mode and we lasso selected a group of elements.

In both cases RackForms will take our selection and add a row for each item. RackForms attempts to make intelligent assignments for the values of these fields, and in many cases they can be left as is.

That said, it's important to understand that each row will become a database column. As such, we have several options we can set to dictate the behavior and properties of each field when converted to a database field.

Please see the listings below for a more detailed description of each option.

Primary

This designates the field as a Primary Key. Tables can only have one primary key at a time.

Generally speaking, a good way to create a primary key field is to add a hidden field to a form. This hidden field can then be renamed and defined as the primary key, and if needed, deleted from the form page when done with the SQL+ Automation process.

Of course a table need not have a primary key, but it's strongly suggested that it does!

Database professionals should note that when a field is designated as Primary, it can no longer, nor could it, participate in an UPDATE operation.

In addition, any field marked as Primary and Auto-Increment is never INSERTED, and also never UPDATED.

Thus, it pays to read up on database theory and design if you're not familiar with some of the basic concepts, as the more we know the better and more secure our database will be/

Auto-Increment

Generally used in conjunction with the Primary Key field, if set this field will increment it's value by 1 on each new INSERT.

As stated above, any field set with auto-increment will never participate in an UPDATE or INSERT operation, as by definition this column's value comes from the database, not the form user.

Field Name

An important field, this will become the column name in the database.

In many cases we've already set meaningful values in the RackForms editor, but if not this item features a powerful time-saver: any change we make here will update the actual form item's ID and name property.

This means if we've missed naming a form field item it's very simple to set a proper value here.

Of course we do not need to change these values, but it's strongly advised that we do. Creating meaningful values means the database will make much more sense in terms of human readability.

Database Data Type

Another very important field, this item tells RackForms what the data type of the column will be.

By default RackForms creates sensible defaults when the SQL+ Automator first opens, but this by no means is a final list we need to stick by.

Specifically, the rules are

• All fields get assigned VARCHAR(255) unless a validation limit is set fort the field, at which point the Precision field gets that value.
• All text areas are assigned TEXT for MSSQL and LONG TEXT for MySQL.
• All file elements are assigned that database vendors binary format.

Another very important point is we have several sub-sections of data types: MySQL, MSSQL, and Access. Please be aware that the database being used must match the data type options we select. For example, we cannot use LONGTEXT, a MySQL data type, for MSSQL. We would instead use TEXT.

Of course we should note when we first load the SQL+ automator RackForms handles these values for us. Also, some data types, like VARCHAR and INT, overlap. Despite this, we should always use the proper values for the database type we've selected.

Precision

This field sets the precision, or length, of the field in question. For text elements this is how many characters the field will accept, for numbers the numerical accuracy.

Generally speaking, and as noted above, all text, password, radio, and select items are assigned VARCHAR(255). This is fine for most uses, but a good rule to stick by is to make these fields a little more sensible by saying, for example, a firstname field would be VARCHAR(50).

Of course if the field can be much longer, we should use a larger number or a TEXT/LONGTEXT format.

As with all databases, little quirks and idiosyncrasies can get us into trouble if we're not careful. For example, MSSQL does not accept a precision setting for int fields.

Please also note that if you switch a field from say, TEXT to VARCHAR, you will need to supply a precision value or the SQL creation process will fail.

Allow Nulls?

This setting defines NOT NULL in the column specification.

Default Value

Setting a value here means that on every INSERT the column will receive this value if no other value is provided.

Important! At this time RackForms does not support default values for datagrid items when using MSSQL. If you must use default values for datagrid items, please use MySQL instead.

Is WHERE

This is a very important field for processing when using the UPDATE Actions, as described below and in this section.

Specifically, if we wish to have an UPDATE action we must first define what row gets the update. The way we do this is to tell RackForms the field marked as the WHERE column will provide this link.

That is, lets say we have a database table with two fields:

id

name

id would be an INT field that has Primary, Auto-Increment, and Is WHERE checked. Its data type would be INT.

name would simple be a VARCHAR(50).

Knowing that id is an INT that auto-increments and is the Primary Key means it's a good candidate for being the WHERE field, as by definition this value will always be unique--a key attribute of any UPDATE operation.

Thus, we would check the Is WHERE box knowing that by doing so any SQL+ modules created by the SQL+ Automator will use the id field as the link between the record in the database and the row being updated.

Database And SQL+ Creation Details

Confirmation Page To Write SQL+ Items To
In this box we define which confirmation page SQL+ items are created too, if any.

By default we leave this box at the first confirmation page found, though this may not always be appropriate. Thus, we can chose which one via this option.

Table Only Actions

This action simply creates the database table as defined by our Database Field Details. This is handy for quickly creating tables, but the real power of the SQL+ Automator comes from the next few items...

INSERT Actions

For each of the next few items we have four options:

SQL+ Insert Item

This option creates an SQL+ item on the confirmation page of the job. At very minimum, this saves us from having to write any SQL code. However, it does require the table set in the Table Name option exists, and that the column names match.

Table And Add SQL+ Insert item

This option creates an SQL+ item, plus the database table it needs to write records for a single page in your job. The key here is unlike the next two options, this option only creates SQL+ items and database tables for the currently selected page.

This can be handle for those times when you want maximum control over your database creation process.

Entire Job + Index Table

This option creates a database table for each form page in your job, along with an index table to join them together. The table names are incremented with a numeric _n for each form page. The index page takes the value we define in Table Name and appends _index. The key field we use to join tables is defined as: entry_id.

The downside to this approach is we do not have control over the field data types, as RackForms infers the types automatically. However, we can certainly go in to our database after the fact and modify fields accordingly. By default, RackForms assigns most text fields with VARCHAR(50), text areas with TEXT, and data fields as DATETIME.

The other consideration is this option creates one database table for each form page in the job. This means a job with relatively few fields, such as 30, would gain a bit more complexity than may be needed. This is because we need to JOIN the page tables together at a later time.

However, for larger jobs this is usually preferable, as we keep table column counts lower this way.

Entire Job Single Table

This option creates a single database table and SQL+ item for all pages in your job. This can be very handy for smaller jobs, though may cause issues with forms with more than 50 or so fields. This is because now we'll have a database table with that many columns, which can often be a burden to work with.

However, this is often offset by the fact that a single database table for each form entry is much easier to work with than several.

 

UPDATE Actions

In some cases we may instead wish to update an existing table. While we already have the Builder toolset for this purpose, in many cases having the full RackForms form element set at our disposal is a better, more appropriate solution.

Thus, for such jobs we have this item, which when clicked, creates an SQL+ item that will UPDATE the specified table.

It's important to note that as described above, in order for this process to work we must provide a suitable Is WHERE column. This is because in order for us to update a table we must know which row we wish to update.

Setting a proper column as the Is WHERE field means RackForms will create UPDATE code using the Is WHERE column for its WHERE clause.

Combo Actions

Combo actions are used to combine INSERT and UPDATE actions into one logical block.

This is very useful for times when we have a form that's part of a larger whole, where we have a main database table, say, dealers, that connects to a smaller table, inventory.

In our hypothetical form application we start by selecting a dealer which has known data, then jump into a form for managing inventory. Trick is, updating inventory is a very common operation, but so is adding inventory. Thus, instead of creating two different forms we just create one.

Normally this would create a problem of how do you know when to INSERT vs. UPDATE. Not so with Combo Actions!

If the dealer enters a piece of inventory that doesn't already exist in the system, a Primary Key of say, vehicle VIN, will tell the UPDATE/INSERT block RackForms creates, via a conditional code statement, that no record exists in the database with that VIN. Thus, it runs the INSERT SQL+ item.

If a matching VIN is found, then RackForms will run the UPDATE SQL+ item instead.

All of this is done for you when using the Combo acton.

Of course this is all dependent on having a logical data structure where we have unique keys and again, proper settings for the Is WHERE field.

The best way to learn more is to set up a simple job that has two fields, and id and name, must like the example above.

Try each of the elements out to see how they work!

Field Population Actions

Field population actions aim to significantly reduce the effort involved when creating forms that populate existing form fields. While RackForms has an existing framework for updating existing records (Builder Pages), these pages are not meant to handle large numbers of fields, nor are they designed to allow for complex form interactions, such as processing dynamic values, using validation, or allowing more advanced fields such as signatures or file uploads.

The field population items aim to address these shortcomings, though a careful process must be followed to get the most of this feature. In summary the aim of the Field Population module is simple: automate the process of creating forms that populate fields with existing values, and then update those fields upon submission.

Field Population Process

The first step in setting up a field population form is to note this process is meant to run opposite a standard INSERT form, as in a form that collects new data. By contrast these forms should be copies of an input form, one that has no SQL+ or other database INSERT items present.

Once we have our form copy it's important to ensure the database column name and form input field name be identical. RackForms will automatically name fields in this manner when using other SQL Automator tools, though if you've created the database table by hand or in another tool, please note we'll need to match all column names exactly to the field names they map to.

With a form copy in hand and all field names matching their database columns, the next step is to determine how we'll populate the forms initial page. To do so we must provide either a session variable or a GET variable with the key value pair: id = pk value. That is, a PHP session element that's been assigned a valid row identifier in the form of: $_SESSION['id'] = 1, or a query string appended to the first page of your form as in: https://yourform.com/forms/output/form/index.php?id=1

Technically, the session or query string item tells RackForms which record to pull from your database when the form first loads. It's critical to note their is a security issue here, in that the default code that's generated creates a query that accepts GET values as well as the far more secure SESSION. Because of this we'll need to ensure this form is either locked down behind a login of some sort or in incredibly rare cases, has data that we're ok with being accessed and modified by the general public. Alternatively, we can modify the SQL Query item on the first page of the form to not accept GET values:

SQL Code: SELECT * FROM unit_sql_automator_populate WHERE id = ?
SQL / Mongo Variables: S{id}

In general, in almost no cases will a form provide public access for pulling and updating existing records, such forms should always be behind a strong login system. RackForms provides a sample job just for this purpose under the Load Example Job menu: login-form-mssql and login-form-mysql. We also support CAS authentication, as well as basic user name and password authentication. These systems can be found under Page Attributes > Form Security.

A few other important rules should be followed when creating Population pages:

• The entire form submission must use a single database table.
• File uploads left blank will overwrite previously uploaded items with an empty value.
• Signature fields will not display existing signature items.

Status Messages

Populate All Pages