mysql demo

Customer Documentation

Visual FoxPro 9 + MySQL 4.1.11 DemoVisual FoxPro 9 + MySQL 4.1.11 Demo

Contents

1Contents

1Introduction

2MySQL Installation and Setup

3MySQLDemo Database Creation and Setup

5MySQLDemo Tables

5Categories

5CustomerCustomerDemo

5CustomerDemographics

6Customers

6Employees

6EmployeeTerritories

6OrderDetails

7Orders

7Products

7Region

7Shippers

7Suppliers

8Territories

8Users

8Installing and Running the Demo Application

8Local server configuration

8Web server configuration

9Show Connection Info

10Simple Data Entry

11Show Picture

11Add category

11Edit category

12Delete category

12Complex Data Entry

13Add product

14Print invoice

15HTML report

16Standard VFP Report

16Application code

16Application Object

23Complex Data Entry Demo Form

IntroductionUnfortunately, I dont have VFP 6. For this reason Ive used VFP 9 (which I own) and Ive decided to document the demo thoroughly instead, with source code and screens. I guess some of the forms will raise errors if you try to open and run them in VFP 6, because of the language differences, and I hope this document will be clear enough.In client/server applications Visual FoxPro programmers have several means to access remote data: sending SQL commands to server (SPT); remote views; cursoradapters; ADO and OLEDB. Each of them has advantages and disadvantages; my belief is the programmer should choose the best tool for a specific goal. For this demo Ive used SPT commands (SQL Pass Through).This document contains step-by-step instructions to setup a testing environment under Windows. The production environment will have MySQL installed on a Linux machine, though. This shouldnt worry you the only difference is the IP address used in connection. The demo will be able to connect and use any MySQL machine, no matter which operating system that machine has.

For starters, please download MySQL-4.1.11-win32 from http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-4.1.11-win32.zip/from/pick#mirrors (pick a mirror close to you). Youll end having a zip file in your computer. Unzip it it contains a setup.exe.Ill include in the final zip package Visual FoxPro 9 runtimes, in order to allow you to run the demo.MySQL Installation and Setup

#1. Double-click setup.exe

#2. Next

#3. Select Typical then click Next

#4. Click Install

#5. Select Skip Sign-up, then click Next

#6. Make sure Configure MySQL Server now is checked, then click Finish.

#7. In Configuration Wizard, click Next

#8. Check Detailed Configuration, and then click Next.

#9. Check Developer Machine, and then click Next.

#10. Check Multifunctional Database, and then click Next.

#11. Select C:\ and Installation Path in the combos, and then click Next.

#12. Check Decision Support (DSS)/OLAP, then click Next.

#13. Check Enable TCP/IP Networking. In the Port Number combo select or enter 3306, then click Next.

#14. Check Standard Character Set, and then click Next.

#15. Check Install As Window Service, select in Service Name: MySQL, check Launch the MySQL server automatically, check Include Bin Directory in Windows PATH, then click Next.

#16. Check Modify Security Settings. Enter a password for root (note: I find this to be a little confusing; root is an actual user you are entering the password for user named root), confirm the password, uncheck Enable root access from remote machine, uncheck Create an Anonymous account, and then click Next.

#17. Click Execute.

#18. Click Finish.MySQL supports several database types:

The most used is MyISAM database. This database is not a real database (according to standard definition of database). It merely is a bunch of tables located in a single directory. This database type does neither supports transactions nor server-side code (Im extrapolating the term code here. I mean any kind of server side code execution). It doesnt offer support for transactions nor for relationships. On the other hand, its biggest advantage is it is very fast. For most websites MyISAM is enough.The other database type Im considering is InnoDB. This database supports transactions and relationships. It also can handle referential integrity (If a parent record is updated/deleted, child records can be automatically updated/deleted too). For a desktop application InnoDB is more appropriate than MyISAM, because it allows isolating data entered simultaneously by users. This situation hardly happens in web applications, and when it does, most of the time who writes last wins. This would be unacceptable in an inventory management application.For this demo purposes Ive used a MyISAM database. Final product will use InnoDB. In Database Setup chapter youll find the scripts and instructions required for inserting the initial data into mysql database.Next step would be downloading and installing MySQL ODBC drivers. Please open http://dev.mysql.com/get/Downloads/MyODBC3/MyODBC-3.51.11-1-win.exe/from/pick and pick a mirror close to you. Download the installer and execute the file. Installation should put no problems.MySQL webpage shows three types of connectors: ODBC driver, .NET driver and Java driver. These drivers act like translators between the application and MySQL server itself. For our needs ODBC driver is required.MySQLDemo Database Creation and Setup

