When one user cannot use OSFAX but others can

This post describes how you can fix the problem where a single user is unable to use OSFAX, but other users are OK. If the user tries to send a document using 'FAX/Assist' they will get an error saying 'Overlay not found'. This is because OSFAX rejects the user when the program does a check to see that the required fax overlay is set up. If the user tries to send directly through OSFAX they will get an error saying 'Already exists'.

The problem occurs because OSFAX stores user details by both username and unix-id, and either the username or unix-id for the user has been used previously and OSFAX is blocking the new username/unix-id combination. This sometimes happens when a user leaves a company and they are removed from the password file, then they return and are added with the same username but they get a new unix-id.

The solution is to remove the old user information from the OSFAX database. To do this, open the OSFAX browser using the following command as superuser at the shell prompt: 

    $FAXDIR/bin/osfax

Then do the following:

1. Press the F4 key to display a pop-up list.
2. Arrow down to the word 'Users' and press ENTER.
3. Arrow down to the word 'name' and press ENTER.
4. Arrow to the username for the user that cannot use OSFAX and press ENTER. (See below if the username is not shown.)
5. Arrow down to the bottom line of the screen. The screen will scroll up a few lines as you do this.
6. Arrow across to the word 'Delete' and press the 'R' key.
7. Press the 'OK' button in the confirmation box.
8. Press the HOME key several times to exit the OSFAX browser and return to the shell prompt.

The user should now be able to use OSFAX normally.

If there is no entry for the username for the user that cannot use OSFAX, then it is the unix-id that has been re-used rather than the username. This is unusual as unix-ids are not normally re-used. In this case, you first need to know the unix-id for the user. You can check this using the command:

    grep username /etc/passwd

The unix-id should be the third field (the first numeric field) on the line that is displayed. To remove this from the OSFAX database, use the same commands as listed above, but replace lines 3 and 4 with the following commands:

3. Press ENTER on the word 'uid'.

4. Arrow to the unix-id for the user that cannot use OSFAX and press ENTER.

How to set up OSFAX to send an email to more than one address

If you are using OSFAX to send out PDF documents such as customer statements, sometimes the customer will ask you to send them to more than one person. This posting explains how you can tell OSFAX to do this.

The best method is that the customer sets up a generic address that you send to (such as 'accounts @company.com.au') and then sets up their own email server so that any mail received at this address is sent to the required people. If they set it up internally, the customer does not need to advise you when staff change jobs, leave or go on holiday.

Assuming the customer cannot or does not want to make changes at their end, you can do it within OSFAX using the OSFAX browser. If you have administration privileges in FAX/Assist, you can go to the OSFAX browser from the FAX/Assist administration menu. If not, you can open the OSFAX browser using the following command as superuser at the shell prompt:
    $FAXDIR/bin/osfax

In this example, we will use the email address 'multi@company.com.au' within Pronto, and tell OSFAX to send emails instead to both 'fred.smith@company.com.au' and 'mary.jones@company.com.au'.

In the OSFAX browser, use the arrow keys to move the cursor down to the line beginning with the word 'redirect:', then move to the right to put the cursor on the word 'ADD' and press the enter key. This will bring up a new screen like this:

Enter the email address that Pronto will print on the document to be sent in the 'from:' field, and the email addresses you really want to send to in the 'to:' field, separated by semi-colons. Our example would look like this:

Note that it is essential that you do not include the address that you entered in the 'from:' field in the list of addresses in the 'to:' field as OSFAX will not be able to handle the circular reference.

You may want to add a comment (such as the PRONTO customer code) in the 'comment:' field. Then use the arrow keys to move the cursor down to the word 'Add' at the bottom left of the screen, and press the enter key. (Note that the 'insert' key can be used as a shortcut to jump down to the bottom of the screen.)

To exit the OSFAX browser, press the 'home' key a couple of times. If you have more entries to add, arrow up to the top line of the screen, put the cursor on the word 'redirect' and press the enter key, then move the cursor to the word 'Add' on the second line and press the enter key again.

This procedure, using the 'redirect' option in OSFAX, may also be used to handle the situation where an email address is longer than the 40 characters that FAX/Assist allows to be manually entered or the 60 characters that Pronto allows.

