ドリンクバーゲン会場

サブルーチンから複数の値を取得する2011年01月25日 09時50分18秒

今回はサブルーチンから、複数の値を取得したいと思います。
現在、定期試験の採点に関するプログラムを作成中で、
前回は平均を求めたのですが、今必要なのは平均点だけでなく、
合計点も必要になります。

平均点と合計点は、関数以外は違いがないので、平均点と合計点を一度に求め、
それを取得します。

データ取得の部分をサブルーチン化し、合計と平均を取得していきます。

採点テーブル
学籍 科目 点数
A01 国語 75
A01 数学 77
A01 英語 48
A02 国語 48
A02 数学 55
A02 英語 48

前回平均点を求める際に使ったテーブルです。
平均を求めるSQLは次のようなものです。

SELECT ROUND(AVG(CAST(点数 as decimal(3,1))),1) as 平均
from 採点テーブル where 学籍 = 'A01'

今回は合計も求めるので、以下のように修正します。

SELECT ROUND(AVG(CAST(点数 as decimal(3,1))),1) as 平均 , SUM(点数) as 合計
from 採点テーブル where 学籍 = W_GAKUSEKI

これで、合計と平均を出力します。

前回は学籍を直接指定していたのですが、
今回は学籍を変数にし、メインから引き渡します。

メインのプログラムです。

Dim w_dt As Object
Dim wret_cd as integer
Dim wgokei as integer
DIm wheikin as decimal

w_dt = data_set("A01")
wret_cd = wgh(0)
wheikin = wgh(1)
wgokei = wgh(2)

サブのプログラムです。

Private function data_set(W_GAKUSEKI as string) As Object
dim WSQL as String
dim ret(2) as Object

WSQL = "SELECT ROUND(AVG(CAST(点数 as decimal(3,1))),1) as 平均 , SUM(点数) as 合計 from 採点テーブル where 学籍 = '" & W_GAKUSEKI & "'"

Dim cnStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("GAKUSEKI").ConnectionString
Using connection As New SqlConnection(cnStr)
Dim command As New SqlCommand(WSQL, connection)
connection.Open()
Try
Dim dr As SqlDataReader = command.ExecuteReader
If dr.Read Then
ret(0) = 0
ret(1) = dr("平均")
ret(2) = dr("合計")
End If
Catch ex As Exception
ret(0) = -99
End Try
'コネクションを閉じる
connection.Close()
End Using
Return ret
End Function

データベースの接続はサブで行っています。
学籍を取得しSQL文を作成して、読み込んだ結果をretに入れ、
メインに返しています。

これでそれぞれの値を取得することができます。
実際にはエラーチェックなどが入りますが!
複数の項目を返す場面はたくさん出てくるので、
とりあえず、やり方は覚えておいたほうが良いと思います。

SQLSERVERでの平均の求め方2011年01月24日 10時31分13秒

今回は、ちょっとした小技をひとつ!
データベース上に登録されているデータの平均点を取る方法です。

学校で使うシチュエーションとしては、成績を出す場合に平均点が必要になります。
100点満点で何点かですが、個人の平均もあれば、科目の平均、クラス単位での平均など、結構頻繁に使用することになります。

今回は、SQLで直接平均値を求める方法です。

学校では、SQLServerを使っているので、SQLServerで使える方法ですが、
他のデータベースでも、同じような感じでいけると思います。

まず、平均を求めるのは、AVG関数です。
SQL上でAVG関数を指定することで平均値を算出します。

データは下記のような形で考えていきます。
採点テーブル
学籍 科目 点数
A01 国語 75
A01 数学 77
A01 英語 48
A02 国語 48
A02 数学 55
A02 英語 48

学籍がA01の平均を求める場合のSQL文を作ってみたいと思います。

SELECT AVG(点数) as 平均 from 採点テーブル where 学籍 = 'A01'

SQL文はこんな感じでいけると思うので、実行してみます。
結果は下記のとおりです。