If you have checked Include Bin Directory in Windows Path in #15 in previous chapter, then typing mysql.exe in any command prompt should launch MySQL console. It should show an error message saying access denied. If it says File not found, then you have to navigate to C:\Program Files\MySQL\MySQL Server 4.1\Bin before trying to use MySQLs console, or specify full path to it in command line (C:\Program Files\MySQL\MySQL Server 4.1\Bin\mysql.exe)Anyway, you have to open a command prompt and launch mysql.exe. The synthax is:mysql u root p

It will answer:

Enter password:

Please enter the password you have chosen in #16 in previous chapter. MySQLs console should answer:Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10 to server version: 4.1.11-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql > _

Do not close the console yet.

Side note: There are two major approaches when it comes to security in desktop applications:

#1. Each user has his own username/password in backend database, and when they enter username/password that info used to establish the connection to backend server. In this case, every time a new user need database access you have to add it in MySQLs user list. (Well see shortly how this is done). Users login information is kept in MySQLs users table.

#2. Application has a username/password and users information is kept in applications database.Lets suppose you have multiple applications running with MySQL. In case #1, all users from all applications are kept all together, and in case #2 each application has its own user list.

Personally, I prefer second approach. It allows me to copy the database with application having no worries about exporting/importing user info. All required info is stored in the database.

Websites built in Apache+Php+MySQL use the same approach. Apache is a user, and actual website users have their username/password stored in a separate table, one per each database.Here are the commands you have to enter in MySQLs console in order to define the application as a user (I am assuming you have mysql console open and you are logged as root see previous code lines):

use mysql;

answer: Database changed

insert into user (host, user, password) values ('localhost', 'your_username_here', PASSWORD('your_password_here'));answer: Query Ok, 1 row affected (0.01 sec)Do not close the console yet.

MySQL Server has a database named 'mysql' which is used to store servers data. We need to add mysql users into that database, hence the need to switch to database named mysql. Its kind of confusing first time: database has the same name as the server.

Please replace your_username_here and your_password_here with whatever you like. Avoid using spaces, please. Also, note these info down, youll need them later.

Now its time to create applications database and grant the new added user access rights to it:create database mysqldemo;

answer: Query Ok, 1 row affected (0.00 sec)insert into db (host, db, user, select_priv, insert_priv, update_priv, delete_priv) values ('localhost', 'mysqldemo', 'your_username_here', 'Y', 'Y', 'Y', 'Y');

answer: Query Ok, 1 row affected (0.00 sec)

flush privileges;

answer: Query Ok, 0 rows affected (0.02 sec)The commands above have created a user, a database and have granted that user partial access to database. It can only select, insert, update and delete records. It is unable to drop tables or the database itself, for example. Also, it has no access to other databases.Lets see this in action: logoff from console by typing Exit. You should see the command prompt now. Now type:mysql -u -p

Note: username and password are case sensitive. You should be logged now.

use mysql;

Answer: ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'use mysqldemo;

Answer: Database changedExit;As you see, your new added user has access to MySQLDemo database only. You can close the command prompt now.Side note on this: web applications run on the server and the username and password are protected by the server itself, even they are usually stored in a text file. Distributed applications run on the local computer and for this reason they have to have a mean to get the username/password info required to connect to server. There are two approaches on this: #1. Hiding the info somewhere and getting it whenever is required (which allows to have different username/password pairs for application and for server) and #2. Reading the SQL username/password from user, but this means someone has to administer the server in order to add/edit/delete users. Also, in this approach there is no application user. All users are sql servers users. Both ways work, both have proven their stability so its just a matter of decision.MySQLDemo TablesThis chapter shows the table DDL (Data Definition Language). You are not supposed to enter them by hand I am just showing them here; Ive included in MySQLDemo\Other a file named mysqldemo.sql, which contains all the definitions and all the data required to build a fully functional database.

