|  Organizer Advantage HowTo/Help  |  Designer HowTo/Help  | 

Advantage Help > Business Premium License > Run SQL.

Run SQL Commands

This page explains how to run SQL commands and queries within the Organizer Advantage program.

Available with the Business Premium License, Run SQL lets you prepare and execute Firebird SQL UPDATE statements directly against your database — useful for complex data fixes that go beyond standard replace tools. Includes two practical examples: restoring quote characters after a CSV import, and building a call number field by combining values from two other fields. Always back up before running SQL commands.

Exercise caution when running SQL statements, as you could accidentally modify or delete all records in your library databases. Do not use any commands that change the structure of the database, such as adding or deleting fields, renaming field names, or creating or deleting tables.

Before running any SQL statement, creating a backup file is recommended. The Run SQL Statement function supports the following:

• Preparing an SQL statement and executing it
• Defining an SQL statement and saving it for future use
• Select an SQL statement from the list and execute it.

Example 1

A spreadsheet contained many single and double quote characters, which complicated the data transfer process to our software. Since single and double quotes are commonly used as data separators and delimiters, they should not be present in the data. The single and double quotes were substituted with the text strings "SINGLE_Q" and "DOUBLE_Q" correspondingly. Subsequently, the data was transferred to the Advantage system, where the 'Run SQL' feature was utilized to restore the data back to its original format.

UPDATE ART_INVENTORY SET SHORT_NOTE = REPLACE(SHORT_NOTE, 'DOUBLE_Q', '"');
UPDATE ART_INVENTORY SET SHORT_NOTE = REPLACE(SHORT_NOTE, 'SINGLE_Q', ASCII_CHAR(39));

Notes:
ASCII_CHAR(39) is used, the single quote character is reserved for strings.
; (semicolon) at the end of the line defines the end of the command.

Example 2

In this example, the user must create a value for the CALLNO field in the BOOK_LIBRARY database (combining the three letters of the last author's name (AUTHOR) with the section (SECTION).

UPDATE BOOK_LIBRARY SET CALLNO = SECTION || ' ' || SUBSTRING(AUTHOR from 1 for 3);

Book Library database; the call number data field with new values:

Book Library database; spine labels use the value from the call number data field:

Organizer Advantage, Simple Database Solutions for Windows PCs

Organizer Advantage, Business Database Solutions for Windows PCs

Organizer Advantage Free Service

Organizer Advantage Support

Database Blog:
Advantages of Database Software over Spreadsheet Applications
Firebird SQL Tutor
Custom Wine Inventory Database

Free Database Templates