Difference between revisions of "MySQL"

From wiki.visual-prolog.com

(MySQL and VIP)
(connect string)
 
(16 intermediate revisions by 2 users not shown)
Line 1: Line 1:
== MySQL and VIP ==
+
This article briefly describes how to install MySQL locally and how to interact with a MySQL DB using a VIP (GUI) program.
  
Page under development
+
=== Download and install MySQL ===
  
This article briefly describes how to install MySQL locally and how to interact with a MySQL DB with a VIP (GUI) program.
+
See http://www.mysql.com and http://dev.mysql.com/downloads/
 +
Download the ''MySQL community Server''
  
 +
At the end of the installation you will automatically be prompted to configure the server, via the ''MySQL Server Instance Configuration Wizard''. You can call this wizard at any time from the Windows ''Start-Programs-MySQL'' menu item.
  
#'''Download and install MySQL'''<br>See http://www.mysql.com<br>and http://dev.mysql.com/downloads/<br>Download the ''MySQL community Server''<br><br>
+
Select all the defaults for the dialogs as presented by the wizard: it's probably best to use a non-blank password (e.g. "scott").
#'''Configure the Server'''<br>At the end of the installation you will be automatically be prompted to configure the server, via the ''MySQL Server Instance Configuration Wizard''. You can call this wizard at any time from the Windows ''Start-Programs-MySQL'' menu item.<br><br>Select all the defaults for the dialogs as presented by the wizard: it's probably best to use a non-blank password.<br><br>Check that the server is running as a Windows service (''Control Panel - Performance and Maintenance - Administrative Tools - Services'')<br><br>
+
 
#'''Download and install the MySQL ODBC Driver'''<br> See http://www.mysql.com/products/connector/odbc/<br><br>
+
Check that the server is running as a Windows service (''Control Panel - Performance and Maintenance - Administrative Tools - Services'')
#'''Create a Datasource'''<br>Go to ''Control Panel - Performance and Maintenance - Administrative Tools - Data Sources (ODBC)''<br>Under the dialog tab ''User DSN'', press ADD, select the ''MySQL ODBC Driver'' (probably version 3.51).<br><br>In the dialog, enter the following:<br>#*'''Data Source Name''': myodbc<br>#*'''Description''':      a test<br>#*'''Server''':          localhost<br>#*'''User''':            root<br>#*'''Password''':        The password you entered for the server in step 2 above<br>#*'''Database''':        fred<br>Press the TEST button - it should connect.<br><br>
+
 
#'''Test the database with the Command Line Client'''<br><br>From the Start-All Prolograms-MySQL menu, start the command line client. Enter the password at the prompt. The prompt should now be:<br>'''mysql>'''<br><br>*Enter "show databases;" (without the quotes, but with the semi-colon), and database "fred" should be listed.<br>*Enter "use fred"<br><br>Now create a table - <br>*Enter "create table person (name varchar(20, age integer);"<br>*Enter "show tables;"  (remember the semi-colons)<br>Enter some data into the table. <br>*Enter "insert into person values('fred','87');"
+
Also Download and install the MySQL ODBC Driver
 +
 
 +
See http://www.mysql.com/products/connector/odbc/
 +
 
 +
=== Create an ODBC Datasource ===
 +
 
 +
Go to ''Control Panel - Performance and Maintenance - Administrative Tools - Data Sources (ODBC)''
 +
Under the dialog tab ''User DSN'', press ADD, select the ''MySQL ODBC X.Y Unicode Driver''.
 +
 
 +
In the dialog, enter the following:
 +
 
 +
{|{{prettytable}}
 +
|-
 +
| '''Data Source Name'''
 +
| myodbc
 +
|-
 +
|'''Description'''
 +
| a test
 +
|-
 +
| '''Server'''
 +
| localhost
 +
|-
 +
| '''User'''
 +
| root
 +
|-
 +
| '''Password'''
 +
| scott (the password you entered for the server in step 2 above)
 +
|-
 +
| '''Database'''
 +
| fred
 +
|}
 +
 
 +
Press the TEST button - it should connect.
 +
 
 +
=== Create a database with the Command Line Client ===
 +
 
 +
From the ''Start-All Programs-MySQL'' menu, start the command line client.  
 +
 
 +
* Enter the password at the prompt. The prompt should now be: '''mysql>'''
 +
* Enter "show databases;" (without the quotes, but with the semi-colon), and database "fred" should be listed.
 +
* Enter "use fred"
 +
 
 +
Now create a table called "person" in "fred":
 +
 
 +
* Enter "create table person (name varchar(20), age integer);"
 +
* Enter "show tables;"  (remember the semi-colons)
 +
 
 +
Enter some data into the table:
 +
 
 +
* Enter "insert into person values('fred','87');"
  
 
And then check the data is there:
 
And then check the data is there:
  
>Enter "select * from person;"
+
* Enter "select * from person;"
  
 
and the data should be listed.
 
and the data should be listed.
Line 20: Line 71:
 
Close the MySQL session:
 
Close the MySQL session:
  
>enter "quit".
+
*Enter "quit".
 +
 
 +
=== Using MySQL in a Visual Prolog project ===
 +
 
 +
Create a new VIP GUI project, and cut and paste the following code into taskwindow.pro, and (e.g.) call predicate test:() from File-New.
 +
 
 +
This example adds one more record to the table "person" and then lists the table. It will also list the data entered previously at the mysql command line prompt (step 5 above).
 +
 
 +
<vip>predicates
 +
    test: ().
 +