To create a fully functional database, you have to launch a command prompt, launch mysql console (login as root, as shown in chapter MySQL Database Creation and Setup), and enter the following commands:mysql>source c:\mysqldemo\other\mysqldemo.sqlPlease adjust the path, if required.

MySQL console will start executing the batch, outputting the result to console. The typical result looks like this:

Query OK, 0 rows affected (0.00 sec)

Query OK, 91 rows affected (0.00 sec)

Records: 91 Duplicates: 0 Warnings: 0

The whole batch should work without errors and it should create and populate the tables.

Categories

CREATE TABLE `Categories` (

`CategoryId` int(11) NOT NULL auto_increment,

`CategoryName` varchar(15) NOT NULL default '',

`Description` varchar(254) default NULL,

`Picture` mediumblob,

PRIMARY KEY (`CategoryId`),

KEY `CategoryName` (`CategoryName`)

) ;CustomerCustomerDemo

CREATE TABLE `Customercustomerdemo` (

`CustomerID` char(5) character set latin1 NOT NULL default '',

`CustomerTypeID` char(10) character set latin1 NOT NULL default '',

PRIMARY KEY (`CustomerID`,`CustomerTypeID`)

) ;

CustomerDemographics

CREATE TABLE `Customerdemographics` (

`CustomerTypeId` int(11) NOT NULL default '0',

`CustomerDesc` char(254) character set latin1 default NULL,

PRIMARY KEY (`CustomerTypeId`)

) ;

Customers

CREATE TABLE `Customers` (

`CustomerID` char(5) character set latin1 NOT NULL default '',

`CompanyName` char(40) character set latin1 NOT NULL default '',

`ContactName` char(30) character set latin1 default NULL,

`ContactTitle` char(30) character set latin1 default NULL,

Àddress` char(60) character set latin1 default NULL,

`City` char(15) character set latin1 default NULL,

`Region` char(15) character set latin1 default NULL,

`PostalCode` char(10) character set latin1 default NULL,

`Country` char(15) character set latin1 default NULL,

`Phone` char(24) character set latin1 default NULL,

`Fax` char(24) character set latin1 default NULL,

PRIMARY KEY (`CustomerID`),

KEY `City` (`City`),

KEY `CompanyName` (`CompanyName`),

KEY `PostalCode` (`PostalCode`),

KEY `Region` (`Region`)

) ;

Employees

CREATE TABLE Èmployees` (

ÈmployeeId` int(11) NOT NULL auto_increment,

`LastName` varchar(20) NOT NULL default '',

`FirstName` varchar(20) NOT NULL default '',

`Title` varchar(30) default NULL,

`TitleOfCourtesy` varchar(25) default NULL,

`BirthDate` date default NULL,

`HireDate` date default NULL,

Àddress` varchar(60) default NULL,

`City` varchar(15) default NULL,

`Region` varchar(15) default NULL,

`PostalCode` varchar(10) default NULL,

`Country` varchar(15) default NULL,

`HomePhone` varchar(24) default NULL,

Èxtension` varchar(4) default NULL,

`Photo` blob,

`Notes` varchar(254) default NULL,

`ReportsTo` int(11) default NULL,

`PhotoPath` varchar(254) default NULL,

PRIMARY KEY (ÈmployeeId`)

) ;

EmployeeTerritories

CREATE TABLE Èmployeeterritories` (

ÈmployeeId` int(11) NOT NULL default '0',

`TerritoryId` char(20) NOT NULL default '',

