SQL scripts

Swisslog has a very powerful function to run SQL Scripts which allows the execution of complicated queries to modify fields or move data from one field into another. In this way we avoid using complicated operations in the logbook view. Though, SQL scripts are executed in seconds and can perform complex operations that can't be done with the available functions provided by Swisslog.

I have created some scripts which you may find them very useful. I'm not a SQL expert at all but with help of my knowledge in Access and a special view to see the SQL code created in queries I could create it with success.

To run the scripts perform the following steps:

1. Start SWISSLOG
2. Open Tools | For experts only | Run SQL Script
3. Select the desired script file which are found in the SCRIPTS folder (is located as a sub-folder where Swisslog is installed). Press OK to all confirmation messages.

VERY IMPORTANT: Due to the amount of checkings and/or data changes some of these scripts may handle, this operation may take some time. It depends on your computer speed and the number of QSOs in your database. NO PROGRESSION BAR is displayed! You must wait until you see the message "SQL script_filename.sql successfully executed" before doing anything else in Swisslog.

SQL scripts:

DXCC - WAE Corrections.sql

NOTE: This script is executed automatically in invisible mode at first Swisslog startup of version 5.9a. You don't need to execute it again if using version 5.9a or higher. It's only explained so that users know what this script performs.

Up to now every time the prefix allocation table of certain entities was changed, a very complicated manual operations should be performed to adapt your QSO to the new situation. Thanks to the powerful SQL script function this operation can be now automatically made in just seconds!

I have created a script which automatically adapt the prefix allocations previously explained by Hardy, DL5SBA (SK) (Montenegro, Cook Islands, Bosnia Herzegowina, Juan de Nova and Europe, Glorious, Tromelin, South Sudan as well as other changes) safely, automatically and in just seconds!!. In this way we avoid using the manual procedures explained up to now which could cause errors by the user. Best of all is that even if you previously performed the manual operations, you can now run this script without any problem! If all the operations were done well nothing will be changed. Otherwise the script will correct it!

This script perform the following changes:

Set direct in QSO without bureau.sql

This script is very useful to avoid sending a QSL via bureau to those QSO partners with no bureau service. It selects all QSO with entities with no bureau service (having the QSL Manager field empty, no QSL has been send/received and QSL action is different from LOTW, LOTW+eQSL and eQSL) and sets "Direct" in the QSL-Action field. If a QSO contains a QSL Manager, because the QSL could be sent via bureau to the manager, it's out of the script selection.

If you want to change "Direct" by another word simply edit this file with a text editor (Notepad for example) and change "Direct" in the first line of code (is marked boldface in the code below):

UPDATE (PQTH INNER JOIN LOGBOOK ON PQTH.P_CALLID = LOGBOOK.L_CALLID) INNER JOIN DXCC_Tab ON PQTH.P_DXCC = DXCC_Tab.DXCC_ABBR SET LOGBOOK.L_QSL_ACTION = "Direct" WHERE (((LOGBOOK.L_QSL_ACTION)<>"LOTW" And (LOGBOOK.L_QSL_ACTION)<>"LOTW+eQSL" And (LOGBOOK.L_QSL_ACTION)<>"eQSL") AND ((LOGBOOK.L_QSL_RECEIVED)=0) AND ((LOGBOOK.L_QSL_SEND)=0) AND ((LOGBOOK.L_QSL_MGR) Is Null) AND ((DXCC_Tab.BUREAU)="N"));

NOTE: From version 5.94 Swisslog performs this automatically when saving new QSO. If the membership panel is activated and the "Apply QSL Actions from Membership" is checked, DIRECT will only be set if not LOTW/eQSL user.

QSL managers.sql

NOTE: This script is executed automatically in invisible mode at first Swisslog startup of version 5.9a. You don't need to execute it again if using version 5.9a or higher.

This script automatically compares your QSO with an updated list of nearly 3000 stations handled by the following QSL managers: EA1EAU, EA5GL, EA5KB, EA7FTR, EB7DX, IZ8CCW, IZ8CLM, M0OXO, N2OO, UA4WHX, RW6HS and W3HNK. Then the script will set the QSL manager in all matching QSO ONLY if you have not received the QSL card (R-QSL field is 0) and the QSL manager field is empty. Keep in mind that it may take some time.

LOTW users.sql

To avoid sending a QSL card to LOTW users, this script sets the word LOTW in the QSL-Action field in all QSO where a QSL has not been received. Make sure you have updated the LOTW user list before executing this script. You can update it manually in Tools | Update awards information (internet) | Update LOTW user list. In the Print QSL wizard you can avoid selecting these QSO to print your QSL cards.

NOTE: If the membership panel is activated and the "Apply QSL Actions from Membership" is checked, this is done automatically when adding new QSOs if callsign is LoTW user.

Clean special field.sql

If you use the WFF (World Flora & Fauna) award you must run this SQL script to clean the contents of the Special field ONLY ONCE AND BEFORE entering any WFF references. This has to be performed, specially those users from the DOS version, because the SP field may contain obsolete data currently.  

Set WADA.sql

Sets the WADA reference to all callsigns matching the new internal info table INF_WAPWACA. If you work this award you MUST run this script from time to time. Keep in mind that a same callsing may have been active from several WADA references. In these cases the script will set the first coincidence. User will have to check manually the reference with the QSL.

Convert IIA.sql

NOTE: This script is executed automatically in invisible mode at first Swisslog startup of version 5.94. You don't need to execute it again if using version 5.94 or higher.

The Italian Island Award (IIA) changed all references from 1st January 2013. This script converts all old references into the new format.