clauses
 +
    test():-
 +
        Connection = odbcConnection::new(256),
 +
        ConStr =  @"DSN=myodbc;UID=root;PW=scott;",
 +
        if  X = Connection:driverConnect(This:getVpiWindow(), ConStr, odbc_native::sql_driver_noprompt) then
 +
    %        OR USE
 +
    %        Connection:connect("myodbc","root","scott"),
 +
            stdio::write("Connection info =",X),
 +
            Stmt = odbcStatement::new(Connection),
 +
            try
 +
                Stmt:execDirect("INSERT INTO person VALUES ('harrypotter',4)"),
 +
                look(Stmt)
 +
            finally
 +
                Stmt:free()
 +
            end try,
 +
            Connection:disconnect()
 +
        end if.
 +
 
 +
predicates
 +
    look : (odbcStatement Stmt).
 +
clauses
 +
    look(Stmt) :-
 +
        Stmt:resetParameter_all(),
 +
        Stmt:execDirect("select * from person"),
 +
        NumColumns = Stmt:numResultCols(),
 +
        foreach Stmt:fetch_nd() do
 +
            stdio::nl(),
 +
            foreach Column = convert(odbc_native::fieldIndex, std::fromTo(1, NumColumns)) do
 +
                ColName = Stmt:colAttribute_string(Column+0, odbc_native::sql_desc_name),
 +
                ColValue = Stmt:getColumnValue(Column+0),
 +
                stdio::writef("% = %, ", ColName, ColValue)
 +
            end foreach
 +
        end foreach,
 +
        Stmt:closeCursor().</vip>
  
'''6) Create a new VIP GUI project'''
+
Notice that it is very important to "free" <vp>odbcStatements</vp>; failing to do this will sooner or later result in program breakdown.  Therefore the "free" is placed inside a try-finally construction, so that it is also freed in case of exceptions.
  
page being developed
+
[[Category:ODBC]]

Latest revision as of 22:31, 27 October 2016

This article briefly describes how to install MySQL locally and how to interact with a MySQL DB using a VIP (GUI) program.

Download and install MySQL

See http://www.mysql.com and http://dev.mysql.com/downloads/ Download the MySQL community Server

At the end of the installation you will automatically be prompted to configure the server, via the MySQL Server Instance Configuration Wizard. You can call this wizard at any time from the Windows Start-Programs-MySQL menu item.

Select all the defaults for the dialogs as presented by the wizard: it's probably best to use a non-blank password (e.g. "scott").

Check that the server is running as a Windows service (Control Panel - Performance and Maintenance - Administrative Tools - Services)

Also Download and install the MySQL ODBC Driver

See http://www.mysql.com/products/connector/odbc/

Create an ODBC Datasource

Go to Control Panel - Performance and Maintenance - Administrative Tools - Data Sources (ODBC) Under the dialog tab User DSN, press ADD, select the MySQL ODBC X.Y Unicode Driver.

In the dialog, enter the following:

Data Source Name myodbc
Description a test
Server localhost
User root
Password scott (the password you entered for the server in step 2 above)
Database fred

Press the TEST button - it should connect.

Create a database with the Command Line Client

From the Start-All Programs-MySQL menu, start the command line client.

  • Enter the password at the prompt. The prompt should now be: mysql>
  • Enter "show databases;" (without the quotes, but with the semi-colon), and database "fred" should be listed.
  • Enter "use fred"

Now create a table called "person" in "fred":

  • Enter "create table person (name varchar(20), age integer);"
  • Enter "show tables;" (remember the semi-colons)

Enter some data into the table:

  • Enter "insert into person values('fred','87');"

And then check the data is there:

  • Enter "select * from person;"

and the data should be listed.

Close the MySQL session:

  • Enter "quit".

Using MySQL in a Visual Prolog project

Create a new VIP GUI project, and cut and paste the following code into taskwindow.pro, and (e.g.) call predicate test:() from File-New.

This example adds one more record to the table "person" and then lists the table. It will also list the data entered previously at the mysql command line prompt (step 5 above).

predicates
    test: ().
clauses
    test():-
        Connection = odbcConnection::new(256),
        ConStr =  @"DSN=myodbc;UID=root;PW=scott;",
        if  X = Connection:driverConnect(This:getVpiWindow(), ConStr, odbc_native::sql_driver_noprompt) then
    %        OR USE
    %        Connection:connect("myodbc","root","scott"),
            stdio::write("Connection info =",X),
            Stmt = odbcStatement::new(Connection),
            try
                Stmt:execDirect("INSERT INTO person VALUES ('harrypotter',4)"),
                look(Stmt)
            finally
                Stmt:free()
            end try,
            Connection:disconnect()
        end if.
 
predicates
    look : (odbcStatement Stmt).
clauses
    look(Stmt) :-
        Stmt:resetParameter_all(),
        Stmt:execDirect("select * from person"),
        NumColumns = Stmt:numResultCols(),
        foreach Stmt:fetch_nd() do
            stdio::nl(),
            foreach Column = convert(odbc_native::fieldIndex, std::fromTo(1, NumColumns)) do
                ColName = Stmt:colAttribute_string(Column+0, odbc_native::sql_desc_name),
                ColValue = Stmt:getColumnValue(Column+0),
                stdio::writef("% = %, ", ColName, ColValue)
            end foreach
        end foreach,
        Stmt:closeCursor().

Notice that it is very important to "free" odbcStatements; failing to do this will sooner or later result in program breakdown. Therefore the "free" is placed inside a try-finally construction, so that it is also freed in case of exceptions.