PRIMARY KEY (ÈmployeeId`,`TerritoryId`)

) ;

OrderDetails

CREATE TABLE Òrderdetails` (

ÒrderID` int(11) NOT NULL default '0',

`ProductId` int(11) NOT NULL default '0',

ÙnitPrice` decimal(11,0) NOT NULL default '0',

`Quantity` smallint(6) NOT NULL default '1',

`Discount` decimal(11,2) NOT NULL default '0.00',

PRIMARY KEY (ÒrderID`,`ProductId`)

) ;

Orders

CREATE TABLE Òrders` (

ÒrderID` int(11) NOT NULL auto_increment,

`CustomerID` char(5) default NULL,

ÈmployeeID` int(11) default NULL,

ÒrderDate` date default NULL,

`RequiredDate` date default NULL,

`ShippedDate` date default NULL,

`ShipVia` int(11) default NULL,

`Freight` decimal(11,0) default '0',

`ShipName` char(40) default NULL,

`ShipAddress` char(60) default NULL,

`ShipCity` char(15) default NULL,

`ShipRegion` char(15) default NULL,

`ShipPostalCode` char(10) default NULL,

`ShipCountry` char(15) default NULL,

PRIMARY KEY (ÒrderID`)

) ;

Products

CREATE TABLE `Products` (

`ProductID` int(11) NOT NULL auto_increment,

`ProductName` char(40) NOT NULL default '',

`SupplierID` int(11) default NULL,

`CategoryID` int(11) default NULL,

`QuantityPerUnit` char(20) default NULL,

ÙnitPrice` decimal(11,3) default NULL,

ÙnitsInStock` smallint(6) default NULL,

ÙnitsOnOrder` smallint(6) default NULL,

`ReorderLevel` smallint(6) default NULL,

`Discontinued` tinyint(4) NOT NULL default '0',

PRIMARY KEY (`ProductID`)

) ;

Region

CREATE TABLE `Region` (

`RegionID` int(11) NOT NULL auto_increment,

`RegionDescription` char(50) NOT NULL default '',

PRIMARY KEY (`RegionID`)

) ;

Shippers

CREATE TABLE `Shippers` (

`ShipperID` int(11) NOT NULL auto_increment,

`CompanyName` char(40) default NULL,

`Phone` char(24) default NULL,

PRIMARY KEY (`ShipperID`)

) ;

Suppliers

CREATE TABLE `Suppliers` (

`SupplierID` int(11) NOT NULL auto_increment,

`CompanyName` char(40) default NULL,

`ContactName` char(30) default NULL,

`ContactTitle` char(30) default NULL,

Àddress` char(60) default NULL,

`City` char(15) default NULL,

`Region` char(15) default NULL,

`PostalCode` char(10) default NULL,

`Country` char(15) default NULL,

`Phone` char(24) default NULL,

`Fax` char(24) default NULL,

`HomePage` char(100) default NULL,

PRIMARY KEY (`SupplierID`)

) ;

Territories

CREATE TABLE `Territories` (

`TerritoryID` char(20) NOT NULL default '',

`TerritoryDescription` char(50) NOT NULL default '',

`RegionID` int(11) NOT NULL default '0',

PRIMARY KEY (`TerritoryID`)

) ;

Users

CREATE TABLE Ùsers` (

Ìd` int(11) NOT NULL auto_increment,

ÙserName` char(20) character set latin1 NOT NULL default '',

`Password` char(20) character set latin1 NOT NULL default '',

PRIMARY KEY (Ìd`)

) ;

Installing and Running the Demo ApplicationI didnt create an installation kit for the application; instead, Ive included in the root application directory all the DLLs required to run the exe. Unzip the file youve received it will extract all the required files.Next step is to let the demo know the username and password youve chosen. The final product will have them buried deep into registry, but for this demo purposes a plain text file is enough.

Local server configuration

If you want to run the application on your local MySQL server, please open connectioninfo.txt (found in MySQLDemo\Other directory) and replace the data entered with your own settings. Ive used grig and grig for username and password. You have to replace them with and (the ones you have entered while working in MySQL console). Do not remove Server, UserName: and Password:. Just delete grig and replace it in both instances. The final result should look like this:Server:localhost

UserName:grig

Password:grig

Web server configuration

I have shown the demo to Bob Lee before sending it to you, and he has created a database on his site. If you want to see how the application works over the web (the database is located at www.1amsoftware.com), please change the connectioninfo.txt as follows:

Server:www.1amsoftware.com

