Ensemble(Cache)通过ODBC连接Mysql数据库并调用其存储过程

       有同事问到如何用caché数据库连到Mysql数据，并且调用Mysql数据库中的存储过程，开始认为mysql不可能为m语言单独写驱动所以不能调用存储过程。

     在mysql官网可以看到mysql提供了以下几种方式连接它,见下图. (ODBC,NET,JDBC,Python,C++,C,PHP) 确实没有M语言的连接驱动。

      

后面领导说可以看看sql gateway，去研究了下，发现caché有界面来配置SQL Gateway Settings界面，进入方式System Management Portal--Home>Configuration>Object/SQL Gateway Settings-SQL Gateway Connections,界面可以Create New Connection.

创建connection得先有数据库，所以第一步安装mysql数据库

1. 下载mysql数据库安装程序,可以点这里下载Mysql

     安装好后，在开始菜单框内输入cmd，在cmd.exe上右键以管理员身份运行。

    //开启mysql服务

    C:\Windows\system32>net start mysql
     MySQL 服务正在启动 ..............
     MySQL 服务已经启动成功。    

     //进入mysql数据库, 默认用户名root , 密码为空

    C:\Windows\system32>mysql -h localhost -u root -p
     Enter password:

    //切换到test数据库下

    mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.06 sec)


mysql> use test;
Database changed

//建立测试表 dhc_user，并插入数据，在这就不一贴过程，最终查询出来的样子是

mysql> select * from dhc_user;
+--------+------+-------+
| name   | age  | phone |
+--------+------+-------+
| w      |   12 | NULL  |
| h      |   13 | NULL  |
| wanghc |   11 |       |
| wanghc |   12 |       |
| wanghc |   13 |       |
+--------+------+-------+
5 rows in set (0.09 sec)

//写一get_age存储过程, 通过name查询age

mysql> DELIMITER //
mysql> CREATE PROCEDURE get_age(n VARCHAR(50))
    -> BEGIN
    -> SELECT age from dhc_user where name=n;
    -> END //
Query OK, 0 rows affected (0.07 sec)


mysql> DELIMITER ;

// 调用下get_age，能通过

mysql> call get_age("wanghc")
    -> ;
+------+
| age  |
+------+
|   11 |
|   12 |
|   13 |
+------+
3 rows in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

2. 下载Mysql数据的ODBC驱动,点这里下载mysql的ODBC驱动

      Caché数据库通过ODBC连接时就用到了驱动，

     安装好后，在开始菜单框内输入odbc,打开数据源管理界面,在系统DSN界面中添加Mysql的DSN

3. 进入Home>Configuration>Object/SQL Gateway Settings-SQL Gateway Connections,界面可以Create New Connection界面,

  

 点击Test Connection,测试成功

4. 进入Home>SQL界面，选中DHC-APP名字空间，点击Link Procedure Wizard

    

下一步,修改下包名与类型，生成一个web.test.mysql.DHCUser.cls类生成"get_age"方法

进入cahcé的terminal运行

 DHC-APP>d ##class(web.test.mysql.DHCUser)."get_age"("wanghc")