Note that this procedure should not be used to send a single document to a large list of destinations, such as a letter to all customers or suppliers. The OSFAX 'numberLst' option can be used for this instead, and it allows a list of destinations to be loaded from a file.

What to do when OSFAX is not working.

This document sets out how to fix OSFAX when there is a problem.

(You can download a PDF version of this document by right-clicking and saving this link.)

Most likely, a user has reported that they submitted a fax or email to OSFAX using FAX/Assist and it has not been delivered. The first thing to do is to work out the extent of the problem. Is everything failing? Is it only faxes or only emails that are failing? Is it documents for all users or only one user?

Step 1.
To get a quick idea if there are a lot of documents from different users waiting to be sent, you can view the fax queue using the option in FAX/Assist, or you can view it from the shell using this command:
 $FAXDIR/bin/faxqueue -l | more
Note 1: it is minus letter 'el' for 'long' (not the number 'one') after the word 'faxqueue'.
Note 2: press the space bar to show the next screen of data if it shows "--More--" at the bottom of the screen, or press 'q' to quit a long display.

Step 2.
Use this command to see the overall status of the OSFAX software:
 $FAXDIR/bin/faxstatus

If the output scrolls off the screen, you can use:
 $FAXDIR/bin/faxstatus | more

You should see something like the screenprint below. This command will often indicate the nature of the OSFAX problem. The first part of the display shows information about the OSFAX licence. Look at the text on the line that starts with 'Scheduler:' and the last line that starts with 'Status:'.
As noted on the screenprint, ignore the line that starts with 'Server:' as this refers to the OSFAX component that accepts documents from the Windows PC OSFAX printer client.


If there is an error message in the output that is wider than the screen (as in the last line of the example above) you can see the full error message by running this command:
$FAXDIR/bin/osiris psl | grep Method
which will produce an output like this:

If OSFAX is not running at all, the 'Scheduler:', 'Prepare Daemon:', and 'Status:' lines will all show 'Not running' as in this example:

In this case, go to step 4 to start OSFAX.

If either the 'Scheduler:' or 'Prepare Daemon:' lines shows something other than 'Idle' or 'Idle' followed by a number, then re-run the command after a few seconds. If OSFAX is running ok, you would expect the output to change. If the output does not change then it is likely that OSFAX is stuck and OSFAX will need to be restarted. Go to step 4.

If any error is indicated on the 'Status:' line, it is likely that OSFAX is running OK but it cannot send out facsimile documents. In this case, if you send faxes using a modem (the 'Line:' line above the 'Status:' line will show the serial port device name, such as 'ttyS0' in the above example), then you should check the modem is ok. Try powering the modem off for 15 seconds and then powering it back on. Check that the cables between the serial port and the modem and the phone line have not been accidently disconnected. If the status is reported as 'No dial tone' after you have switched the modem off and on, try connecting an analogue phone to the phone socket and try making a call manually. If you use the Osiris relay service to send out faxes (the 'Line:' line will show 'relay1.osfax.net') go to step 5 below.

Step 3.

Try sending a document to yourself from the shell, rather than use FAX/Assist. OSFAX may display an error message that is not being shown when you send using FAX/Assist. To send the OSFAX test page to the fax number 9876 5432, run this command:

 $FAXDIR/bin/sendfax -Tt98765432

To send the OSFAX test page to the email address someone@somewhere.com.au, run:

$FAXDIR/bin/sendfax -Ttsomeone@somewhere.com.au

If the document is accepted, OSFAX will display the OSFAX job number. Run the OSFAX queue display again (step 1) and see if this document is showing on the queue. If the document is not showing on the queue, and OSFAX did not display an error, check whether OSFAX has sent you an email with an error message. (Note that you may need to check for an error email on the server if your email is not forwarded to your Windows email client. You can do this from within Pronto by using the F12 key to open 'promail'.)

Step 4.

Often, OSFAX simply needs to be started, or it is stuck and stopping and re-starting will fix the problem. Note that the commands in this section MUST be run as superuser. This means you must login as either 'root' or 'pronto', or use the 'su' command to change to either 'root' or 'pronto'. You may run these commands using the 'sudo' command if you do not have access to either the 'root' or 'pronto' passwords, however this is not the best method as any fault mail sent by OSFAX is likely to appear as coming from your login.

