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

Advantage Help > Business Premium License > Run SQL.

Execute SQL Statements (Business Premium License)

With the Premium Business License, you can prepare and execute SQL statements. Organizer Advantage uses the Firebird SQL database system.

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

Organizer Advantage, Business Database Solutions

Organizer Advantage Free Service

Organizer Advantage Support

Database Blog:
Compare Windows over Cloud App
Custom Wine Inventory Database