php

LAMP環境からSQLServerインスタンスの接続ポート番号を取得する

制作中の業務用ウェブシステムがSQLServer縛りなので、ここ２~３年急にSQLServerをいじることが多くなってきています💦 別の大半の仕事は MySQLかSQLiteなのですが、やはり WindowsのデファクトスタンダードのDBMSはSQLServer、ツール類は断トツに整備されていじりやすい！

そんなことはともかく。いつも前置きが長いです。すみません。

通常SQLServerインスタンスへコンピューター名(or IPアドレス)\インスタンス名 で接続するので特に接続ポート番号(動的ポート)を意識せずとも接続できると思われます。

ただ、僕はLAMP環境メインでやってきているので、ASP.NETとかいうのは肌に合いませんし💦 今更関わろうとも思いません。（C#でのデスクトップアプリやツール類の作成は趣味でやっているのですが・・・）
Linux側からWindows Server＋SQLServerにする場合、大抵の場合SQLServerインスタンスのポートを固定して、ファイヤーウォールでそのポート番号を開けて・・・というやり方が一番ラクですし、今開発中のものもそういう環境でやると思います。

で、ふと思ったのですが、動的ポートって「動的」って言うんだから変わることを前提にしないといけないなぁ・・・と漠然と思いまして、カンタンなSQLServerインスタンスの情報を取得する方法がないかと思いまして・・・このエントリはその時に調べた内容です。

Windowsクライアントメイン(大概がAccessランタイムアプリ)の職場環境だと、大抵の場合「名前付きインスタンス」で動的ポートになっていると思います。職場環境によってはポート固定への変更を頑なに拒まれることもあろうかと思いますし、結局のところ一体何番で接続していいのか困ることがあります。

とりあえず、ググるが、SQLServer Browserサービスに直接問合せることで、SQLServerインスタンスの情報を得られますよ~、的な、事しかわかりません。

マイクロソフトのドキュメントによると、SQLServer Browserへの問合せはSSRP(SQL Server Resolution Protocol)というプロトコルでアクセスするみたいです。
[MC-SQLR]: SQL Server Resolution Protocol

これを読むと、UDP/1434ポート(SQLServer Browserサービス)へ、下図のように、インスタンス名の前に0x04をつけて問い合わせると、セミコロン区切りで情報を投げてくれるそうです。

(上記URLのドキュメントより抜粋)

なんだ！カンタンじゃん！（ほんとかよ。。。）

もちろんですが、SQLServerホストのファイヤーウォールでUDP/1434ポートを開けておく必要がありますが、普通は空いているでしょう、さもないと外部クライアントから一切接続できなくなるし。

とりあえず、ターミナルからローカルで動いているSQLServer(Deverlopper edition)ホストへ netcat で叩いてみる。
インスタンス名は既定のインスタンス(MSSQLSERVER)。

$ echo -ne "\04mssqlserver" | nc -u localhost 1434
Y ServerName;XXXXXX;InstanceName;MSSQLSERVER;IsClustered;No;Version;14.0.1000.169;tcp;1433;;

一応ローカルで動かしているSQLServerは固定ポートで動かしているので 1433になってまつね。。。
これだけだとプロトコル・ポート番号とインスタンスのSQLServerのバージョンなどが取れます。IsClusteredは・・・なんなんでしょうね？無知ですみません。

結果のフォーマットは、先頭(0バイト目)に1バイトの識別記号？の\x05、1バイト目にペイロード長(終端ヌルを除く)を示す16ビット整数、3バイト目からペイロード・・・という風な感じ。ペイロードはセミコロン区切りで、名前1;値1;名前2;値2;….;; という風なフォーマット。これなら簡単に連想配列かなんかにパースできます。

んで、インスタンスの簡易情報を取得するコードを書いてみる。

この関数を実行してみると

<?php
require 'query_sqlbrowser.php';
print_r(queryNamedInstance('IPアドレス','インスタンス名'));

/***** 出力 *****
Array
(
  [ServerName] => コンピュータ名
  [InstanceName] => インスタンス名
  [IsClustered] => No
  [Version] => 10.50.4000.0
  [tcp] => 49179
)
****************/

とりあえす、これで得られたポート番号をキャッシュしておき、接続試行がタイムアウトしたら、キャッシュ更新・・・という感じで組み込もうかと思います。

WSL(Ubuntu)-PHPからSQLServerへの道のり