UserName:traderuser

Password:traderpassIn this case the app will try to connect to Bobs server, instead your local server.Either way, after successful connection, the application will show the login form:

Username: admin

Password: admin

After login, the menu is presented to user:

Show Connection InfoThis option interrogates the server and the application, then shows some of the results:

Simple Data Entry

This option launches a form that updates Categories table:

Show Picture

Add category

Edit category

You can also doubleclick the record in grid.

Delete category

Complex Data Entry

Add product

Print invoice

HTML report

Standard VFP Report

You can find in MySQLDemo\Docs directory samples of these printings.Application code

Here you can find some samples of my code. I hope you like it.Application Object

**************************************************

*-- Class: app (c:\mysqldemo\classlibs\basectrls.vcx)

*-- ParentClass: cus (c:\mysqldemo\classlibs\basectrls.vcx)

*-- BaseClass: custom

*-- Time Stamp: 05/15/05 08:28:02 PM

*

DEFINE CLASS app AS cus

cusername = ""

cpassword = ""

nconnectionhandle = ""

csqlusername = ""

csqlpassword = ""

Name = "app"

PROCEDURE connecttoserver

If Not File("ConnectionInfo.txt")

MessageBox("Connection Info text file cannot be found." + Chr(13) + ;

"Please reinstall the application.", 16, "MySQL Demo")

llSuccess = .F.

Else

lcString = FileToStr("ConnectionInfo.txt")

ALines(laConnectionInfo,lcString)

If Alen(laConnectionInfo,1) # 3

MessageBox("Connection Info file was tampered." + Chr(13) + ;

"Please reinstall the application.", 16, "MySQL Demo")

llSuccess = .F.

Else

lcServer = StrExtract(laConnectionInfo(1),":")

lcUserName = StrExtract(laConnectionInfo(2),":")

lcPassword = StrExtract(laConnectionInfo(3),":")

lcConnectionString = ;

"Driver={MySQL ODBC 3.51 Driver};" + ;

"Server=" + lcServer + ";" + ;

"UID=" + lcUserName + ";" + ;

"PASSWORD=" + lcPassword + ";" + ;

"Database=mysqldemo;" + ;

"Port=3306;" + ;

"Option=16899;"

This.nConnectionHandle = SQLStringConnect(lcConnectionString, .T.)

This.cSQLUserName = lcUserName

This.cSQLPassword = lcPassword

If This.nConnectionHandle < 0 && an error has occured

AError(gaError)

MessageBox("Could not connect to MySQL Server. Error message returned is: " + Chr(13) + ;

gaError[1,2], 16, "MySQL Demo")

llSuccess = .F.

Else

llSuccess = .T.

EndIf

EndIf

EndIf

Return llSuccess

ENDPROC

PROCEDURE setenvironment

_Screen.Caption = "MySQL Demo"

_Screen.WindowState = 2

Set Path To Classlibs; Forms; Graphics; Menus; Other; Progs; Reports

Set Classlib To BaseCtrls.vcx additive

Set Procedure To prgclasses.prg Additive

Set Sysmenu Off

Set Talk Off

Set Notify Off

Set Notify Cursor Off

Set Safety Off

Set Escape Off

Set Confirm On

Set Strictdate To 0

Set Exact On

Set Near Off

Set Ansi On

Set Exclusive Off

Set Seconds On

Set Multilocks On

Set Deleted On

Set Refresh To 0,5

Set Odometer To 100

Set Blocksize To 64

Set Reprocess To 0

Set Resource On

Set Century On

Set Decimals To 2

Set NULLDISPLAY to ""

Set Safety Off

_Vfp.AutoYield = .F.

SQLSetProp(0,"ConnectTimeOut",5)

SQLSetProp(0,"DispLogin",3)

SQLSetProp(0,"Transactions",1)

CursorSetProp("MapBinary",.T.,0)

DECLARE INTEGER GetSysColor IN Win32api ;

INTEGER

nIndex

DECLARE INTEGER ShellExecute IN shell32.dll ;

INTEGER hndWin, ;

STRING cAction, ;

STRING cFileName, ;

STRING cParams, ;

