Review and Edit the SQL That ASQL Creates

Expert mode adds two buttons to ASQL, Show Script and Try Again, so you can see the SQL that ASQL writes for a file and change it before it runs. It also shows the Reserved Keywords list and a Generate Foreign Keys box, and the tree starts at Accessible Files. An ordinary import doesn't need it. ASQL also turns on Show Script, Try Again and the keyword list by itself after an import fails.

To turn expert mode on, close ASQL and run these two commands in Command Prompt on the computer where you use ASQL:

set "ASQL_EXPERT_MODE=1"
"C:\Program Files\AppxODBCDriver\asql.exe"

When you are done, close ASQL and clear the setting:

set "ASQL_EXPERT_MODE="

ASQL only checks whether the variable exists, so even a value of 0 turns expert mode on. The setting belongs to that one Command Prompt window and the ASQL started from it. It doesn't affect the gateway, the APPX login manager or anyone else's ASQL.

What each button does with the script

So a change in the pane only counts if Try Again is the next button you choose. Choosing Show Script or Import Table after an edit throws the edit away.

When a folder is selected instead of a file, the buttons read Import Tables and Drop Tables and act on every file in that folder.

Rename a column before it is created

Use this when an APPX field name can't be used as a SQL column name, or when you want a different name. The example assumes a field named NUMBER, which every edition refuses with -20003 Syntax error. See SQL Name Rules and Reserved Words for the rules and the list of refused words.

1. Select the file in the tree. If it already has a SQL table, choose Drop Table first.
2. Choose Show Script. The pane shows a CREATE TABLE statement, a CREATE INDEX or CREATE UNIQUE INDEX statement for each APPX key, and a CREATE PUBLIC SYNONYM statement.
3. Copy the whole script into Notepad and save it with your records.
4. Change the name everywhere it appears, including in the index statements. For example, change number to number_txt. Keep every column in the same place with the same type. The gateway matches columns to APPX fields by position, not by name, so a renamed column still reads the right field.
5. Choose Try Again. When every statement works, the status line reads Ok and the pane shows only the last statement, CREATE PUBLIC SYNONYM ....
6. Check the table with a query, for example in WinSQL. Don't rely on the pane or the icon in the tree.

When a statement fails, the error appears on the status line, and a box titled Try Again Failed opens with the message OnTryAgain() import_script() failed. Choose OK to close it; the real error is the one on the status line.

Why a change can seem to disappear

• After a successful Try Again, choosing Show Script or Import Table shows the APPX names again, but the SQL table keeps your names. Import Table also stops with -20041 Table/View/Synonym already exists and puts a red exclamation mark on the file, because the table is already there.
• When Try Again fails, only the failed CREATE TABLE statement is left in the pane. Fixing the name there and choosing Try Again creates the table, but not its indexes or its public synonym. Queries still work, so this is easy to miss. To finish the job, choose Drop Table and start again at step 2.
• The icon next to a file shows the result of the last Import Table or Drop Table for that file: a check mark when it worked and a red exclamation mark when it failed. Try Again never changes the icon.
• A later Import Table for the same file, for example after the APPX file changes, builds the APPX names again. Keep the edited script so you can make the same change again.

Older instructions, including the FAQ answer Is it possible to change the field names that are used by APPX/ODBC?, say to delete comment lines and the last semicolon before choosing Try Again. That isn't needed here: the script has no comment lines, and Try Again skips the empty end after the last semicolon.