2019年5月20日 若干修正

「CentOSからWindows上のSQLServerへの道のり」のつづきです。

LinuxからWindows上の SQLServerへのアクセス方法については、世間的にはあまり需要がないのでしょう。まぁ、Linuxとかのunix系のOSでは、SQLServerなんて採用しないし、逆も然り。。。困ったもんだ。。。

さて、Linuxの各ディストリビューション用のMicrosoft製のODBCドライバが提供されていて手順を踏むと、LinuxからSQLServerへODBC経由でアクセスすることができました。

今度は LAMP環境、PHPから文字化けさせずにCRUDすることができるか、ウェブアプリケーションをちゃんと実装できるか、確認です。とりあえずLinux各ディストリビューション用にマイクロソフト製のODBCドライバをインストールしていることが前提です。

PHPでたとえば、以下のようなコードでテーブルを作成し、SQLServer Management Studioとかで確認すると、日本語が全部文字化けします。
sqlcmdコマンドで日本語交じりのSQL文を発行すると化けないので、おそらく、PHPの問題でしょう。DSNで odbc: を使うのがだめなんでしょうね。odbcドライバをインストールしたんだから、当然odbcプレフィックスを使うものだと勘違いしてまして、これが罠でした・・・。日本語関係の処理が全くされない。。。この辺、よくわからん・・・。知識が圧倒的に足りない。SQLServerのODBCドライバとsqlsvr(PHP SQLServer用のドライバ？)の関係が全然わからない。PHPのマニュアル見てもよくわからない・・・。

// これが間違い
$pdo = new PDO('odbc:Driver={ODBC Driver 13 for SQL Server};Server=localhost;Database=Sample','dbuser','dbpass');
$pdo->exec('CREATE TABLE tbl_hoge(hoge_id int NOT NULL,hoge_txt nvarchar(100))');
$pdo->exec("INSERT INTO tbl_hoge values(1,N'日本語で挿入・・・なんかエロいな')");

接続文字列でエンコーディング関係のパラメータがあるのかなと、調べてみたけどないっぽい。さらにネットでの情報がほとんどなく。。。ヒットするのはFreeTDSを使ったものばかりで、多くはPHP5の情報ばかりで、PHP7以降のものは出てこず。。。

