Erlang Central

Difference between revisions of "MySQL native client"

From ErlangCentral Wiki

m
m
Line 95: Line 95:
 
</code>
 
</code>
  
18:25, 26 September 2011 (BST)<br>
+
18:56, 26 September 2011 (BST)<br>
<nowiki>[[Category:HowTo]]</nowiki>
+
[[Category:HowTo]]
<nowiki>[[Category:Interfacing_Erlang]]</nowiki>
+
[[Category:Interfacing_Erlang]]

Revision as of 17:56, 26 September 2011

Contents

Author

User:Krasnopolski

Introduction

Erlang client for MySQl server is a native client. It means the client is directly connecting to the server and using low level (network socket level) connection protocol. So advantage of the client as a native is speed and performance. Disadvantage is that client design tightly coupled with MySQL connection protocol, but MySQL team is conservative enough with it.

Design

The MySQL client is implemented as Erlang/OTP application. Root supervisor of the client manages a set of datasource processes each of them can be pointed to different MySQl servers or to the same but differently configured. To create a datasource process (or object) we can use function from the client API:

my:new_datasource(my_ds, #datasource{
		host = "localhost",
		port = 3306,
		database = "",
		user = "root",
		password = "root"}
).

Datasource is supervisor in own turn and keeps management under one connection pool process and a few active connection processes to server. The connection pool stores a reusable ip/tcp socket and is tracking their life cycles. When new connection process is creating then the connection pool is trying to provide an existed but idle socket. If set of idle socket is empty connection pool creates new one. If connection process has closed then assigned socket becomes idle and goes to set of idle sockets of the pool.

Supported features

The client supports the following client features of MySQL server:

  • configure client connection using #client_options{} record
  • compression mode for transfer data through connection
  • transfer of very long packets as a multi sub-packets
  • prepared statements execution
  • transfer a long statement parameters (blob parameters)
  • fetch cursor on server previously generated by prepared statement
  • transaction execution

Getting started

We have already created datasource object above. So now we can obtain connection to our server:

Conn = my:get_connection(my_ds)

Let's create new database (or schema) on the server:

my:execute_query(Conn, "CREATE DATABASE IF NOT EXISTS testDB")

Next step is a table creation:

my:execute_query(Conn, 
	"CREATE TABLE testDB.sample_table ("
		"id bigint(20) NOT NULL AUTO_INCREMENT,"
		"name varchar(45) DEFAULT NULL,"
		"PRIMARY KEY (id)"
	") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"
)

It is time to insert something to new table:

my:execute_query(Conn, 
	"INSERT INTO testDB.sample_table(name) "
	"VALUES ('Alex'), ('John')"
)

And finally we can extract this rows from the table:

{Metadata, Rows} = my:execute_query(Conn, "SELECT * FROM testDB.sample_table"),
io:format("Result: ~p~n", [Rows])

We can see on console something like this:

Result: [{rs_row_data, [1,"Alex"]},{rs_row_data, [2,"John"]}]

Query returns a tuple that contains two members. First one is metadata record represented information about table fields and second one is list of records represented rows of query result. Each row contains values of fields of SELECT statement. In our case there are 'id' and 'name' fields.

Prepared statement

Let's prepare a very simple statement:

Handle = my:get_prepared_statement_handle(Conn, "SELECT name FROM testDB.sample_table WHERE id = ?")

Now execute it:

{Metadata, Rows} = my:execute_statement(Conn, Handle, [?MYSQL_TYPE_LONGLONG], [1]),
io:format("Result: ~p~n", [Rows])

Result has to be:

Result: [{rs_row_data, [1,"Alex"]}]

18:56, 26 September 2011 (BST)