运行会报错，说mysql语法错误，看了下生成的"get_age"方法，生现call后面语句有问题，{call """".get_age(?)}改成{call test.get_age(?)},再运行

 DHC-APP>d ##class(web.test.mysql.DHCUser)."get_age"("wanghc")

没有报错，但也看不到返回值，进入Home>SQL 用call nullschema.get_age("wanghc")只说count是三条，看不到记录。用sqldbx工具运行call nullschema.get_age("wanghc")倒是可以看到三条记录与mysql内看到的一样。应该是caché自身的SQL没有从%sqlcontext内拿ResultSet显示出来,只是显示了SQLCODE与Message,ROWCOUNT。

5. 不用系统生成类方法，自己编写. 

    看了下生成的代码有个关键类%Library.SQLGatewayConnection，看了下他的api与例子,

    在web.test.mysql.DHCUser.cls内增加了个方法，没有用portal>sql gateway settings,用了dsn连mysql，再去运行 test.get_age()。

ClassMethod Call(name) As %Status
{
		set gc=##class(%SQLGatewayConnection).%New()
		If gc=$$$NULLOREF  quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
			  
		//Make connection to target DSN
		s pDSN="mysqltest"
		s usr="root"
		s pwd=""
		set sc=gc.Connect(pDSN,usr,pwd,0) 
		If $$$ISERR(sc) quit sc
		if gc.ConnectionHandle="" quit $$$ERROR($$$GeneralError,"Connection failed")
			  
		set sc=gc.AllocateStatement(.hstmt) 
		if $$$ISERR(sc) quit sc
		set pQuery= "{call test.get_age(?)}"
		set sc=gc.Prepare(hstmt,pQuery) 
		if $$$ISERR(sc) quit sc
		set sc = gc.BindParameter(hstmt,1,1,1,12,25,0,25)
		set sc = gc.SetParameter(hstmt,$lb(name),1)
		//Execute statement
		set sc=gc.Execute(hstmt)
		if $$$ISERR(sc) quit sc
		//Get list of columns returned by query
		set sc=gc.DescribeColumns(hstmt, .columnlist) 
		if $$$ISERR(sc) quit sc
		 
		//display column headers delimited by ":"
		set numcols=$listlength(columnlist)-1  //get number of columns
		for colnum=2:1:numcols+1 {
			    Write $listget($listget(columnlist,colnum),1),":"
		  		}
		write !
		 
		//Return first 200 rows	  
		set sc=gc.Fetch(hstmt)
		if $$$ISERR(sc) quit sc
		s rownum=1
		while((gc.sqlcode'=100) && (rownum<=200)) {
		      	for ii=1:1:numcols {
			      	s sc=gc.GetData(hstmt, ii, 1, .val)
			      	w " "_val
			      	if $$$ISERR(sc) break
		      	}
		      	s rownum=rownum+1
		 		write !
		 		set sc=gc.Fetch(hstmt)
				if $$$ISERR(sc) break
		  		}
		    
		  //Close cursor and then disconnect
		set sc=gc.CloseCursor(hstmt)
		if $$$ISERR(sc) quit sc
		  
		set sc=gc.Disconnect()
		  
		Quit sc
}

进入terminal运行下

DHC-APP> d ##class(web.test.mysql.DHCUser).Call("wanghc")
age:
 11
 12
 13

Call方法是用OOP写的，再参考了下生成的"get_age"方法写了另一MCall方法

ClassMethod MCall(name)
{
Set DLLName=$g(^%SYS("bindir"))_$s($$$isWINDOWS:"cgate.dll",$$$isUNIX:"cgate.so",$$$isVMS:"cgate.exe",1:"cgate.dll")
Set DLLHandle = $zf(-4,1,DLLName)
Set ConnectionHandle = $zf(-5,DLLHandle,45,"mysqltest","root","",15) 
Set hstmt=$zf(-5,DLLHandle,5,ConnectionHandle)
Set sqlcode=$zf(-5,DLLHandle,3,hstmt,"{call test.get_age(?)}") ; Prepare=3
Set sqlcode=$zf(-5,DLLHandle,62,hstmt,1,1,1,12,25,0,25)
 	Set sqlcode=$zf(-5,DLLHandle,9,hstmt,$lb(name),1)
 	Set sqlcode=$zf(-5,DLLHandle,4,hstmt) ; Execute=4
 	;Set %ROWCOUNT=$zf(-5,DLLHandle,31,hstmt)
 	;Write "%ROWCOUNT=",%ROWCOUNT,!
 	Set sqlcode=$zf(-5,DLLHandle,7,hstmt) ;Fetch=7
while (sqlcode'=100){
Set val = $zf(-5,DLLHandle,25,hstmt, 1, 1) ;GetData=25
 	Write val,!
 	Set sqlcode=$zf(-5,DLLHandle,7,hstmt) ;Fetch=7
}
}

在teminal内运行下

DHC-APP>d ##class(web.test.mysql.DHCUser).MCall("wanghc")
11
12
13

也能出来结果。

MCall方法大量用到了$zf。$zf是调用的active dll有兴趣的可以看下。

调用的是cach é数据库安装目录bin下的cgate.dll，通过这个dll再去调用dns。

 

类代码xml见附件，本地库是2010.2.8