データベースの最近のブログ記事

Microsoft SQL Server の FLOOR 関数の動きが変。

例えば、DBに「返金 20,244,233円」という情報があり、これは返金なのでマイナス金額にした上で、返金額の税抜金額を計算する処理がある。
「返金 1,100円」なら -1.100円にした上で 1.1 で割って税抜 -1,000円を算出。返金 -1,100円からこの -1,000円を引いて -100円が消費税・・・のような計算をしている。
この際、端数は「0円からマイナス方向へ遠い値で切り捨てる(切り下げ)」ことになっている。「返金 126円」なら -126 / 1.1 で -114.54545 だから -115 となる。税抜が -115円で、消費税が -11円である。

ちなみに、この計算方法の是非についてはこの場で議論はしない。こういうルールで動いているのだからそれでいいのだ。

で、切り下げをする関数 FLOOR の動きがなんか変。

「返金 20,244,233円」の場合、

SELECT FLOOR(20244233 * -1 / 1.1) AS R

という SQL で R に -18,403,848.18 を切り下げた -18,403,849 が返ってきて、税抜 -18,403,849 で消費税 -1,840,384 となってほしい。

ところが、上記の SQL の結果は、R が -18,403,830 となる。19円も値がずれている。
しかし、元の数値をマイナスにするタイミング(-1 をかける)を変えるだけで、正しい数値が戻ってくる。

SELECT FLOOR(20244233 / 1.1 * -1) AS R 

だと、R列は -18,403,849 になる。
FLOOR に食わせる数値は()内で計算しているので、-1 するタイミングが前後しても関係ないと思うんだが・・・

ちなみにこれ、数字が大きい分だけ誤差も大きくなる。-1,000,000なら 1円ずれるが、-10,000,000 なら 10円ずれる。

使えねえなあ、FLOOR 関数。内部的に桁落ちしちゃってる???

ネット上を探してみたけど、「これこれこういう理由でずれる」という説明は見つけることができなかった。
「こういう使い方をしたらずれますよ」って情報は、メーカーとしてちゃんと提示しておくべきではないんかいな、マイクロソフトさんよお(^^;

Tedious ドライバーを使って SQL Server に接続できない・・・」というエントリーに書いたように、Tedious ドライバーを使って JavaScript(node.js)からの SQL Server 2019 へのアクセスだけがうまくいかない。

C# で試しにコンソールプログラム作ってアクセスしてみたけど、やっぱりうまくいくなあ。

Program.cs はこんな感じ。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

dbaccess();

        static void dbaccess()
        {
            string dataSource, initCatalog, userId, passWord, connectionString;
            dataSource = "DBSERVER\\MSSQL2019";
            initCatalog = "InazumaDB";
            userId = "sa";
            passWord = "hogepass";
            connectionString = @"Data Source=" + dataSource + ";Initial Catalog=" + initCatalog + ";User ID=" + userId + ";Password=" + passWord;
            string query_s = "SELECT TOMO_ID, TOMO_NAME, TOMO_MEMO FROM InazumaDB.dbo.M_TOMODACHI";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                //接続
                conn.Open();

                //SQL文を作成
                SqlCommand command_s = new SqlCommand(query_s, conn);

                using (SqlDataReader reader = command_s.ExecuteReader())
                {
                    //繰り返し読み込み
                    while (reader.Read())
                    {
                        //1~3列目の値を表示
                        Console.WriteLine(String.Format("{0}, {1}, {2}", reader[0], reader[1], reader[2]));
                    }
                }
                conn.Close();
            }
        }

これを実行すれば、コンソールに、

      1, アンモニア治郎, おごってくれるから友達
      2, クロム陽子, かわいいから友達
     14, ばいじん太郎, 事業に失敗したからそろそろ切ろう

こういう結果が表示される。

やっぱり、Tedious ドライバーが古くて SQL Server 2019 に対応していないってオチなんかね?

しばらく別件で忙しいので、調べている時間がないが・・・

今更だが、初めて知った。

Oracle で、DB(データベース)サーバの持っている日時だけ取得する場合、

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS dt FROM DUAL;

とかすると、dt という項目に、例えば 2023/04/06 10:31:46 という値(現在日時)がセットされて返ってくる。