で、困ったときはグーグルで検索・・・ではなく、まずマイクロソフトの公式のドキュメントを漁りましょうってことですね(^^;
ググらなくても最近のマイクロソフトはちゃんとドキュメントを用意してくれてます。（MSも変わったな。。。）

Build an app using SQL Server


上記サイトの PHP ⇒ UBUNTU をクリックすると、Linux版 SQL Serverのセットアップから詳細な手順が書かれています。英語ですが、簡単な英文なので読みましょう！英語は喋れなくても、聴き取れなくても一向に問題はないけど、英語の読解能力は必須！ちゃんと読めるようにしましょう！！！
日本人がいくら優れたシステムを作れたとしても、英語で説明できなければ、世界には通用しませんし、この先世界中の開発者がこぞって日本語を理解してくれるとは到底思えないからです。

今までSQLServer用のODBCドライバとPHPでのSQLServer用のドライバ、それぞれが別々に説明がされてて、一体何がどうなってってるのか理解できなかったけど、上記サイトで Ubuntu + PHP では、この手順、Redhat系では、この手順、という風にまとめて手順が説明されているので、これで悩まなくて済むようになった。PHP7以降限定ですけどね。 まぁPHP5系はこの先消えていく(deprecated)のでまぁいいか、という感じ。

要するに ODBCドライバに加えて、peclでsqlsvr/pdo_sqlsvrをインストールすれば完了。やっと 非Windows環境でも労せずSQLServerへフツーにアクセスできるようになりました。

上記マイクロソフトのサイトには、Linux用のSQL Serverのセットアップから説明されていますが・・・SQLServerは Windows用のものをインストールしましょう。というか、開発マシンがWindowsの場合は、素直にWindows用のSQLServer Developper Editionをインストールした方がいいです。開発目的ならライセンスフリーですし。（サーバー環境で運用はできないけど）
開発マシンがMacやLinuxの方は・・・そもそもSQLServerに接続するような仕事してないでしょ？w

WSL(Ubuntu)でPHPをインストールしていない場合、aptで適当にphp7系をインストールしてください。その際、php-dev パッケージは必須です。
また、最新のsqlsrv,pdo-sqlsrvドライバはphp7.0をサポートしないので、最新に拘る人はphp7.1以上にするべし？php7.0をサポートするMicrosoftのsqlsrv/pdoドライバのバージョンは5.3です。最新は確か5.6だったかな？
システム要件：https://docs.microsoft.com/ja-jp/sql/connect/php/system-requirements-for-the-php-sql-driver

$ sudo apt install php php-cli php-dev php-... (必要なパッケージ)
# apacheで動作するには必要
$ sudo apt install libapache2-mod-php

1, 先に マイクロソフトのサイトからODBC Driver for SQLServer をインストールしておく（上述）

2, sqlsrv/pdo_sqlsrv のインストール (ubuntu 1804 の場合)

$ sudo apt install php-dev
$ sudo pecl install sqlsrv
$ sudo pecl install pdo_sqlsrv
$ sudo su 
# echo "extension=pdo_sqlsrv.so" > /etc/php/7.2/mods-available/pdo_sqlsrv.ini
# echo "extension=sqlsrv.so" > /etc/php/7.2/mods-available/sqlsrv.ini
# exit
$ sudo phpenmod -v 7.2 -s ALL  pdo_sqlsrv sqlsrv

apache2な人は再起動するべし。
参考リンク：the installation instructions on Microsoft Docs.

3, PHPで確認
PDOで、DSNのプレフィックスをodbc: ではなく sqlsrv: で。

$pdo = new PDO('sqlsrv:Server=localhost;Database=Sample','dbuser','dbpass');
$pdo->exec('CREATE TABLE tbl_サンプル(hoge_id int NOT NULL,hoge_txt nvarchar(100))');
$pdo->exec(sprintf('INSERT INTO tbl_hoge values(1,N%s)',$pdo->quote('日本語で挿入・・・なんかエロいな'));

参考リンク： https://github.com/Microsoft/msphpsql/tree/master/sample

文字列リテラルをNプレフィックスをつけずにINSERTとすると絵文字が化ける。文字列リテラルの場合は Nプレフィックスを忘れずに。。。
ただ、プリペアドステートメントを利用する場合、自動的にNプレフィックスはつけてくれるみたい。SQLServer Data Profilerで実際実行されたSQL文を確認すると、PDO::execでは自動的にNプレフィックスはつけてくれないみたい。まぁ当たり前といえば、そうなんだけど。。。

あと、東京オリンピックまでには、apt install php-sqsrv / yum install php-sqlsrv とか標準のリポジトリでインストールできるようにしてほしい。。。

＼(^o^)／

PHPでのExcel吐き出しCSVファイルの処理

何度となくハマったPHPでのCSV処理・・・もういやだ。
最初は、fgetcsv で setlocaleし忘れでハマり、改行を含むセルでSplFileObjectを知り、SJIS-WIN でハマり・・・もういやだ。

もうCSVで涙目になるのは嫌なので、一個クラスを作る。

要はCP932エンコードされたCSVファイル用の SplFileObject が欲しい！ってことなんですけどね。
マルチバイト用のSplFileObject、mb_SplFileObjectみたいなもの標準で入れてくんないのかな・・・。

ってなわけで？、
SplFileObjectから派生したクラスを定義。ついでに、read / readAll / each メソッドを追加。これは自己満。
コンストラクタでCP932なCSVファイルを引き受けて、UTF-8に変換した作業用ファイルを作成して、デストラクタで消去。
はじめは file_get_contentsで一気に変換しようかと思ったが、巨大サイズのファイルを渡されるとmemory_limitに引っかかるので・・・(^^;
/usr/bin/nkf とか /usr/bin/iconv とかに丸投げしようかと思ったけど、Windows環境だとメンドーだし。

とりあえず、 jQuery のeachみたいな感じのものが欲しかったので・・・

クラス作るほどのものじゃないんだけどなー・・・・

PHP配列の悪夢、再び

おれって、相当アホ!!! ってな備忘録。

こんなコードを書いた。（実際に書いたコードはもっと複雑ですが・・・）

&lt;?php
/******************************

 配列の中身を出力する。だけ。

******************************/
function array_wrong($files)
{
  $num = count($files);
  for($i=0;$i&lt;$num;$i++)
    {
      printf(&quot;%d = %sn&quot;,$i,@$files[$i]);
    }
}

もうこの時点で、「お前の書くコードなんて一切使わない」宣言されるだろう。
はい、そうですね。僕もそう思います。(T-T)

こんな基本的な間違いを平気で犯す自分に自己嫌悪して数時間立ち直れませんでした・・・。

たぶん、次のような引数を渡すと期待するように動作するでしょう。

&lt;?php
$files = array('abc.txt',
               'xyz.pdf',
               'def.jpg');

//たぶん上手くいく。オール、オッケー
array_wrong($files);

しかし・・・次のようにしたら・・・

&lt;?php
$files = array('abc.txt',
               'xyz.pdf',
               'def.jpg');

unset($files[1]);

$files[10] = 'zzzz.gif';
$files[7] = 'bbbbb.ai';

array_wrong($files);

僕が期待した出力には当然なりません。とほほ。

こういうバカ・コードが原因だと気づくのに、半日要してしまったよ・・・・。
PHPの配列とcount関数の挙動が全然理解してない証拠っすね。。。恥ずかしい。。。

原因が分かれば・・・foreachもしくは、count関数を使わず・・・ループすればいい。

&lt;?php
function array_right($files)
{
  ksort($files);
  foreach($files as $i =&gt; $file)
    {
      printf(&quot;%d = %sn&quot;,$i,@$files[$i]);
    }
}

//もしくは・・・count()関数でループ回数を決定するのではなく配列キーの最大値を利用する。
function array_right($files)
{
  ksort($files);
  $num = max(array_keys($files));
  for($i=0;$i&lt;$num;$i++)
    {
      if(isset($files[$i]))
        printf(&quot;%d = %sn&quot;,$i,@$files[$i]);
    }
}

はぁ・・・。いつになったら使える人間になれるんだろう・・・。

MySQL + UPDATE + PDOStatement::rowCount の罠

MySQLのセットアップはテーブル作成とかも含めて、非常に面倒なので、作成途中(開発中)はSQLiteで作って動作確認して、作成大詰めの段階でMySQLに切り替え完成・・・という工程はわりかし一般的？だと思う。ファイル一個でバックアップ・リストアも簡単で開発効率も上がります、僕は。

で、SQLite + PDO で何の問題もなくある程度コーディングが終わり、MySQLに移行して検証していると、おかしな挙動の解決に半日かかってしまった備忘録のエントリです。

データを空更新、特定の行を取得して、編集画面表示、その後、内容を変えずに同じデータで更新すると、update文は成功するのに、作用した行数が０を返す・・・。だいたい下のような感じ。

&lt;?php
/*************************************************************

  あらかじめ下記mysqlクライアントで実行

  &gt;&gt; CREATE TABLE test_table(id INTEGER,data CHAR(255));
  &gt;&gt; INSERT INTO test_table values(1,'Kenji Nakagawa');

**************************************************************/
$pdo = new PDO('sqlite:log.sqlite');

if(modify_name(1,'Kenji Nakagawa'))
{
  header('location: list.php');
}

function modify_name($id,$name)
{
  global $pdo;

  $rv = false;
  $sql = 'update test_table set name = ? where id = ?';

  if(false !== ($stmt = $pdo-&gt;prepare($sql)))
    {
      if(false !== ($result = $stmt-&gt;execute(array($name,$id))))
        {
          $rv = $stmt-&gt;rowCount();
        }
    }

  return $rv;
}

この一連のコーディングでの最大の失敗は、rowCount()メソッドが返す行数で、update文の成功・失敗を判断したところ。分かってしまえば、何でもないことだけど、はまってしまった。

分かったことは、

MySQL + UPDATE文の実行では、PDOStatement::rowCount() は「実際に変更した（あった？）行数」(←ここ重要)を返す

・・・・ということ。

元々のレコードと同じデータをupdateすると、update文の実行はfalseは返さない（成功する）が、rowCount()は1ではなく、0を返す・・・。PHPサイトのドキュメントを検索したらちゃんと書いてありました・・・・とほほ(T-T)

UPDATE を使用する場合、MySQL では新旧の値が同じときには更新処理を行いません。 このことから、必ずしも mysql_affected_rows() の返す値が マッチする行の数と一致するとは限りません。返す値は実際に更新処理が行われた 行の数です。

そんな・・・・僕が勉強したときにちょろっと読んだMySQL入門書には書いてない！(笑)

ってわけで、解説書なんかで勉強するときは、いかに良い本に巡り会うことの重要性を改めて痛感しました。。。