If OSFAX is not running, start it using this command as superuser:

$FAXDIR/bin/osiris fax/start

If OSFAX is already running, stop it with this command as superuser:

$FAXDIR/bin/osiris fax/stop

Then check that there are no OSFAX processes running using:

ps -ef | grep osiris | grep -v grep

This command should result in nothing being displayed. However, if there are still OSFAX processes running as shown in the example below, you will need to terminate the process as identified by the process number on the line:

In this example, you would need to terminate the process using:

kill 14694

or if this does not work, use:

kill -9 14694

Be extremely careful to ensure that the correct process number is used.
In each case, re-run the original 'ps' command to check there are no OSFAX processes still running.

If you needed to terminate any OSFAX processes, you should restart OSFAX using this command which checks and repairs OSFAX :

$FAXDIR/etc/reset

If you did not need to terminate any processes (which is the normal case), restart OSFAX using the command:

$FAXDIR/bin/osiris fax/start

Step 5.

This step only applies if you are using the Osiris relay service to send faxes via the internet.

Osiris Technology displays the current status of the relay service on the web site 'www.osfax.com' at the bottom of the page. The normal display is:


If the relay service is listed as normal operation, check that you are able to connect to the Osiris relay server by running this command:

telnet relay1.osfax.net 9875

If you are able to connect, an Osiris message will be displayed, followed by some gobbledegook, then the connection will be closed, as in this screenprint:

If you get the 'Connected' message and the 'Escape character' is shown, but you do not see the 'OSIRIS' header line, you will need to enter the escape character to close the connection. Note that '^]' indicates that you must press the 'escape' key and the ']' key at the same time. In this case, if the OSFAX web page did not indicate any problem, then you should alert them by phoning Osiris Technology on (02) 9439 4311.

If you are unable to connect to the Osiris server using the 'telnet' command, your system will probably wait for 60 seconds, then display a message such as 'Network error' or 'No path to host'. If this occurs, try connecting to any external web site using your PC. Check you are viewing a live page rather than one from cache by reloading the page or entering 'Time now' into Google and checking the result. If you can access the web but cannot connect to Osiris Technology using the 'telnet' command, then it is likely that your firewall is blocking the connection. The port 9875 needs to be open for outgoing connections to 'relay1.osfax.net'.

Step 6.

If you have reached this far and you still don't know what is wrong with OSFAX, here are a few more possibilities:

If OSFAX gives a 'Slice nearly full error', you need to allocate it more memory. Refer to the notes in this document or refer to this error in the index of the OSFAX User Manual.

If OSFAX gives a 'Disk partition full error', then you have a disk space problem. Check the disk space in the disk partition that is used by the 'Data directory:' shown by the 'faxstatus' command in step 1.

If some users can use OSFAX and others cannot and you do not have an unlimited-user licence, it maybe that you have reached your OSFAX licence limit. OSFAX is licenced for either 8, 16, 32 or unlimited users. This is not the number of simultaneous users at a point in time, but the total number of distinct users over a rolling 7 day period. The number of licenced users is shown by the 'faxstatus' command in step 1.

If one user cannot use OSFAX but others can, it may be that the username or unix-id for the user has been used previously and OSFAX is blocking the new username/unix-id combination. This sometimes happens when a user leaves a company and they are removed from the password file, then they return and are added with the same username but they get a new unix-id. OSFAX does not allow this. The solution is to remove the old user from OSFAX, but that is a bit awkward to describe here, so contact Lantana Systems directly for help. Note that when this problem happens, the user will get a error message saying 'Overlay not found' if they try to send a document using 'FAX/Assist', or an error saying 'Already exists' if they try to send directly through OSFAX. UPDATE: This document now describes how to fix this error.

Further help.

The OSFAX User Manual includes a chapter entitled 'Fault Finding' which is a valuable reference. There is also a copy of this manual in PDF format on the OSFAX installation CD.

There are other documents in the 'Support' section of the Lantana Systems' website at www.lantanasystems.com.au. The 'Contact us' section of this website also provides our current contact details, including any special holiday contact details.