プログラマじゃない人に説明すると、SELECT 文は DB 上のテーブル(データが書き込まれている表)から値を取得する命令で、FROM のあとにテーブル名を指定する。
例えば、「個人情報」というテーブルに皆の名前や住所、年齢などが記録されているとして、その中から名前と年齢を取得しようとすると、

SELECT 名前, 年齢 FROM 個人情報

とすれば名前と年齢の一覧が取得できる。

しかし、上に書いたように、その DB サーバ自体が持っている日付(システム日付)を取得したいときなどは、なにかのテーブルにその値が記録されているというものではないので(「今何時?」って聞かれて、あなたは手帳を開いて今の時間を確認するかい?」ってことだね)、テーブル名を指定するわけにはいかない。でも、SELECT 文はテーブルを読む命令だから、なんかテーブルを指定してもらわないと・・・というのが Oracle の考えなわけで、仕方ないから「じゃあ、なんかダミーのテーブルを指定しますわ」ってものが DUAL なんですな。ダミーテーブルね。

で、さっき、SQL Server 触ってて、「DBサーバのシステム時間取りたいけど、ダミーテーブル名ってなんだっけ?」としばし悩んだのである。「DUAL」じゃ「オブジェクト名 'DUAL' が無効です。」って怒られるし。

そんで、ググったら、SQL Server にはダミーテーブルなんて概念ないので、From を省略すればいいだけと。

SELECT SYSDATETIME() AS dt

で、dt という項目に 2023-04-06 16:54:39.9673299 って値がセットされるって・・・

MySQL や PostgreSQL も一緒で、(ただし、SYSDATETIME という組み込み関数ではなく)

SELECT NOW() AS dt

のように指定する。

