Host Is Not Allowed To Connect To This MySQL Server Error In Visual Foxpro

When you encounter this type of error in VFP, it may mean that the MySQL account you are using does not have the right privileges.

For a quick fix, go to the server PC and open MySQL Command Line Client. Type the command below:

grant all privileges on *.* to root@'%' identified by 'YourPassword';

Change the 'root' if you're not using that user and change YourPassword with your own password.

Show MySQL Port

What is the open port that your MySQL is using? By default it should be 3306, but in any case that you might have changed it during the installation then perhaps you could include the port number in your connection string.

You can check it via the MySQL command line client by typing:

SHOW variables LIKE 'port';

Understanding Types of Joins In SQL

Understanding Types of Joins in SQL by All Saints

Connecting VFP To MySQL Database

Here's how to access a MySQL database from VFP.

First, you need to download an ODBC connector to MySQL from this link. Choose between 32 or 64 bit version.
http://dev.mysql.com/downloads/mirror.php?id=412782

Next is to connect to it via DSN or DSNless connection.
Here's an example of a connection string via a DSNLESS (ODBC) connection. Please note that the lines starting with an asterisk (*) are comments.

lcStringConnect = 'Driver=MySQL ODBC 5.2 Unicode Driver;Server=localhost;uid=root;pwd=password'

*---This part here sets connection errors hidden.

SQLSetprop(0, "DispLogin", 3)

SQLSetprop(0, "DispWarnings", .F.)

*---This is where the actual connection happens.

lnConnect = Sqlstringconnect(m.lcStringConnect)

*---The variable lnConnect holds the number returned by the function
*---SQLSTRINGCONNECT(), if the value is greater than zero (0) then
*---no error happened during the connection attempt. 

If m.lnConnect > 0

  *--- List the databases in MYSQL.

   SQLEXEC(m.lnconnect,'show databases','testcur')

   BROWSE

Endif

=SQLDisconnect(m.lnConnect) && Close Connection

If the MySQL Database is remote (or online), you will need to specify the IP address as a server name in the connection string.

Date Imported From Excel Changed Into Number

When importing data from an Excel file into VFP cursor, a date field may change into number field like the example below:

Date in Excel	Current Number In Cursor
19-Dec-2006	39070
30-dec-2008	39812
19-May-1989	32647

To fix the issue, we could either replace all the serial numbers in the new field with this command:

Scan

   Replace SomeField with SomeField + {^1899/12/30}

EndScan

Or use a select statement that should convert that serial number back to date readable by human eyes.

Select SomeField + {^1899/12/30} As NewField from SomeTable Into Cursor TestCursor

Extracting Date In 10-Digit Integer Format from MySQL To VFP

To extract date from a MySQL field in 10-digit integer format, we can use  FROM_UNIXTIME function.

Assuming you are connected via ODBC (SPT):

=SQLExec(m.nConnect,'select from_unixtime(somefield) as xdatetime from sometable','SomeViews') && should produce datetime

=SQLExec(m.nConnect,'select Date(from_unixtime(somefield)) as xdate from sometable','SomeViews') &&should produce date format

Create A New Table From An Existing Table In MS SQL Server

If I want to transfer (copy) table and its record from one database to another, I would do this for example:

Select * Into [Database2].[Dbo].[Table1] From [Database1].[Dbo].[Table1]

Basically, that's how it can be done. You can alter the structure of the newly created table
by adding records from JOIN or via creation of new fields using CAST.