Did this document help?

We welcome any feedback regarding how useful you found this document, and how it could be improved. Please send comments to: feedback@lantanasystems.com.au. Thanks.

Understanding the problem with batched SQL reports with date parameters, and a new solution using SQL/Input

If you create an SQL report and edit it so that it runs in a batch sequence using passed date parameters, you may have found that the report runs extremely slow. This blog posting explains why this happens and provides a solution that solves this problem, including a new solution that works for SQL reports that are run using the Pronto 'Report Distribution Management' module.

When you run an SQL report, the SQL system has to work out the best way to collect the data you want, including what indexes to use when it reads data from an object. For example, if you are extracting data from the sales-order-archive file and you specify a date range for the field so-processing-date, the SQL system will normally decide that the best index to use is index 2 that uses the fields so-processing-date and so-order-no.

This works fine if you explicitly specify the date, for example:
where so-processing-date = 21-DEC-2009

However, if you use any programming function to specify a field, the SQL system ignores the field when it is working out what index to use. For example, if you use:
where so-processing-date = today() - 1
then the SQL system will NOT choose the date index and instead it will use another index, normally index 1 that uses the field so-invoice-no. Consequently, it reads a huge amount of data to find yesterday's orders and it takes a l-o-n-g time.

You can test this yourself by creating two SQL reports with where clauses like those above and adding the reports to a batch sequence. If you check the mail you get when the batch job finishes, you will find the first will say something like:
234 records extracted (from 234 records tested)
and the second will say something like:
234 records extracted (from 30146253 records tested)
It is easy to see why one takes the extra time to run.

If you setup an SQL report to run in a batch sequence and use the 'Date-Parameter-Overlay' option to set the date when the batch runs, you need to handle the passed parameter in your SQL using something like:
where so-processing-date = date-to-julian($1)
This is using a function and the date will not be used to work out the index to use.

I worked out a solution to this problem back in 1994 when I created my program 'SQL/Input' which acts as front-end to the Pronto SQL system. Its main feature is that it allows you to set up meaningful defaults, including date defaults, when you prompt the user for input values to use in an SQL report. However, it also allows you to run an SQL report in a batch sequence where the default parameters will be used. The program rewrites the SQL report into a temporary file and then runs this temporary SQL as a batch job. The result is that the SQL runs as if the date had been explicitly specified, and the correct date index is used by the SQL system to extract the data.

SQL/Input has been available as a free download from my website for many years.

My report management program 'Rep-Runner' was able to handle SQL reports that used 'SQL/Input' for the date parameter substitution, so such reports could be used as part of regular overnight processing and the output automatically distributed to one or more users.

'SQL/Input' could not be used with the standard Pronto 'Report Distribution Management' module because it loaded the substituted SQL as an extra job on the batch queue and the output was not captured and distributed to users. I did not have a problem with this as it worked fine with 'Rep-Runner'.

I have now decided to retire 'Rep-Runner' from Pronto version 670 onwards, as Pronto have changed the way that batch job sequences are stored and submitted and I do not want to rewrite the 'Rep-Runner' program. My 'Rep-Runner' software was initially released in 1995 when Pronto lacked the functionality that 'Rep-Runner' provided, but Pronto now has this functionality so it does not seem worthwhile to modify 'Rep-Runner' for the batch sequence changes.

However, that leaves the problem of how to run an SQL with date parameters using the correct indexes in a batch sequence that is run using the 'Report Distribution System' module, so I have now come up with a brand new solution.

I have modified the 'SQL/Input' program so that it can be called in a new mode with the name of a text file that contains a list of SQL reports to process. The program then reads each of the listed SQL reports, calculates the default parameters, and rewrites the SQL reports into new files in another directory. The new files have all parameters replaced with their calculated values so that they can be processed by the SQL system as if the values were explicitly specified. These new SQL report files can be correctly run in batch mode using the correct indexes by the 'Report Distribution System' and the output files will be correctly captured and distributed.

The solution is very easy to implement as it requires only one extra batch job to be run either prior to the 'Report Distribution System' or as part of an included sequence. The only requirement is that this extra call to 'SQL/Input' is made prior to any of the individual SQL reports in the sequence.

