How to connect to a Database using ODBC
This lesson demonstrates how to use ODBC to connect to a database. Here we have accessed MS Access Database and have fetched results from it. We have created this example using LiveCode, MS Access 2003 and Windows XP, but you can use ODBC to connect to any database that supports it, from within LiveCode.
You can download the associated sample stack and database for this lesson below from this url: https://tinyurl.com/ya5he5y3
Creating a Database using MS Access
Click File->New, this will open a Tab in the right side of the Application which will ask you to select what type of DataBase you are creating. Click Blank Database option. This will create a blank Database, which we have named livecodeTest.
Creating a table in MS Access
Click "Create Table in Design View" , this will open up the table design. Add two fields labeled Name and Company to the table and save it.
Fill the table with Data
Double click the table "test", this will open the blank table. Fill in some data and then save the table and database.
Create a ODBC connection for the Database livecodeTest
Step 1: Goto Control panel -> Administrative Tools -> Data Sources(ODBC).
Step 2: Add new Data Source
Click the ADD button. This will popup a window asking for the correct driver, Select Microsoft Access Driver (*.mdb) and click finish
Step 3: Selecting the Database file and Naming it.
Set the name as "runrev" and select the database file livecodeTest.mdb.
Step 4: Creating Login and Password
Click on Advanced button to set the Username as "testUser" and Password "123456". Click Ok to save login details and click ok to save the Data Source. Setting username and password is essential to protect the data, of course for a real world database you would use a secure password.
Step 5: Accessing ODBC connection in LiveCode
Create a basic test stack with a card, a scrolling field, and a button called "Fetch Data", as above. We will now add code to the Fetch Data button so that it will fetch all the data from the Database Table "Test" and display it in the scrolling field.
Step 6: Coding for Fetch Data
on mouseUp local tDatabaseID --This will open up connection for the MS Access Database using a ODBC connection put revOpenDatabase("ODBC", "runrev", "livecodeTest", "testUser", "123456" ) into tDatabaseID if tDatabaseID is null then answer "Not connected" else answer "connected" --This statemenr will select all the data from the table Test put "SELECT * FROM test" into tQuery --This will query the Database Table using the statement in tQuery put revDataFromQuery(tab, cr, tDatabaseID, tQuery) into tData --This will display all the data from the Database put tData into field 1 --This closes the Database connection revCloseDatabase tDatabaseID end if end mouseUp
Step 7: Displaying Results
Click on the Fetch Data button, and you should see a result similar to the above. We have fetched the data to LiveCode from a MS Access Database using the ODBC connection.
Note: While accessing the MS Access Database file from LiveCode, you need to make sure the Access Database is closed, otherwise it will throw an error.