STRING cDir, ;

INTEGER nShowWin

_Screen.BackColor = GetSysColor(12)

_Screen.Visible = .T.

_Screen.Closable = .F.

ENDPROC

PROCEDURE login

If Empty(This.cUserName) Or Empty(This.cPassword)

Do While .T.

oParameterObject = CreateObject("Empty")

AddProperty(oParameterObject, "cUserName","")

AddProperty(oParameterObject, "cPassword","")

Do Form LoginForm With oParameterObject

If Empty(oParameterObject.cUserName) Or ;

Empty(oParameterObject.cPassword)

MessageBox("You haven't entered required login info. Application will close.",64,"MySQL Demo.")

If This.nConnectionHandle > 0

SQLDisconnect(This.nConnectionHandle)

This.nConnectionHandle = 0

EndIf

llSuccess = .F.

Exit

Else

This.cUserName = oParameterObject.cUserName

This.cPassword = oParameterObject.cPassword

cUserName = This.cUserName

cPassword = This.cPassword

lcSQLCommand = "Select * From Users Where UserName = ?cUserName And Password = ?cPassword"

SQLExec(This.nConnectionHandle, lcSQLCommand, "Login_Info")

If Reccount("Login_Info") > 0 && found a record

llSuccess = .T.

Exit

Else

llSuccess = .F.

MessageBox("Login info are incorrect.", 16 , "MySQL Demo")

EndIf

EndIf

EndDo

EndIf

If Used("Login_Info")

Use In Login_Info

Endif

Return llSuccess

ENDPROC

PROCEDURE showconnectioninfo

Do Form ShowConnectionInfo

ENDPROC

PROCEDURE simpledataentry

Do Form SimpleDataEntry

ENDPROC

PROCEDURE showsqlerrormessage

Lparameters tcErrorString, tcSQLCommand

MessageBox("An error has occured. Error message is:" + Chr(13) + ;

Alltrim(tcErrorString) + Chr(13) + Chr(13) + ;

"SQL Command: " + Chr(13) + ;

Alltrim(tcSQLCommand) , 16, "MySQL Demo")

ENDPROC

PROCEDURE complexdataentry

Do Form ComplexDataEntry

ENDPROC

PROCEDURE htmlreport

lcString = "SELECT " + ;

"products.ProductID, " + ;

"products.ProductName, " + ;

"products.QuantityPerUnit, " + ;

"products.UnitPrice, " + ;

"products.Discontinued, " + ;

"categories.CategoryName " + ;

"FROM " + ;

"categories " + ;

"RIGHT OUTER JOIN products ON (categories.CategoryId=products.CategoryID) " + ;

"ORDER BY products.ProductName"

lnResult = SQLExec(_Screen.oApp.nConnectionHandle, lcString, "crsHtmlResult")

If lnResult < 0

AError(gaError)

This.ShowSQLErrorMessage(gaError[1,2], lcString)

Return .F.

EndIf

Select crsHTMLResult

Index On ProductName Tag Product

Text To lcOutputString NoShow

MySQL Demo :: Alphabetical List Of Products

Alphabetical List Of Products

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

X

Y

W

Z

ENDTEXT

For lnI = 65 to 90

This.AddHTMLReportGroup(Chr(lnI))

Endfor

lcOutputString = lcOutputString + []

If Used("crsHTMLResult")

Use in crsHTMLResult

EndIf

StrToFile(lcOutputString, "tmp\productlist.htm")

ShellExecute(0,"open","tmp\productlist.htm","","",1)

ENDPROC

PROCEDURE addhtmlreportgroup

Lparameters tcFirstLetter

lcOutputString = lcOutputString + ;

[] + tcFirstLetter + []


[] + ;

[] + ;

[Product Id] + ;

[Product Name] + ;

[Category Name] + ;

[Qty Per Unit] + ;

[Unit Price] + ;

[Discontinued] + ;

[]

Scan For Left(ProductName,1) = tcFirstLetter


[] + ;

[] + Alltrim(Transform(crsHTMLResult.ProductId)) + [] + ;

[] + Alltrim(crsHTMLResult.ProductName) + [] + ;

