Erlang Central

Difference between revisions of "MySQL native client"

From ErlangCentral Wiki

m (New page: ==Author== User:Krasnopolski ==MySQL Native Client== ===Introduction=== Erlang client for MySQl server is a native client. It means the ...)
 
m
Line 2:Line 2:
 
[[User:Krasnopolski]]
 
[[User:Krasnopolski]]
  
==MySQL Native Client==
+
==Introduction==
===Introduction===
+
[https://sourceforge.net/projects/erlmysql Erlang client] for MySQl server
[[https://sourceforge.net/projects/erlmysql/|Erlang client]] for MySQl server
+
 
is a native client. It means the client is directly connecting to the server and using
 
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
 
low level (network socket level) connection protocol. So advantage of the client as a native
Line 10:Line 9:
 
connection protocol, but MySQL team is conservative enough with it.
 
connection protocol, but MySQL team is conservative enough with it.
  
Design
+
==Design==
 
The MySQL client is implemented as Erlang/OTP application. Root supervisor of the client
 
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
 
manages a set of datasource processes each of them can be pointed to different MySQl servers
Line 31:Line 30:
 
and goes to set of idle sockets of the pool.
 
and goes to set of idle sockets of the pool.
  
Supported features
+
==Supported features==
 
The client supports the following client features of MySQL server:
 
The client supports the following client features of MySQL server:
 
* configure client connection using #client_options{} record
 
* configure client connection using #client_options{} record
Line 41:Line 40:
 
* transaction execution
 
* transaction execution
  
Getting started
+
==Getting started==
 
We have already created datasource object above. So now we can obtain connection to our server:
 
We have already created datasource object above. So now we can obtain connection to our server:
 
<code>
 
<code>
Line 81:Line 80:
 
there are 'id' and 'name' fields.
 
there are 'id' and 'name' fields.
  
Prepared statement
+
==Prepared statement==
 
Let's prepare a very simple statement:
 
Let's prepare a very simple statement:
 
<code>
 
<code>
Line 98:Line 97:
 
18:25, 26 September 2011 (BST)<br>
 
18:25, 26 September 2011 (BST)<br>
 
<nowiki>[[Category:HowTo]]</nowiki>
 
<nowiki>[[Category:HowTo]]</nowiki>
 +
<nowiki>[[Category:Interfacing_Erlang]]</nowiki>

Revision as of 17:53, 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:25, 26 September 2011 (BST)
[[Category:HowTo]] [[Category:Interfacing_Erlang]]