{"id":14,"date":"2013-11-12T14:56:46","date_gmt":"2013-11-12T19:56:46","guid":{"rendered":"http:\/\/www.scalemysql.com\/blog\/?p=14"},"modified":"2013-11-12T18:08:10","modified_gmt":"2013-11-12T23:08:10","slug":"handlersocket-syntax-perl","status":"publish","type":"post","link":"https:\/\/www.scalemysql.com\/?p=14","title":{"rendered":"Handlersocket Part II \/ syntax Perl\/PHP"},"content":{"rendered":"

(originally published 07\/18\/12)<\/p>\n

other handlersocket entries:<\/p>\n

part I<\/strong> – introduction to handlersocket<\/a><\/p>\n

part III<\/strong> – handlersocket examples – Perl<\/a><\/p>\n

————————-<\/p>\n

Like the native access, you can use access all handlersocket functionality via HandlerSocket.pm, you can download the entire Handlersocket package at,
\nhttps:\/\/github.com\/ahiguti\/HandlerSocket-Plugin-for-MySQL<\/a><\/p>\n

if you already have Handlersocket via Percona Server installed, in order to get HandlerSocket.pm, you may still need to download the separate installation, then simply go to the perl-Net-HandlerSocket directory within it and build the perl module per the instruction within it.<\/p>\n

What perl-Net-HandlerSocket can do:<\/p>\n

Like the native functionality it can,<\/p>\n

Select \/ insert \/update\/ delete on primary and secondary keys
\nFilter on primary and secondary keys
\n\u2018In\u2019 type functionality
\nqueries with more than one parameter<\/p>\n

The official documentation is at,<\/p>\n

https:\/\/github.com\/ahiguti\/HandlerSocket-Plugin-for-MySQL\/blob\/master\/docs-en\/perl-client.en.txt<\/a><\/p>\n

Initiate a connection<\/strong><\/p>\n

perl<\/span><\/strong><\/p>\n

use Net::HandlerSocket;<\/p>\n

my $args = { host => '127.0.0.1', port => 9999 };\r\n my $hs = new Net::HandlerSocket($args);<\/pre>\n
php<\/span><\/strong><\/p>\n
$hs = new HandlerSocket($host, $port_wr);<\/p>\n
very simple, choose your host, choose your port (normally 9999 or 9998, more below on this).<\/p>\n
Error Handling <\/strong><\/p>\n
After executing a call, you can print alerts and stop the program with the following,<\/p>\n
perl<\/span><\/strong><\/p>\n
die $hs->get_error() if $res != 0;<\/pre>\n
Or for multiple rows,<\/p>\n
for my $res (@$res) {\r\n die $hs->get_error() if $res->[0] != 0;\r\n shift(@$res);<\/pre>\n
php<\/span><\/strong><\/p>\n
if (! Whatever function) {\r\necho $hs->getError(), PHP_EOL;\r\n die(); \r\n}<\/pre>\n
Authorization<\/strong><\/p>\n
1) edit your my.cnf settings (see configuration settings above)<\/p>\n
2) then in your code,<\/p>\n
perl<\/span><\/strong><\/p>\n
my $res = $hs->auth('your_password');\r\n die $hs->get_error() if $res != 0;<\/pre>\n
php<\/span><\/strong><\/p>\n
if (!$hs->auth('your password')) {\r\n die('Fault auth');\r\n }<\/pre>\n
It will test both passwords and give appropriate permissions to the password that matches<\/p>\n
Open an index<\/strong><\/p>\n
HandlerSocket completely relies on the indexes to get data, it can not get a record without one. (although it can certainly get the non-indexed data within the row you retrieve using the index). You must open at least one index for handlersocket to operate on to do a query.<\/p>\n
perl<\/span><\/strong><\/p>\n
$res = $hs->open_index(a, 'b', 'c', 'd', \u2018e1,e2,e3\u2026\u2019, 'f1,f2,f3\u2026');<\/pre>\n
describing each position in the set above,<\/p>\n
a<\/strong> \u2013 the index id, this can be any number, you\u2019ll use it to differentiate all of your open indexes in your calls. Using the same number for a different index will overwrite the former opened index.
\nb<\/strong> \u2013 the database name
\nc<\/strong> \u2013 the table name
\nd<\/strong> \u2013 the name (defined in your table) of the index you are using
\ne(1,2,3\u2026)<\/strong> – the columns you are using in the index. If it\u2019s a multiple index, like mysql you must include the left most columns of the index (i.e. to use col2, you can not leave out col1 and use col2, you must include both).
\nf(1,2,3\u2026)<\/strong> \u2013 the columns for which you want data returned.<\/p>\n
php<\/span><\/strong><\/p>\n
$hs->openIndex(a,\u2019b\u2019 , \u2018c\u2019, \u2018d\u2019, 'f1,f2,f3\u2026')<\/pre>\n
exactly the same as above EXCEPT no defining partial indexes (no \u2018e1, e2,\u2026\u2019)<\/p>\n
Data transactions<\/strong><\/p>\n
To see specific examples, go to Handlersocket examples<\/a>. The general form of the call is the following,<\/p>\n
perl<\/span><\/strong><\/p>\n
select, insert, update and delete can all be done with the general form below,<\/p>\n
$res = $hs->execute_single(a, 'b',['c',\u2019c2\u2019, \u2018c3\u2019\u2026], d, e, \u2018f\u2019, \r\n[g1,g2,g3,\u2026],['h1','h2', 'h3','h4'],i,['j1','j2',\u2019j3','j4']);<\/pre>\n
Not all are necessarily relevant and depends on the operation you are doing.<\/p>\n
a<\/strong> \u2013 index id
\nb<\/strong> \u2013 the operation one of (>,=,!=, +)
\nc<\/strong> \u2013 the value for the index to query on OR the values for an insert
\nd<\/strong> \u2013 the limit of rows operated on like limit in mysql
\ne<\/strong> \u2013 the offset of the result like offset in mysql
\nf<\/strong> \u2013 if a delete or update then (\u2018D\u2019, \u2018U\u2019) otherwise undef
\ng(1,2,3..)<\/strong> \u2013 if update, an array of values to use for the update
\nh<\/strong> \u2013 an array with filter values
\nh1<\/strong> \u2013 \u2018F\u2019 (filter those values for =, !=), \u2018W\u2019 (stop once value is hit such as =)
\nh2<\/strong> \u2013 filter operation (>,=,!=)
\nh3<\/strong> \u2013 index id for the filter
\nh4<\/strong> \u2013 value to filter on
\ni<\/strong> \u2013 key for IN statement
\nj(1,2,3..)<\/strong> \u2013 values for IN statement<\/p>\n
the call itself can be execute_single or execute_multi. The latter will feed it as an array of the series above, there are a couple of examples coming up to demonstrate.
\nI\u2019ve found many positions not requiring a value may still need a placeholder, where an empty \u2018\u2019 will not work for some of them. Use undef in this case.<\/p>\n
php<\/span><\/strong><\/p>\n
Php is a little more flexible, you can use a similar executeSingle function for everything or use specific functions for insert, update, delete<\/p>\n
$retval = $hs->executeSingle(a, '=', array(\u2018c\u2019), d, e);<\/pre>\n
$retval = $hs->executeMulti(\r\n array(array(a1, '=', array(\u2018c1'), d1, e1),\r\n array(a2, '=', array('c2'), d2, e2)));<\/pre>\n
$hs->executeInsert(a, array('c1', 'c2','c3')\r\n $hs->executeUpdate(a, 'b', array('c1'), array('c2'), d, e)\r\n $hs->executeDelete(a, 'b', array('c'))<\/pre>\n
Things to keep in mind (optimization, troubleshooting and bugs)<\/strong><\/p>\n
a) execute_multi is supposed to be much faster for many statements than looping through execute_single.<\/p>\n
b) Contrary to the documentation, I\u2019ve found if you do not include definitions for the limit and offset, it will error. Using \u2018undef\u2019 for each place will work, but throw a \u2018Use of uninitialized value in subroutine entry at handlersockettest.pl\u2019 warning.<\/p>\n
c) DO NOT leave spaces on either side of the commas in \u2018open index\u2019 call for (e) and (f) – it will error. I assume because these are multiple value and each is not enclosed by quotes has something to do with it.<\/p>\n","protected":false},"excerpt":{"rendered":"
(originally published 07\/18\/12) other handlersocket entries: part I – introduction to handlersocket part III – handlersocket examples – Perl ————————- Like the native access, you can use access all handlersocket functionality via HandlerSocket.pm, you can download the entire Handlersocket package at, https:\/\/github.com\/ahiguti\/HandlerSocket-Plugin-for-MySQL if you already have Handlersocket via Percona Server installed, in order to get […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-14","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts\/14","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=14"}],"version-history":[{"count":25,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts\/14\/revisions"}],"predecessor-version":[{"id":239,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts\/14\/revisions\/239"}],"wp:attachment":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=14"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=14"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=14"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}