[] + Alltrim(crsHTMLResult.CategoryName) + [] + ;

[] + Alltrim(crsHTMLResult.QuantityPerUnit) + [] + ;

[] + Alltrim(Transform(crsHTMLResult.UnitPrice, '@$ 999,999.99')) + [] + ;

[], [>]) + []

EndScan


[] + ;

[]

ENDPROC

PROCEDURE standardvfpreport

lnStartTime = Seconds()

lcString = "SELECT " + ;

"Orders.OrderID, " + ;

"Orders.OrderDate, " + ;

"Orderdetails.Quantity, " + ;

"Products.ProductName, " + ;

"Products.ProductId, " + ;

"Suppliers.CompanyName AS Supplier, " + ;

"Categories.CategoryName, " + ;

"Products.QuantityPerUnit, " + ;

"Products.UnitsInStock, " + ;

"Products.UnitsOnOrder, " + ;

"Products.ReorderLevel, " + ;

"Products.Discontinued, " + ;

"Customers.CompanyName AS Customer, " + ;

"Orderdetails.UnitPrice, " + ;

"Orderdetails.Discount * 100 AS Discount, " + ;

"Orderdetails.UnitPrice * Orderdetails.Quantity * (1 - Orderdetails.Discount) AS ExtendedPrice " + ;

"FROM " + ;

"Orders " + ;

"RIGHT OUTER JOIN Orderdetails ON (Orders.OrderID=Orderdetails.OrderID) " + ;

"INNER JOIN Products ON (Products.ProductID=Orderdetails.ProductId) " + ;

"INNER JOIN Suppliers ON (Suppliers.SupplierID=Products.SupplierID) " + ;

"INNER JOIN Categories ON (Categories.CategoryId=Products.CategoryID) " + ;

"INNER JOIN Customers ON (Customers.CustomerID=Orders.CustomerID) " + ;

"ORDER BY " + ;

"Products.ProductName, " + ;

"Customers.CompanyName, " + ;

"Orders.OrderDate"

lnResult = SQLExec(_Screen.oApp.nConnectionHandle, lcString, "crsProductSales")

If lnResult < 0

AError(gaError)

This.ShowSQLErrorMessage(gaError[1,2], lcString)

llSuccess = .F.

Else

llSuccess = .T.

EndIf

lnDuration = Seconds() - lnStartTime

If llSuccess

Report Form ProductSales Noconsole To Printer Prompt Preview

EndIf

If Used("crsProductSales")

Use in crsProductSales

EndIf

ENDPROC

PROCEDURE Destroy

_Screen.Closable = .T.

Set Sysmenu To Default

Set Sysmenu Automatic

If This.nConnectionHandle > 0

SQLDisconnect(This.nConnectionHandle)

This.nConnectionHandle = 0

EndIf

ENDPROC

PROCEDURE Init

Local llSuccess

With This

.SetEnvironment()

llSuccess = This.ConnectToServer()

If llSuccess

llSuccess = This.Login()

EndIf

If llSuccess

Do mainmenu.mpr

EndIf

EndWith

Return llSuccess

ENDPROC

ENDDEFINE

*

*-- EndDefine: app

**************************************************

Complex Data Entry Demo Form

**************************************************

*-- Form: complexdataentry (c:\mysqldemo\forms\complexdataentry.scx)

*-- ParentClass: frm (c:\mysqldemo\classlibs\basectrls.vcx)

*-- BaseClass: form

*-- Time Stamp: 05/15/05 12:27:05 PM

*

DEFINE CLASS complexdataentry AS frm

DataSession = 2

Height = 534

Width = 675

DoCreate = .T.

Caption = "Complex data entry demo"

MinHeight = 422

MinWidth = 600

MDIForm = .T.

Name = "complexdataentry"

ADD OBJECT pgforders AS pgf WITH ;

ErasePage = .T., ;

PageCount = 2, ;

Anchor = 15, ;

Top = 5, ;

Left = 8, ;

Width = 664, ;

Height = 495, ;

Name = "pgfOrders", ;

Pag1.Caption = "\

mysql demo

Documents