「そうなんかあ・・・初めて知った。SELECT 文に FROM がないのは気持ち悪いな」・・・と、Oracle 世代(DBと言えば Oracle だったおっさん世代)として素直に思ったが、さっき、PostgreSQL を DBMS とした自分の過去のプログラムを見たら、普通に「SELECT NOW() AS サーバ日時」とか書いてて、「初めて知ったわけではなく、完全に忘れていたのか」と加齢の恐ろしさに愕然としている(^^;;;

32bit 版の ODBC ドライバー経由で DB に接続する Access アプリを仕事で使わないといけないんだけど、メインのノートパソコンの Office は当然 64bit 環境なので、Access でデータソースの新規作成をしようとしても 32bit 版の ODBC ライバーが一覧に出てこない。

64bit 版Access で 32bit 版ODBCドライバーって使えないのかぁ~。上位互換で可能なのかと思っていた・・・

というわけで、新しい MS-Office 買ってきて、32bit版を新規インストール。

DVD版を買ったので、ドライブに DVD を突っ込むと「Setup.exeを実行するか?」と聞かれたので Yes とすると、いきなりインストールが実行された。64bit版の・・・

途中で 64bit/32bit のどっち?って聞かれるのかと思ってたわ・・・
そういえば昔の話だが、Office って 64bit版の開発が遅れてて、64bit の Windows OS にインストールするときも、勝手に 32bit版が選ばれるので、あとでもう一度 64bit版を入れ直したりってことがあったな(^^; 今は勝手に 64bit版が選ばれるのね(笑)

インストールを途中で止めると中途半端にファイルが残ったりしそうなので、最後までインストールしてからアンインストール。改めて、Setup32.exeを実行し 32bit版 Office をインストールした。

で、無事 32bit版 ODBC ドライバーが使えることを確認。 

でも、よく調べたらこの DB、64bit版の ODBC ドライバーもリリースしてるんで、それ使えばいいんじゃない?とも思ったのだが、アプリのメーカーの動作保証対象が 32bit版なので仕方ないのである・・・(^^;
プログラム内で、仮テーブル上のデータをまるごと本テーブルにINSERTする処理があって、特になんの編集もなく突っ込むだけだから、

SELECT * INTO hon_tbl FROM kari_tbl

みたいな SQL を書いてたわけよ。(DBMS は SQL Server です)

そしたら、(もちろんこのSQL以外にもいくつかのフラグの更新SQLなどが流れているのでトランザクション処理をしてるんだけど)Roolback のときも Commit のときも、

System.InvalidOperationException: 'この SqlTransaction は完了しています。再度使用することはできません。'

という例外が発生する。
SELECT INTO すると、勝手に Commit されちゃうの???謎~

「SELECT INTO するのならトランザクション処理は必要なかろう」という乱暴な意見もありそうだが、いやいや、やっぱおかしいやろう。

トランザクション処理はなるべく範囲を小さく・・・は基本だが、いくつかのテーブルを更新してから Commit なんて普通のことで、いくらなんでも SELECT INTO したからトランザクション終わり!ってもんではない。

結局、1件ずつ INSERT 文を発行する形に直したが、なんか納得いかんなあ

そういうもんなの???>SQL Server に詳しい人!

<追記>
すぐに SNS で偉い人が「SELECT INTO は SQL ではなく DDL 扱いじゃないか?INSERT INTO SELECT ならトランザクション内として処理されるはず」とアドバイスをくださった。
なるほど~。日頃、SQL と DDL の違いを意識することのない適当エンジニアです。すみません(^^; 勉強になります!
他人が作った SQL Server を使う VB.NET のソースを修正していたら、

SELECT TOP 0 * INTO new_hoge FROM org_hoge 

みたいな SQL を発行してるんで、「0行だけ取ってきて新しいテーブルにインサートって何?」って思ったら、これ、定義だけコピって新しいテーブルを作る方法なんじゃね。

へえ、SQL Server じゃこんなことできるんだ・・・と思って、PostgreSQL で同じように、

SELECT * INTO new_hoge FROM org_hoge LIMIT 0 OFFSET 0

ってやってみたら、org_hoge の定義で新しい空のテーブル new_hoge が作られた。

「へえ」と。俺は職業プログラマなので、他のテーブルと同じ定義で空のテーブルを作るときも、ちゃんと CREATE TABLE 文を書いて作ってた。どっちみち、設計資料として必要だしね。それに、たまたまこの長い人生の中で、プログラム内で(例えばワークテーブルを作るとか?)こういうことをする機会も無かったので、こんな簡単な方法知らなかったよ(笑)
知っている人にとっては「なんだ、そんな簡単なこと!」なんだろうけど(笑)

で、MySQL でも、

SELECT * INTO new_hoge FROM org_hoge LIMIT 0,0

とやってみたら、これは

ERROR 1327 (42000): Undeclared variable: new_hoge

とエラーになった。new_hoge は宣言されてねえぞ・・・って?

調べてみると、MySQL では SELECT * INTO TABLE <新しいテーブル>・・・という書き方はサポートされていなかった。マニュアルには、

INSERT INTO new_hoge SELECT * FROM org_hoge LIMIT 0,0

にしろ・・・と書いてあるらしいが、そもそも MySQL にはこういう方法での CREATE TABLE 機能は無いそうで、この場合も「new_hoge なんてテーブルは無ぇよ!」と怒られるらしい。

結局、MySQL では「SELECT した情報で空のテーブルを新しく作る」ということはできないようだ。

素直に、

CREATE TABLE new_hoge LIKE org_hoge

でテーブルを作りなさいということだね。

他の DBMS ではどうなのかは面倒くさいので調べていない(笑)

SQL Server のストアド・プロシージャは、SQL Server Management Studio で修正することはあったんだけど、新規に作ったことはなかった。
(メンテナンスし辛いので(DB内のすべてのストアド・プロシージャに対するワード検索とか、いちいち SQL 書かないといけないし)、できるだけ使いたくない(笑))

修正のときは、ソースを直して「実行」するだけだったので、(今回は、もともと存在するストアドの内容をコピーして、色々修正を加えた上で新規登録する)同じように最後に「実行」すると、

メッセージ 208、レベル 16、状態 6、プロシージャ p_data_create、行 64 [バッチ開始行 7]
オブジェクト名 'p_data_create' が無効です。

というエラーが出る。

これは、

ALTER PROCEDURE [dbo].[p_data_create]

になっていたため。
ALTER は 「変更する/改める」という意味だから、変更対象のストアドが見つからないってエラーが出てた。

新規作成時は

CREATE PROCEDURE [dbo].[p_data_create]

になってないとね。DB や TABLE を作るときと一緒だから。

ソースコピーしてきて、とりあえずプロシージャ名だけ一括変更して登録しようとしたのが敗因だった(^^; ちゃんとソースは読みましょう(笑)

登録後は CREATE を ALTER に変更しておく。
Microsoft Access で、「クエリデザイン」→「SQL ビュー」として直接以下のような SQL を叩くと、

SELECT 
    Format(Uri_day,'mm/dd') As Uri_day,
    Denpyo_no
FROM 
    T_Uriage 
WHERE 
    Denpyo_no = '001002'

なんか、「クエリ定義の SELECT で指定されている別名 'Uri_day' が循環参照を発生させています。」とエラーになる。

20210309_access1.jpg

どうも、式(Format)の中で使っている列名(Uri_day)と同じ名前を、計算結果の別名には使えないようだ。別名を Uri_Uri_Day とかに変えたら上手くいった(笑)。

Excel でも同じようなエラーが出るケースがあるようなので、Microsoft べったりの開発者にとっては「常識」なのかな?
でも、つまりこれって、a = a + 1 とか、そういうことが出来ないっていうのと同じことじゃない?相当変な話やな(笑)

他の DBMS でもこんなことになるっけ?と思い、MySQL で似たようなことをやってみた。

mysql> SELECT
    ->     DATE_FORMAT(cdate,'%m/%d') AS cdate,
    ->     user_no
    -> FROM
    ->     t_user
    -> WHERE
    ->     user_no = 'AB129'
    -> ;
+-------+---------+
| cdate | user_no |
+-------+---------+
| 06/10 | AB129   |
+-------+---------+
1 row in set (0.01 sec)

ならんやん!!正常に実行された。

やっぱ、なんというか・・・Microsoft めぇ~な話やなあ(笑)。Microsoft の「常識」は社会の「非常識」(笑)

※ちなみに、VB.NET のプログラム内から同じ SQL で Access を参照してもエラーにはならない。

おいっ!Microsoft!!(^^;

※「循環参照」を「環境参照」って間違えてたことにさっき気づきました(^^; 単なる打ち間違いです。
VisualBasic から ADO.NET を使用して SQL Server に接続・使用する簡単な例。

SQL Server のインストールや、DB作成、TABLE作成は済んでいること。

(1) データソースの選択と、DB接続設定を行う

    • [表示]→[サーバーエクスプローラー] でサーバーエクスプローラーを表示する。
    • サーバーエクスプローラー上にある DB アイコン(データベースへの接続)をクリック。
    • 「データソースの選択」ダイヤログが開くので、データソースで「Microsoft SQL Server」を選択。データプロバイダーは「.NET Framework SQL Server用データプロバイダー」を選択する。[続行]ボタン押下。
    • 「接続の追加」画面が開くので、「サーバー名」を設定。サーバ名がわからなければ SQL Server Management Studio 等で調べる。うちの場合は「XVIDEOS\SQLEXPRESS」。XVIDEOS は PC名。(一覧から選択するのかと思ったが、フルに手入力しないといけなかった)
    • 「サーバにログオンする」の「認証」を「SQL Server 認証」に変更する。ユーザ名とパスワードが入力可能になるので入力。(あらかじめ SQL Server Management Studio で作成したもの)
    • 「データベースへの接続」の「データベース名の選択または入力」を選択。DB名を入力する。
    • 画面下の「テスト接続」ボタンを押してみて正常に接続されれば「OK」ボタン押下。(うまくいかなければ、DB名やユーザアカウント、パスワードが間違っていないか?)

(2) データソースの登録

    • [表示]→[その他のウィンドウ]→[データソース]でデータソースウィンドウを表示。
    • データソースウィンドウの上の一番左の DB アイコン(データソースの追加)をクリック。
    • 「データソースの種類の選択」画面で「データベース」を選択し「次へ」ボタン押下。
    • 「データベースモデルの選択」画面で「データセット」を選択して「次へ」ボタン押下。
    • 「データ接続の選択」では、対象の「データ接続」を選択し、「はい。重要情報を接続文字列に含めます」を選択し「次へ」ボタン押下。
    • 「接続文字列をアプリケーション構成ファイルに保存する」画面では、そのまま「次へ」ボタン押下。
    • 「データベースオブジェクトの選択」画面では、プログラムから参照する TABLE などをすべて選択する。「完了」ボタン押下。

(3) プログラム開発

サンプル用に、超簡単なプログラムを作る。
ID と NAME という項目を持つ m_user という TABLE を参照し、NAME を画面に出すというプログラム。
例えば、ID='admin' の NAMEは'偉い人'である。

20200701_VB1.jpg

開発する画面は「IDを入力し『参考』ボタン(・・・参照と打ったつもりなのに(^^;)を押すと、下に『名前は○○です』」と表示する」という単純なもの。

「参考」ボタンの Click イベントのソースを下記のとおり書いた。

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim sql As String

        'SQLステートメント
        sql = "SELECT name FROM m_user WHERE id='" & TextBox1.Text & "'"

        Label1.Text = ""

        'コネクション指定
        Using connection As New SqlClient.SqlConnection(My.Settings.nandfConnectionString)

            'データコマンドを定義する
            Using command As New SqlClient.SqlCommand(sql, connection)

                'コネクションを開く
                connection.Open()

                'データリーダの生成
                Dim dr As SqlClient.SqlDataReader = command.ExecuteReader()

                'データが存在する場合、label1にセット
                '検索結果は1件だけ
                Do While dr.Read
                    Label1.Text = "名前は " & dr("name") & " です"
                Loop

                'データリーダを閉じる
                dr.Close()

                'コネクションを閉じる
                connection.Close()

            End Using

        End Using

    End Sub

いやあ、ADO.NET はデータソースの選択までのアレコレが煩わしいね。まあ、おかげでソースを書くのは楽なんだけど(笑)

それより、うーむ・・・まだ 5年前にやった VB.NET 案件で DB 利用をどのようにコーディングしたか思い出せない。なんとなく、インタフェースは ADO.NET だったような気がするが・・・
SQL Server に接続して DB を新しく作ったり色々と操作をお個合うためのツールが「SQL Server Management Studio」(SSMS)である。
今回、v18.5.1 をインストールした。

んが、起動してみると英語表記である。日本語になっていない。まあ、DB に関することだから英語でも使えなくはないが、やはり設定画面等は日本語の方が使い易い。英語苦手なもんで(^^;;;

 20200701_SQLManagement1.jpg

というわけで、[Tools]→[Options]→[Environment]→[International Setting] と設定画面を進み、 Language を「Same as Microsoft Windows」にしてみた。
多分、「Windows と同じ言語」ってことだろうなあと。
「Changes to the language setting will not take effect until the environment is restarted.」と言われたので、念の為 Windows の再起動までしてみたのに・・・

英語のままやんけ(^^;;;

調べてみると、なんか、SSMS は簡体中国語、繁体中国語、英語 (米国)、フランス語、ドイツ語、イタリア語、日本語、韓国語、ポルトガル語 (ブラジル)、ロシア語、スペイン語それぞれのインストーラーがあるようだ・・・


というページの、最初に出てきた「SQL Server Management Studio (SSMS) のダウンロード」というリンクからダウンロードをすると、SSMS-Setup-ENU.exe という英語版のインストーラーになっていた・・・。ページのもっと下の「日本語」というリンクをクリックすれば、SSMS-Setup-JPN.exe という日本語版がダウンロードされるようである・・・

一旦、英語版をアンインストールし、日本語版をインストール。これで無事、日本語版の SSMS が使えるようになった。

 20200701_SQLManagement2.jpg

ただ、英語版だと接続時にサーバ名が自動でセットされていたが、日本語版は自分で参照しないといけなかった。
しかし、次の接続からはちゃんと前回選択した DB がディフォルトでセットされるので問題なしである。

このアーカイブについて

このページには、過去に書かれたブログ記事のうちデータベースカテゴリに属しているものが含まれています。

前のカテゴリはWindowsです。

次のカテゴリはパソコンです。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。

月別 アーカイブ

電気ウナギ的○○ mobile ver.

携帯版「電気ウナギ的○○」はこちら