平均
-------
64

点数はintegerになっているので、小数点以下が切り捨てられ、
64になってしまいました。
小数1位まで表示を出したいので、このままでは使えません。
なので、まず小数の表示がでるようにSQLを修正します。

SELECT AVG(CAST(点数 as decimal(3,1))) as 平均
from 採点テーブル where 学籍 = 'A01'

decimalを使って小数点以下を表示させます。

平均
-------
64.727272

DecimalでCASTした結果、小数点以下が表示されましたが、
小数1位までの表示にしたいとおもいます。
四捨五入の関数であるROUNDを追加します。

SELECT ROUND(AVG(CAST(点数 as decimal(3,1))),1) as 平均
from 採点テーブル where 学籍 = 'A01'

ROUNDで表示する小数を1位に指定して実行しました。

平均
-------
64.7

いろいろと指定をしないと思うように表示できませんが、
面倒くさい気もしますね!

ストアドプロシージャの作成2008年10月14日 09時44分32秒

ストアドプロシージャの追加
.NET用の参考書がようやく届き、これからASP.NETを勉強していきたいと思います。
今回はデータベース接続をするための準備を行います。

データベース接続するには、SQLをコーディングする必要がありますが、プログラムの中にSQLコードを直接書き込むこともできますが、今回はストアドプロシージャを作成し、プログラムからはストアドプロシージャを呼び出すようにします。

よく使うSQLコードをストアドプロシージャとして登録することで、プログラム上にSQLをコーディングするよりもレスポンスを改善することができます。

例えば、学校では、各クラス毎に試験の成績の一覧表を出力しますが、クラス以外に違いがないので、ストアドプロシージャとしてSQLを登録しておけば、クラスが変わっても使いまわしてくれます。
クラスなどの変更する部分は、パラメータとして値を受け渡しできるようにしておきます。

しかし、プログラム上にSQLを作成した場合、実行するたびにそのSQLコードを毎回解釈するための処理が余計にかかってしまいます。

VisualStudioはサーバーエクスプローラから、直接SQLServerの設定を変更することができ、ストアドプロシージャの作成も行うことができます。

今回は、とりあえずテストのためにログイン用の簡単なものを作成してみます。
*******************************************
ALTER procedure ログイン
@id varchar(10),
@pass varchar(10),
@kyoin varchar(20) output
AS
select @kyoin=KYOIN from 教員
where LOGID=@id and PASS=@pass
if @@rowcount<1
begin
select @kyoin='未登録'
end
*******************************************

1行目はストアドプロシージャの名前を指定しています。
ログインという名前を付けました。
2行目から4行目はパラメータを登録しています。
ログインのためのIDとパスワードは入力用のパラメータです。
最後のkyoinは後ろに「output」とありますが、これは教師名を取得するための項目です。
ログインが完了したときにログインした先生の名前を取得します。
パラメータには、頭に必ず@をつけるようにします。

ASの下にTransact-SQL文を追加します。
教員テーブルには、ID,パスワード、名前が登録されており、IDとパスワードでチェックし名前をとってきます。
名前を取得する必要があるので、@kyoin=KYOINでパラメータへの受け渡しを設定しています。
そのあとの「If」では、データがない場合に名前を受け取るパラメータに”未登録”とセットしています。

入力できたら保存して、実行してみます。
VisualStudioのサーバーエクスプローラにログインが追加されるので、右クリックして、「実行」を選んでやると、「ストアドプロシージャの実行」という画面が出てきます。

パラメータを入力することができるので、あらかじめ登録されているIDとパスワードをセットし、「OK」ボタンで実行してください。
出力画面に教員の名前が表示されればOKです。

ACCESSからSQLServerの接続2008年07月26日 09時50分38秒

現在、Javaでの開発を準備中ですが、現在の業務で不便なことはアクセスを使用してとりあえずしのいでいます。

