This scenario describes a job which updates an SQLite database file based on a prepared statement and using a delimited file.
Drop a tFileInputDelimited and a tSQLiteRow component from the Palette to the design workspace.
On the tFileInputDelimited Basic settings panel, browse to the input file that will be used to update rows in the database.
There is no Header nor Footer. The Row separator is a carriage return and the Field separator is a semi-colon.
Click the [...] button next to Edit schema and define the schema structure in case it is not stored in the Repository.
Make sure the length and type are respectively correct and large enough to define the columns.
Then in the tSQLiteRow Basic settings panel, set the Database filepath to the file to be updated.
The schema is read-only as it is required to match the input schema.
Type in the query or retrieve it from the Repository. In this use case, we updated the type_os for the id defined in the Input flow. The statement is as follows:
"Update download set type_os=? where id=?".
Then select the Use PreparedStatement check box to display the placeholders' parameter table.
In the Input parameters table, add as many lines as necessary to cover all placeholders. In this scenario, type_os and id are to be defined.
Set the Commit every field.
Save the job and press F6 to run it.
The download table from the SQLite database is thus updated with new type_os code according to the delimited input file.