Hints for MySQL ODBC for Win9X

in C++ Builder Application

Get from mysql.com here: MyODBC

1. Install ODBC

2. Run ODBC Data Source Administrator (in Control Panel) & Configure. See Connection Issues/Considerations below.

3. Start C++ Builder

Objects Needed:

-- From Data Access Tab: TTable, TDatabase TDataSource

-- From Data Controls Tab: TDBGrid

Property changes in objects:

• TDataSource: DataSet: Table1

• TDatabase: AliasName (select one built with ODBC Data Source Administrator), DatabaseName: (enter name), Connected: TRUE (will be prompted for User Name & Password - ones specified in the User DSN ODBC previously configured. If this connection is not made, your TTable Active object property will not be able to be set to TRUE and when the application is compiled and runs, the TDBGrid will not display any data.)

If the TDatabase LoginPrompt object property is set to TRUE

-- Will be prompted for the MySQL User Name & Password before the database is opened on the application (provided the TTable Active property is set to true before compiling project the table will then be displayed in the application)

-- If the ODBC System DSN has a User Name & Password:

• Pressing OK without having entered a User Name/Pasword entered when prompted, you will connect to the database.

-- If the ODBC System DSN does not have a User Name & Password:

• Pressing OK without having entered a User Name/Pasword entered when prompted, you will not connect to the database.

• TTable: DatabaseName (select one built with ODBC Data Source Administrator), TableName: (will be prompted for User Name & Password - ones specified in the User DSN ODBC previously configured) then the available table name will be displayed for selection, Active: TRUE.

• Click on TDBGrid: DataSource: Select data source being used (DataSource1 in this example). The grid will display table data when selected.

• Change TDatabase: Connected to FALSE. If not done, you will be prompted for User Name & Password twice.

4. Compile & Run. You will be prompted for User Name & Password. However, if the password is entered when configuring the User DSN, the user does not have to enter the User Name & Password to access the Database.

Connection Issues/Considerations:

• To connect to the database permissions must be granted in MySQL. For each user, you can specify a username, password, allowable hosts and a set of permissions that determine what the user can do. The permissions tables are broken down into the following types: User, Database, Host, Table, and Fields. Brief explanations follows:

USER Permissions: Any user listed that is granted permissions has those permissions on all databases. It is not a good practice to grant any permissions in this table except to the database administrator.

DATABASE Permissions: Used to define which MySQL users will have access to which databases from which hosts. Every user must already exist in the User Permissions table, though they can be granted additional permissions here. Users may not login from any hosts other than those specified in. However, you may grant different permissions for different hosts by creating multiple rows for the same user.

TABLE Permissions: Used to grant additional access to tables by users, beyond what is available in User Permissions or Database Permissions. The permissions granted here apply only to access to one table in one database by one user, thus allowing quite fine-grained access to be granted.

FIELD Permissions: This field allows you to grant additional access to specific fields in tables by users. The permissions granted cannot be greater than those specified in the Table Permissions under Field permissions for the table the field is in.

• If there is no User Name or Password specified in the System DSN and the TDatabase LoginPrompt object is set to TRUE:

  -- upon execution the User Name & Password dialog box will be activated

   

  • The User Name displayed will be the last name used to connect to MySQL when the project was started and then compiled. The name can be changed by the user by highlighting it and typing in a new one.

   

  • Even when there is no prompt for a User Name & Password for the TDatabase, the driver doesn't appear to use the Windows User Name and Password to connect -> it uses "ODBC."

  Return to top

---

EXAMPLE 1

Permission table/ODBC information:

USER permissions table: User: ME; Hosts: localhost; Password: (encrypted); and Permissions: None.

DATABASE permissions table: Databases: mydb; User: ME; Hosts: 137.155.37.46; and Permissions: None.

ODBC System DSN: The correct MySQL host (Name or IP), MySQL database name, and User information has been entered.

The application will not be able to connect to the database from localhost because the database permissions specify Host 137.155.37.46.

EXAMPLE 2

Permission table/ODBC information:

USER permissions table: User: ME; Hosts: 137.155.37.46; Password: (encrypted); and Permissions: None.

DATABASE permissions table: Databases: mydb; User: ME; Hosts: 137.155.37.46; and Permissions: None.

TABLE Permissions table: Table: (table name); Database: mydb; Hosts: 137.155.37.46; User: ME; Table Permissions: Select; and Field Permissions: None.

ODBC System DSN: The correct MySQL host (Name or IP), MySQL database name, and User information has been entered.

The application prompts the user for User Name & Password, it will connect to the database, the database will be displayed in the TDBGrid and the user can use the arrow keys & tab keys to move around within the table. However, an error will occur if the user attempts to change data in any Field because the Field Permissions do not include Update.

EXAMPLE 3

Permission table/ODBC information:

USER permissions table: User: ME; Hosts: 137.155.37.46; Password: (encrypted); and Permissions: None.

DATABASE permissions table: Databases: mydb; User: ME; Hosts: 137.155.37.46; and Permissions: None.

TABLE Permissions table: Table: (table name); Database: mydb; Hosts: 137.155.37.46; User: ME; Table Permissions: Select; and Field Permissions: Select | Update.

FIELD Permissions table: All Fields listed with correct Table, Database, Hosts, and User names. Then all of them have Permissions set as only Select, except one that has Select and Update.

ODBC System DSN: The correct MySQL host (Name or IP), MySQL database name, and User information has been entered.

The application prompts the user for User Name & Password, it will connect to the database, the database will be displayed in the TDBGrid and the user can use the arrow keys & tab keys to move around within the table. However, an error will occur if the user attempts to change data in any Field except the one with Select | Update permissions because the Field Permissions do not include Update in any of the other Fields.