The free version of 'SQL/Input' that is available on my website does not include this new functionality. The enhanced version is available at no charge to existing users of my 'Rep-Runner' software, or for $300 (+GST) to other users.

Formatting data in Excel

All of the tools I have created to send data directly to Excel from Pronto share the same library of routines. Whether you create a 4GL report fully using GHOSTWRITER, use my Excel include files with your own 4GL program, or use SQL/Output to transform the data from a Pronto SQL query, in each case you are able to exercise a great deal of control over how the data is shown in an Excel cell.


For example, for a numeric field here is how the values -1234.56, zero and 1234.56 would be shown using several different format specifications:

Format	-1234.56	zero	1234.56
----------	----------	----------	----------
-(6)9.99	-1234.56	0.00	1234.56
-(6)zvzz	-1234.56		1234.56
-(6)9	-1235	0	1235
-(6)z	-1235		1235
-$(5)9.99	-$1234.56	$0.00	$1234.56
---,--9.99	-1,234.56	0.00	1,234.56
-$$$,$$9.99	-$1,234.56	$0.00	$1,234.56

Here are some different ways to display a date field:

Format	Date
----------	----------
dd-mm-yy	30-11-09
dd mmm yyyy	30 Nov 2009
ddd, dd mmm yyyy	Mon, 30 Nov 2009

As you can see, as a user you are fully in control of the Excel cell formatting with our Excel tools.

Add Excel output to your own 4GL reports

If you are a Pronto site that has a licence to use the Pronto 4GL, you can now easily add the ability to create Excel reports to your programs. I have extracted the Excel routines from my 'GHOSTWRITER' 4GL report writer software and packaged them up into a convenient include file and a component library file.

The include file provides a suite of simple macros that can be used to perform all the processing necessary to easily create Excel output files. This includes macros for opening and naming output files, creating worksheets, setting column headings and formats, inserting data into cells, closing the Excel file and emailing the file.

By providing individual macros for the various Excel functions, as a programmer you have full control over the layout and contents of the Excel spreadsheet. For example, you can control the sheet titles and column headings, create multiple worksheets within the file, and add additional processing information if you wish.

Just like the Excel files created using my new 'SQL/Output' software, the Excel output files that are created using the macros in the include file are in ‘XML Spreadsheet 2003’ format. This is a native Excel format that is supported by Excel 2003 or later versions.

The macros provide you with the ability to directly create the Excel output files from programs that are run in batch mode. If a program is being run interactively, and the Pronto output file directory is mapped to a PC directory, the user will be asked whether they want to open the file immediately using Excel.

My website has more information about the Excel include file and component library, including a summary of the provided macros and a document showing how to convert a normal 4GL report to create an Excel output file.

Create an Excel file from SQL in batch mode

With my new program called 'SQL/Output' you can automatically turn the data retrieved using a Pronto SQL report into an Excel spreadsheet file. It can produce the Excel file even if you run the SQL as part of your overnight batch processing. No extra user action is required to create the Excel file from the SQL data (apart from setting up the batch queue). SQL/Output can also be configured so that the Excel file is automatically emailed to one or more recipients.

A specification is setup once within SQL/Output for each SQL report that defines the column headings, column types and cell formats to be used, together with details of totals to be placed at the end of the spreadsheet. The output directory and filename structure is also specified, together with optional email destinations.

To automatically create the Excel output, an entry to run the SQL/Output program is added to a Pronto batch sequence immediately after the entry to run the SQL report itself. If an SQL report is run manually by a user, SQL/Output can be manually run after the SQL has finished. Note that a site does not require a 4GL licence to use the SQL/Output program.

The Excel file created by SQL/Output is in ‘XML Spreadsheet 2003’ format. This is a native Excel format that is supported by Excel 2003 or later versions.

I've called this software 'SQL/Output' because the first general software for Pronto that I released back in 1994 was called 'SQL/Input'. That software allows entry screens to be created for SQL queries with smart defaults for entry parameters, including date defaults and help screen lookups for system table fields. SQL/Input has been available at no charge from my website for many years.

There is more information about both SQL/Input and SQL/Output on the website.