2006-09Sep-29

Strange error message with Oracle

The Oracle ODBC driver threw an error message on a completely innocent looking line:

SQLEXEC( m.lnHandle, ;
  "BEGIN MyFunction('val1', ?pcVal2 ); END;" ;
)

The error message was particularly puzzling:

ORA-06550: line 2, column 39:
PLS-00103: Encountered the symbol "end-of-file" when
expecting one of the following:

( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string liter (6550)

Even more puzzling is that the statement executes just fine on a second identically configured connection. The ODBC trace didn't reveal any particular useful. Both connections produce the same output up to the point where one returns with an error, but the other not. By logging all network traffic with Ethereal it became at least apparent why Oracle throws the strange error message. The working version was transmitted as:

BEGIN MyFunction('val1', ?); END;

Oracle received the failing version like this

 BEGIN MyFunction('val1', 

In other words, starting with the query parameter, everything was missing. The ODBC driver simply didn't pass the entire query string on to the server. No wonder it was confused! The reason seems to be that somehow the Oracle ODBC driver couldn't keep track of the parameter bindings. The solution, therefore, is to add more occurrences of the query parameter like this:

DECLARE
  X CHAR(10);
BEGIN
  X:= ?pcVal2;
  MyFunction('val1', ?pcVal2 );
END;

2006-09Sep-29

Dealing with Outlook references

When you automate Outlook, you typically create a reference to Outlook.Application and then get the namespace using the GetNamespace() method. Everything you want to do is possible through the Namespace object. loNamespace.Application provides a nice reference back to Outlook. So why would one want to keep the additional reference around?

Local loOutlook, loNamespace, loInspector, loFolder
MessageBox("Please close Outlook now")
loOutlook = CreateObject("outlook.application")
loNamespace = loOutlook.GetNamespace("MAPI")
loOutlook = NULL
loFolder = loNamespace.GetDefaultFolder(6)
loFolder.Items(1).GetInspector.Activate()
MessageBox("Close Outlook window, please.")
loFolder.Items(1).GetInspector.Activate()

This code raises an error stating the GetInspector doesn't evaluate to an object. The first line worked fine, the exact same line after closing the outlook item doesn't work anymore. If you don't clear the reference in loOutlook, the code works just fine.

2006-09Sep-29

Choosing a folder in Outlook

When looking around for samples about Outlook automation samples, it seems that most either create items only in the default folders or hardcode the path in the program code. However, in a real world application, the folder in which you create item often has to be configurable. Fortunately, there's actually a function build into MAPI that does this:

Local loOutlook, loNamespace, loFolder
loOutlook = CreateObject("outlook.application")
loNamespace = loOutlook.GetNamespace("MAPI")
loFolder = loNamespace.PickFolder()
If Vartype(m.loFolder) == "O"
  * Do something with the folder item
  ? loFolder.FolderPath
EndIf

PickFolder automatically prompts the user for a profile, user name and password if the user hasn't logged on so far. There's one drawback of PickFolder(), though. If you have got an Outlook window visible, this window moves into the foreground and becomes the active one afterwards.

2006-09Sep-27

Opening an e-mail window with GroupWise™

In most cases, you can open an e-mail editing window using

DECLARE INTEGER ShellExecute IN SHELL32.DLL ;
  INTEGER nWinHandle,;
  STRING cOperation,;
  STRING cFileName,;
  STRING cParameters,;
  STRING cDirectory,;
  INTEGER nShowWindow
ShellExecute(0,"open","mailto://"+m.tcMail,"",NULL,1)

Sometimes (or maybe always) GroupWise™ doesn't respond to the "mailto:" syntax despite being the default email client. Fortunately, GroupWise™ supports something that I'd call DDE over COM. You create a COM object that lets you send commands in the same fashion as DDEPOKE() does in VFP with DDE:

LOCAL loGroupwise, lcResult
lcResult = ""
loGroupwise = CREATEOBJECT("GroupWiseCommander")
loGroupwise.Execute("NewMail()",@lcResult)
loGroupwise.Execute( ;
  [TextSetTo("]+m.lcText+[")], ;
  @lcResult ;
)

A list of commands (called GroupWise Tokens) can be found on the Novell website.

2006-09Sep-26

Calling _Execute() from a different thread

You can only call the _Execute VFP API function from within the same thread that Visual FoxPro uses to load the library. Calling the function from a different thread crashes Visual FoxPro. If you need to execute code originating in a different thread, you can use SendMessage, though. Here's the code for the FLL. First of all, you need a variable to hold the window handle:

HWND hWndMain = NULL;

Next, you need a function that a VFP application calls to register the handle

void FLL_Register( ParamBlk *Param )
{
  hWndMain = (HWND) (Param->p[0].val.ev_long);
}

Finally, whenever you wanted to use _Execute(), you use code like this instead

LRESULT Result;
Result = SendMessage( hWndMain, WM_USER, 0, 0 );

Here's the code that loads the FLL and initializes the messaging

Set Library To Sample.FLL Additive
BindEvent( _VFP.Hwnd, 0x400, This, "WndProc" )
FLL_REGISTER( _VFP.Hwnd )

The WndProc routine looks like this.

Procedure WndProc( tnHwnd, tnMsg, tnWParam, tnLParam )
  Do case
  Case m.tnMsg == 0x400
    * do something
    Return 2
  EndCase
EndProc

The return value (2 in the sample) is passed back to the FLL in the Result value. Similar to _Execute, the FLL is suspended until the VFP program processed the message.

2006-09Sep-26

There is not enough memory to complete this operation

When calling an FLL function you get this error messages, when the FLL creates a new string but doesn't specify a string length:

Value ret;
ret.ev_type = 'C';
ret.ev_handle = hDest;
_RetVal( &ret );

The missing line is

ret.ev_length = bytesDest;

2006-09Sep-25

Sorting

You find sorting algorithm in most programming languages, but you have to look hard for an implementation of common sorting algorithms in Visual FoxPro. Of course, Calvin Hsia has some on his blog, but other than that it's getting difficult. The most obvious reason is that Visual FoxPro supports sorting natively for indexes as well as arrays.

Sorting mixed strings

If you have data like

1/1A
1/1B
2/A
10/2
10/3

you want to sort the first part as if it's numerical. That is, the order should be 1, 2, 10, not 1, 10, 2 as it would happen, if Visual FoxPro treats this as a string. At the same time, however, you want to sort the remaining part in alphabetical order. The following index expression accomplishes that:

INDEX ON STR(VAL(field)) + field TAG _Sort

Sorting with COLLATE

Virtually every application picks only one COLLATION sequence it uses all the time. The majority uses MACHINE, followed by GENERAL, followed by country specific versions. However, sometimes you need to sort and search data with a different order. For example, you might use MACHINE as the collate sequence, but in some cases need to sort case-independent (that's just an example!).

When you create an index, Visual FoxPro uses the active COLLATE sequence or the one you specified with the COLLATE clause. A little known fact is that Visual FoxPro always uses the COLLATE sequence that belong to an index when Visual FoxPro searches or sorts using the index. This is true even when the application switched to a different COLLATE sequence:

Create Cursor curTest (cName C(10))
Insert into curTest Values("Straße")
Set Collate To "GERMAN"
Index on cName Tag cName
Set Collate To "MACHINE"
? Seek("Strasse")  && Prints .T.

2006-09Sep-24

Record is out of range (#5)

Something tried to access a record that does not exist in the selected table.

Cause: Index corruption

When the index for a table is corrupt, it might very well contain record numbers that do not exist in the table at all. When a VFP command tries to access the record, VFP immediately raises this error. If the error appears on a command that does not access records, it might be a filter condition that is evaluated in the background, for instance, in a grid. REINDEX usually takes care of this problem during development. At runtime real reindex routines should be used.

Cause: Listbox and Requery

A listbox is bound to a cursor using the RecordSourceTypes #2 (Alias) or #6 (Fields) and its value points to any record. Now you reduce the number of records in this RecordSource. Typically the error appears when you use ZAP, but occasionally it also appears upon re-executing a SELECT statement or using the REQUERY() statement.

Once this happened, the error likely to appear in various situations. It happens, when you access a property of the listbox. But it also happens when VFP internally refreshes the listbox. The reason is simple. Visual FoxPro stores the number of the current record and navigates to this record when you access the listbox, or when it needs to repaint it. When you removed records and there's no record with the old number, then you get the error. The same applies to ComboBoxes, as well.

Remains the question why you don't always get it. Visual FoxPro appears to contains a lot of code that notifies controls of a change in a table. That explains, why the grid is immediately blanked out, even though there's no need for the grid to repaint it. These event notifications are necessary to avoid errors like this one. When you change the number of records of the RecordSource from within the Requery() method, you don't see any problem. That's obvious, because VFP knows that the RecordSource is likely to change. But even when you execute SELECT...FROM somewhere in the program, the listbox seems to be notified sometimes and does not raise an error. ZAP seems to be the least cooperative command, as it causes most such problems.

Cause: GOTO or LOCATE RECORD

The most obvious reason for this error is an invalid record number used by the GOTO or LOCATE RECORD command. Valid record numbers are 1 through RECCOUNT() and negative numbers for added records. LOCATE RECORD can also get RECCOUNT()+1 as a parameter and positions the table at EOF() in this case.

2006-09Sep-20

Class Factory cannot supply requested class (0x80040111)

When the Init() method of a VFP COM object returns .F., Visual FoxPro raises this error to the creating application.

2006-09Sep-18

FRX as a cursor

You cannot create an FRX file as a cursor using the CREATE CURSOR command, because FRX files contain a UNIQUE field which isn't allowed as a field name:

CREATE CURSOR errorCursor (UNIQUE L)

This statement results in an error message stating that there are no fields to process. That's because VFP sees UNIQUE as a clause following a non-existent field name

Update: wOOdy kindly pointed out that the above statement is wrong and provides the solution:

CREATE CURSOR NoErrorCursor ("UNIQUE" L)

You have to place the field name in quotation marks.