How to connect to Mysql ?

Learning APL or new to Dyalog? Ask "silly" questions here, without fear...

How to connect to Mysql ?

Postby GowthamSen on Tue Dec 01, 2009 5:40 am

Hi,

I recently started learning Dyalog APL.
Its really interesting. Looks simple. But I feel it takes some time to learn.

I started working on samples provided by the APL. I am using APL 11.0

There is a sample APL WINFORM.WS. There is a function Grid which in turn calls Grid_Load.

I am trying to replicate Grid_Load by updating the connection details with respect to MySql.

My purpose is, I want to connect to My Sql and get the list of tables available and there on see what's there in each table.

I am trying this since one week. I didn't get success.

Could you please guide me in this? I would like to have a sample code which connects to MySql and retrieves one table data and display it in a grid.

Any guidance or suggestion are welcome.

Looking forward for your kind reply.

Thank you,
Regards,

Gowtham Sen.
GowthamSen
 
Posts: 5
Joined: Mon Nov 30, 2009 6:02 am

Re: How to connect to Mysql ?

Postby Morten|Dyalog on Tue Dec 01, 2009 10:15 am

A very similar question was asked by another newcomer about a month ago; it turned out that he was trying to solve a problem as part of a job interview process. If this is also your purpose, I would ask that you not submit the following code as your own work(!), but spend a few hours reading documentation on how to use the Dyalog GUI and SQAPL– I suggest taking a look at the first chapters of the Interface Guide… and the ODBC chapter towards the end of that manual.

The following code is a very quick hack… It uses the Native Dyalog GUI rather than WinForms. Some work is required to make it "look nice" (and check for error conditions) - or rework it using WinForms, but should be enough to get you started - I hope

This function takes an SQAPL connection name as its argument, extracts the list of table names, creates a form with a listview in it and populates it with the table names:

Code: Select all
      ∇ DBVIEW Connection;tables
[1]    tables←2⊃SQA.Tables Connection
[2]    tables←1↓tables[;tables[1;]⍳⊂'TABLE_NAME']
[3]    'DBF'⎕WC'Form' 'DataBases'('Size'(400 800))('Coord' 'Pixel')
[4]    'DBF.TABLES'⎕WC'List'tables(10 10)(380 250)('Event' 'Select' 'ReadCols')
[5]    ⎕DQ'DBF'
     ∇

This function is called when there is a Select event on the listview: It creates a grid with the column information.

Code: Select all
     
     ∇ ReadCols arg;data;table
[1]    ⍝ Callback on select in Table List
[2]    table←DBF.TABLES.((SelItems⍳1)⊃Items)
[3]    data←0 3↓2⊃SQA.Columns'C1'table ⍝ Drop Schema Info
[4]    'DBF.COLS'⎕WC'Grid'(1 0↓data)(10 260)(380 530)('ColTitles'(data[1;]))
[5]    DBF.COLS.TitleWidth←0
     ∇


To run it, I type:
Code: Select all
      SQA.Connect 'C1' 'NorthWind'
 0
      DBVIEW 'C1'


Which brings up the following picture:
dbviewer.jpg
dbviewer.jpg (85.02 KiB) Viewed 23179 times
User avatar
Morten|Dyalog
 
Posts: 405
Joined: Tue Sep 09, 2008 3:52 pm

Re: How to connect to Mysql ?

Postby GowthamSen on Tue Dec 01, 2009 4:07 pm

Thank you Morten.

I haven't tried this code yet. But today I will this try out.

I am sure I am not going to use this code. I would like to create my own stuff for my job.

I am confused a lot with the syntax. In which documentation I can see the syntax regarding calling methds, assigning the resultant object data to a corresponding object?

For example, I did code like this.

Code: Select all
A - Establishing connection.
Connection← MySqlConnection 'Server=localhost,Database=test,user id=root,Password=mysql'
Command← MySqlCommand 'Select * from account' Connection
A Creating DataReader object to hold data
DataReader←MySqlDataReader
DataReader←Command.ExecuteReader


I tried executing the function, its showing an error for the line DataReader.
Showing the error as Syntax Error.

I think, I am not yet understood about APL syntax fully.

Even, when I go through the code given by you, I don't understand what exactly happening. I mean I got the idea what we are doing here. But with respect to syntax I don't understand much.

1. 2⊃ - what does it refer to in the 1st statment in DBVIEW function?
2. ⎕WC - ?

