Retrieving the data

//---------------------------------------------------------
// Sample macro to demonstrate data retrieval from an
// ODBC data source.
// © Geoff Lane 1997
//---------------------------------------------------------

//---------------------------------------------------------
// DIALOGDEFINE is used to allow pasting from a web page
//---------------------------------------------------------


  //Define dialog box
DIALOGDEFINE(
	Dialog: "RetrievalDlg";
	Left: 10; Top: 10; Width: 200; Height: 100;
	Style: OK!|Cancel!;
	Caption: "Retrieve ODBC Data")
DIALOGADDTEXT(
	Dialog: "RetrievalDlg"; Control: "LastNameLabel";
	Left: 10; Top: 11; Width: 50; Height: 13;
	Style: Right!; Text: "Last Name:")
DIALOGADDEDITBOX(
	Dialog: "RetrievalDlg"; Control: "LastNameBox";
	Left: 65; Top: 10; Width: 100; Height: 13;
	Style: Left!|NoWPChar!|NoTabs; 
	MacroVar: LastName)
DIALOGADDTEXT(
	Dialog: "RetrievalDlg"; Control: "CityLabel";
	Left: 10; Top: 26; Width: 50; Height: 13;
	Style: Right!; Text: "City:")
DIALOGADDEDITBOX(
	Dialog: "RetrievalDlg"; Control: "CityBox";
	Left: 65; Top: 25; Width: 100; Height: 13;
	Style: Left!|NoWPChar!|NoTabs; 
	MacroVar: City)
DIALOGADDTEXT(
	Dialog: "RetrievalDlg"; Control: "CountryLabel";
	Left: 10; Top: 41; Width: 50; Height: 13;
	Style: Right!; Text: "Country:")
DIALOGADDEDITBOX(
	Dialog: "RetrievalDlg"; Control: "CountryBox";
	Left: 65; Top: 40; Width: 100; Height: 13;
	Style: Left!|NoWPChar!|NoTabs; 
	MacroVar: Country)
DIALOGADDCOMBOBOX(
	Dialog: "RetrievalDlg"; Control: "DataTypeBox";
	Left: 65; Top: 55; Width: 100; Height: 40;
	Style: Droplist!; MacroVar: DisplayType)
	DIALOGADDLISTITEM(
		Dialog: "RetrievalDlg"; Control: "DataTypeBox";
		Item: "Retrieve as Table")
	DIALOGADDLISTITEM(
		Dialog: "RetrievalDlg"; Control: "DataTypeBox";
		Item: "Retrieve as Merge Data")
	DisplayType := "Retrieve as Table"

DIALOGSHOW("RetrievalDlg")

IF (MacroDialogResult = 2)	//Cancel pressed
	DIALOGDESTROY ("RetrievalDlg")
	QUIT
ENDIF

//---------------------------------------------------------
// Process the user's input, building the SQL statment
// as we go
//---------------------------------------------------------
MyQuery := "SELECT FirstName, LastName, Street, Address1, "+
	"Address2, City, PostCode, Country FROM Contact "

Subclause := ""
IF (StrLen(LastName) > 0)
	Subclause := "WHERE LastName = '" + LastName + "' "
	MyQuery := MyQuery + Subclause
ENDIF

IF (StrLen(City) > 0)
	IF (StrLen(Subclause) > 0)
		Subclause := "AND City = '"
	ELSE
		Subclause := "WHERE City = '"
	ENDIF
	Subclause := Subclause +  City + "' "
	MyQuery := MyQuery + Subclause
ENDIF

IF (StrLen(Country) > 0 )
	IF (StrLen(Subclause) > 0)
		Subclause := "AND Country = '"
	ELSE
		Subclause := "WHERE Country = '"
	ENDIF
	Subclause := Subclause +  Country + "' "
	MyQuery := MyQuery + Subclause
ENDIF

//---------------------------------------------------------
// Now process as for the basic macro, with exception
// that we now choose the display format
//---------------------------------------------------------
ImportSetSource (SourceType: ODBC!)
IF (DisplayType = "Retrieve as Table")
	ImportSetDestination (DestinationType: WPTable!)
ELSE
	ImportSetDestination (DestinationType: MergeData!)
ENDIF
ImportSetIncludeNames (State: On!)
ImportSetDataSource (Source: "MyData")
ImportSetSQLQuery (SQLQuery: MyQuery)
ImportDoImport()

// Clean up
DIALOGDESTROY("RetrievalDlg")