アクセス単体で使うことはなく、通常はSQLServerのデータを更新するようにしているのですが、これにはいくつかの手順が必要なので、今回はその方法を解説します。

まず最初はデータソースの設定です。
「管理ツール」の中に、「データソース(ODBC)」というアイコンがあるので起動します。

いくつかタブがある画面が表示されるのですが、その中で「システムDSN」タブを選びます。
ただし、PCにユーザーが一人の場合などは、「ユーザーDSN」でも大丈夫です。
私の職場では、共有利用するので、すべてのユーザーから利用できる「システムDSN」を利用します。

「追加」ボタンで、SQLServerを登録していきます。
SQLServerのドライバを選択し、データソース名、サーバー名、ログインIDの設定、既定のデータベースの設定などを登録します。

まず、データソース名は自由な名前を付けることができます。
複数のデータソースを登録する場合には、管理しやすいように、
わかりやすい名前をつけます。

サーバー名は、SQLServerがインストールされているサーバー名を記入します。

ログインIDの設定は、職場では、「WindowsNTの認証メカニズムを使う」にチェックを入れています。
環境によっては、IDとパスワードを登録する必要があります。

既定のデータベースの設定では、読み込むデータベースを選択します。
これは、一つのサーバーに複数のデータベースが登録されている場合がありますので、どのデータベースを使うのかを指定します。
職場では、3つのデータベースが同じサーバーに登録されているので、
それぞれを登録しています。

登録が済んだら、一覧に表示されていると思います。

データソースの登録が完了したら、アクセスを起動し、新規のデータベースを作成します。

ACCESS2007の場合、「外部データ」タブを選択し、「インポート」から「その他」を選びます。
一覧の中に「ODBCデータソース」というのがあるので、選択します。

データの保存方法を聞いてくるのですが、インポートとリンクの2種類がありますが、ここではリンクを選択します。

インポートを選択してしまうと、SQLServerのデータがすべてACCESSに入ってしまい、登録などを行ってもSQLServerには反映されません。
リンクを指定すると、登録などの処理がSQLServerに反映されるようになります。

次にデータソースの選択画面が表示されます。
この画面で「コンピュータデータソース」タブを選択すると、登録したデータソースが一覧の中に表示されているので、選択して「OK」ボタンをクリックします。

「テーブルのリンク」という画面が表示されるので、利用するテーブルをクリックして選択します。
すべてのテーブルを使う場合は、「すべて選択」ボタンを押せば、すべてのテーブルが選択されます。

「OK」ボタンを押すと、リンクテーブルが作成されます。
ACCESS内に作成されたテーブルは表のようなアイコンですが、
リンクテーブルでは、地球に矢印のアイコンになりますので、ACCESSに登録しているテーブルとは簡単に区別がつくようになっています。

後は、普通にクエリーやフォームを作っていけばACCESSから、SQLServerのデータを操作できるようになります。

Eclipse JDBCドライバー定義2008年06月13日 10時09分33秒

Eclipseで開発を進めるために、SQLServerと接続する必要があります。
そこで今回はSQLServer用のJDBCドライバーを使ってEclipseからSQLServerに接続するための設定を行います。

まずSQLServer2005用のJDBCドライバーをダウンロードしてインストールします。
次に、「sqljdbc.jar」ファイルをテスト用のワークスペースにコピーします。

次にEclipseメニューの「ウィンドウ」から「設定」を選択します。
左の一覧の下のほうにある「接続」から、「ドライバー定義」を選択すると、ドライバーの一覧が表示されるので、「SQLServer」の「2005」を選択し、「追加」ボタンをクリックします。

ドライバー定義の画面が出ますので、
JDBCドライバーを追加します。
JDBCドライバーは先ほどワークスペースにコピーしたものを選択します。

最後にプロパティにアドレスやID等を入力したら、「OK」ボタンを押して登録完了です。
これは定義だけで、まだつながったわけではありません。
次は実際に接続を行ってみたいと思います。