I have gone throught documentation also. Could you please suggest me, how I can improve my skills in this area.

I aplogoise for lot of questions.

Thank you,
Regards,

Gowtham Sen.
GowthamSen
 
Posts: 5
Joined: Mon Nov 30, 2009 6:02 am

Re: How to connect to Mysql ?

Postby Morten|Dyalog on Tue Dec 01, 2009 5:08 pm

What are the "MySQL" objects that you refer to in your code - is this code supposed to be using COM or .NET? And if so, how do you set up the link to it?

For APL syntax in general, I suggest that you start at http://tutorial.dyalog.com. Regarding the user interface, see the introduction to the GUI in our Interface Guide. This manual should be included with your APL system, the latest versions can be downloaded from http://www.dyalog.com/documentation.

We also have a new book/tutorial on APL which we are about to publish via Amazon, and also make available as a free download. I will see whether I can post a copy of it so that you can download it.
User avatar
Morten|Dyalog
 
Posts: 405
Joined: Tue Sep 09, 2008 3:52 pm

Re: How to connect to Mysql ?

Postby GowthamSen on Tue Dec 01, 2009 5:25 pm

Hi Morten,

Thanks for the documentation links. I am looking forward for the new book link.

Here is the actual code. I am using .NET dll files.
I installed .NET 3.5 SP1 framework and ADO.NET compoenents for MySql.

Code: Select all
⊃Using 'System.Data.MySqlClient,System.Data.MySqlClient.dll' 'System.Data, System.Data.Dll'
A - Establishing connection.
Connection← MySqlConnection 'Server=localhost,Database=test,user id=root,Password=mysql'
Command← MySqlCommand 'Select * from account' Connection
A Creating DataReader object to hold data
DataReader←MySqlDataReader
DataReader←Command.ExecuteReader


MySqlConnection, MySqlCommand and MySqlDataReader are namespaces in the assemblies which are defined in the first statement with 'Using'.

For the question - What are the "MySQL" objects that you refer to in your code : I am not sure.
here Connection , Command and DataReader are MySql objects.

Thank you,
Regards,

Gowtham Sen.
GowthamSen
 
Posts: 5
Joined: Mon Nov 30, 2009 6:02 am

Re: How to connect to Mysql ?

Postby Morten|Dyalog on Tue Dec 01, 2009 5:32 pm

The two statements

Code: Select all
DataReader←MySqlDataReader
DataReader←Command.ExecuteReader

Look a little strange - why would you immediately redefine DataReader? Are you trying to convert this from some sample code written in another language? If so, can you send us that sample - or a link to it?
User avatar
Morten|Dyalog
 
Posts: 405
Joined: Tue Sep 09, 2008 3:52 pm

Re: How to connect to Mysql ?

Postby GowthamSen on Tue Dec 01, 2009 5:40 pm

Yes Morten.

We are trying to replicate a sample code.

Here is the URL:
http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/connector-net-architecture-reader.html

Thank you,
Regards,

Gowtham Sen.
GowthamSen
 
Posts: 5
Joined: Mon Nov 30, 2009 6:02 am

Re: How to connect to Mysql ?

Postby Morten|Dyalog on Tue Dec 01, 2009 6:49 pm

OK, I think your problem is that you have translated the C# declaration "MySqlDataReader myReader" into APL as "DataReader←MySqlDataReader". But APL is typeless - no declarations are needed or possible, so you should simply remove this line of code.

I seriously think you would be better off using ODBC and the SQAPL tool which is included with APL, it will return data to you as an array which you can put straight into our grid object: Reading the data with this tool will be so much easier than looping to extract all the pieces of data as you seem to have to do with the MySql objects.

I also think you need to start with learning the basics of APL, otherwise you will just be pasting code snippets together with no real idea of what is going on (sorry).
User avatar
Morten|Dyalog
 
Posts: 405
Joined: Tue Sep 09, 2008 3:52 pm

Re: How to connect to Mysql ?

Postby GowthamSen on Tue Dec 01, 2009 7:04 pm

Hi Morten,

Thanks for your valuable inputs.

I did read documentation provided but not fully. But as suggested, I will go through the books posted out here.

Thank you,
Regards,

Gowtham Sen.
GowthamSen
 
Posts: 5
Joined: Mon Nov 30, 2009 6:02 am


Return to New to Dyalog?

Who is online

Users browsing this forum: No registered users and 1 guest