IFIX 和SQL通讯的相关问题

一：IFIX系统运行环境要求：
1：安装VB6.0程序。

2：安装OFFICE 2003.
3：安装SQL2005.
4：安装F1BOOK ONE 报表控件。

二：在IFIX中：
1：在IFIX中添加时间控件（），F1BOOK1 表格控件。

2：在VB脚本中添加时间控件，
郑工介绍：：
（
1、FIX3.0的控件引用
FIX3.0画面中，添加VB的DT Picker控件和F1 book控件，再画面使用该控件后，需要在VBA编辑脚本环境中，添加引用才可使用。

安装VB后，在FIX3.0VBA编辑脚本环境中选择：Microsoft Windows Common Controls-2 6.0
安装F1 book软件，经破解后，OLE插入，自动引用Tidestone Formula One 6.0 Library。

还要引用Microsoft activeX data objects 2.8 library 和Microsoft activeX data objects recordset 2.8 library .(这两个空间是建立ODBC的链接用。

) 其余引用如下图：
2、IFIX下F1 book控件调用出现倒计时（或出错）
F1 book控件调用出现倒计时，主要为软件安装后，破解文件未替换，而产生的间断性出错：比如第一次打开含有F1 book控件的画面，或长时间未进行画面切换而调用到含有F1 book控件的画面时，产生的VBA错误和出现控件10S倒计时。

解决如下：
1、执行f1pro6.0.2.5文件夹下的setup.exe
其中序列号：TTI.FPA5.06.00.0020875315
默认安装即可
2、把china_f1文件夹下的ttf16.ocx 覆盖原文件(原有文件默认是在C:\TIDESTONE\FORMONE6)
TTFI6.ocx, TTFIWZ6.dll 覆盖原文件(原有文件默认在C:\TIDESTONE\FIRSTIMP6)
setttfi6.reg 直接用Mouse双击导入到注册表中
3、最后把文件夹formula_ttf1_china下的ttf1.exe直接覆盖原来的c:\TIDESTONE\FORMONE6\目录下的同名文件即可
下使用F1BOOK控件办法：
插入OLE对象->插入对象----对象类型下找TideStone formula one 6.0 WorkBook插入到窗体中即可。

3：在vb中建立调度，将变量的数据按照设定的间隔时间写进SQL的数据库中，具体代码如下：
Private Sub FixTimer_WriteMin_OnTimeOut(ByVal lTimerId As Long)
On Error Resume Next
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim connStr As String
Dim rsStr As String
connStr = "Provider=SQLOLEDB;Data Source=CE2E345B26164E2\SQLEXPRESS;database=FD_Data;user id=sa;password=123;" conn.ConnectionString = connStr
conn.Open
rsStr = "select * from HisData"
rs.Open rsStr, conn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs.Fields(1) = Format(Now, "yyyy-mm-dd hh:mm:ss")
rs.Fields(2) = Format(Fix32.THISNODE.TP.F_CV, "#####0.000")
rs.Fields(3) = Format(Fix32.THISNODE.TN.F_CV, "#####0.000")
rs.Fields(4) = Format(Fix32.THISNODE.PERMANGANATE_INDEX.F_CV, "#####0.000") rs.Fields(5) = Format(Fix32.THISNODE.NH3.F_CV, "#####0.000")
rs.Fields(6) = Format(Fix32.THISNODE.CR_SIX.F_CV, "#####0.000")
rs.Fields(7) = Format(Fix32.THISNODE.TOTAL_MANGANESE.F_CV, "#####0.000")
rs.Fields(8) = Format(Fix32.THISNODE.TOTAL_NICKEL.F_CV, "#####0.000")
rs.Fields(9) = Format(Fix32.THISNODE.FLUORIDE.F_CV, "#####0.000")
rs.Fields(10) = Format(Fix32.THISNODE.POLLUTIONINDEX.F_CV, "#####0.000")
rs.Fields(11) = Format(Fix32.THISNODE.WATERQUALITY.F_CV, "#####0.000")
rs.Fields(12) = Format(Fix32.THISNODE.VOLTAGE.F_CV, "#####0.000")
rs.Fields(13) = Format(Fix32.THISNODE.PH.F_CV, "#####0.000")
rs.Fields(14) = Format(Fix32.THISNODE.ECONDUCTIVITY.F_CV, "#####0.000")
rs.Fields(15) = Format(Fix32.THISNODE.TURBIDITY.F_CV, "#####0.000")
rs.Fields(16) = Format(Fix32.THISNODE.OXYGEN.F_CV, "#####0.000")
rs.Fields(17) = Format(Fix32.THISNODE.W ATERTEMP.F_CV, "#####0.000")
rs.Fields(18) = Format(Fix32.THISNODE.TEMP.F_CV, "#####0.000")
rs.Fields(19) = Format(Fix32.THISNODE.HUMIDITY.F_CV, "#####0.000")
rs.Fields(20) = Format(Fix32.THISNODE.PRESSURE.F_CV, "#####0.000")
rs.UpdateBatch adAffectCurrent
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub
4：：：：在SQL 的数据中建立新的数据库。

（数据库要和系统数据库平级。

不要作为子数据库。

）
在数据库中建立表格，表格的顺序要和IFIX报表中的数据顺序一直。

5：运行调度，激活调度，看数据是否写入到数据库的表格中，如果成功测说明调度根据设定的间隔写入数据到数据库中。

6：在IFIX画面中，建立查询按钮。

写代码，读取数据库。

把数据库中读取的数值写入到报表中。

具体代码如下：：：
日报：
Private Sub cmd_day_Click()
''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim connstr As String
Dim rsstr As String
Dim startdate As String
Dim cmpdate1 As Date
Dim cmpdate2 As Date
Dim cmpdate3 As Date
F1Book2.Sheet = 1 '日报
F1Book2.ClearRange 3, 1, 26, 24, F1ClearValues
'F1Book2.ClearRange 27, 2, 27, 6, F1ClearValues '清空
startdate = Format(DTPicker1.Value, "yyyy-mm-dd 00:00:00")
cmpdate3 = DateAdd("d", 1, startdate)
connstr = "Provider=SQLOLEDB;data source=(local);initial catalog=master;user id=sa;password=123;"
conn.ConnectionString = connstr
conn.Open
Dim i As Integer
Dim j As Integer
i = 0
For j = 1 To 24
cmpdate1 = DateAdd("s", 3, startdate)
cmpdate2 = DateAdd("s", -3, startdate)
rsstr = "select avg(LQ_PH_XS_D),avg(LQ_ZD_XS_D),avg(LQ_YL_XS_D),avg(JS_FLOW_X),avg(SCD_XS), avg(AI_CCS_PH),avg(AI_CCS_ZHUODU),avg(AI_CCS_YULV),avg(CS_FLOW_X),avg(QSC_ YW),avg(SSPUMP_2_P),avg(CS_FLOW_LJ) from SQLTest where [DateTime] between '" & cmpdate1 & "' and '" & cmpdate2 & "'"
rs.Open rsstr, conn, 1, 1
F1Book2.TextRC(i + 3, 1) = startdate
If rs.RecordCount <= 0 Then GoTo end2
If Not IsNull(rs.Fields(0)) Then F1Book2.NumberRC(i + 3, 2) = rs.Fields(0)
If Not IsNull(rs.Fields(1)) Then F1Book2.NumberRC(i + 3, 3) = rs.Fields(1)
If Not IsNull(rs.Fields(2)) Then F1Book2.NumberRC(i + 3, 4) = rs.Fields(2)
If Not IsNull(rs.Fields(3)) Then F1Book2.NumberRC(i + 3, 5) = rs.Fields(3)
If Not IsNull(rs.Fields(4)) Then F1Book2.NumberRC(i + 3, 6) = rs.Fields(4)
If Not IsNull(rs.Fields(5)) Then F1Book2.NumberRC(i + 3, 7) = rs.Fields(5)
If Not IsNull(rs.Fields(6)) Then F1Book2.NumberRC(i + 3, 8) = rs.Fields(6)
If Not IsNull(rs.Fields(7)) Then F1Book2.NumberRC(i + 3, 9) = rs.Fields(7)
If Not IsNull(rs.Fields(8)) Then F1Book2.NumberRC(i + 3, 10) = rs.Fields(8) If Not IsNull(rs.Fields(9)) Then F1Book2.NumberRC(i + 3, 11) = rs.Fields(9) If Not IsNull(rs.Fields(10)) Then F1Book2.NumberRC(i + 3, 12) = rs.Fields(10) If Not IsNull(rs.Fields(11)) Then F1Book2.NumberRC(i + 3, 13) = rs.Fields(11) ' If Not IsNull(rs.Fields(12)) Then F1Book2.NumberRC(i + 3, 14) = rs.Fields(12) end2:
i = i + 1
startdate = DateAdd("h", 1, startdate)
startdate = Format(startdate, "yyyy-mm-dd hh:mm:ss")
rs.Close
Set rs = Nothing
Next j
conn.Close
Set conn = Nothing
errorhandler:
''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
End Sub
）
月报：：：：：
Private Sub cmd_month1_Click()
'On Error GoTo errorhandler
On Error Resume Next
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim connstr As String
Dim rsstr As String
Dim startdate As String
Dim cmpdate1 As Date
Dim cmpdate2 As Date
Dim cmpdate3 As Date
Dim days_month As Integer
Dim days_date As Date
Dim a1 As Double
Dim b1 As Double
Dim c1 As Double
Dim d1 As Double
Dim e1 As Double
Dim a5, a6, a7 As Variant
F1Book2.Sheet = 2 '月报
F1Book2.ShowGridLines = False
F1Book2.ClearRange 3, 1, 33, 24, F1ClearValues '清空
startdate = Format(DTPicker1.Value, "yyyy-mm-01 00:00:00")
days_date = DateAdd("m", 1, startdate)
days_month = DateDiff("d", startdate, days_date)
cmpdate3 = DateAdd("d", 1, startdate)
cmpdate1 = DateAdd("s", -3, startdate)
cmpdate2 = DateAdd("s", 3, cmpdate3)
connstr = "Provider=SQLOLEDB;data source=(local);initial catalog=master;user id=sa;password=123;"
conn.ConnectionString = connstr
conn.Open
Dim i As Integer
Dim j As Integer
i = 0
For j = 1 To days_month
cmpdate3 = DateAdd("d", 1, startdate)
cmpdate1 = DateAdd("s", -3, startdate)
cmpdate2 = DateAdd("s", 3, cmpdate3)
rsstr = "select avg(LQ_PH_XS_D),avg(LQ_ZD_XS_D),avg(LQ_YL_XS_D),avg(JS_FLOW_X),avg(SCD_XS), avg(AI_CCS_PH),avg(AI_CCS_ZHUODU),avg(AI_CCS_YULV),avg(CS_FLOW_X),avg(QSC_ YW),avg(SSPUMP_2_P),max(CS_FLOW_LJ),min(CS_FLOW_LJ) from SQLTest where (DateTime) between '" & cmpdate1 & "' and '" & cmpdate2 & "'"
rs.Open rsstr, conn, 1, 1
F1Book2.TextRC(i + 3, 1) = Format(startdate, "yyyy-mm-dd")
If rs.RecordCount > 0 Then
a5 = rs.Fields(11)
a6 = rs.Fields(12)
a7 = a5 - a6
If Not IsNull(rs.Fields(0)) Then F1Book2.NumberRC(i + 3, 2) = rs.Fields(0) 'rs("粗格栅液位")
If Not IsNull(rs.Fields(1)) Then F1Book2.NumberRC(i + 3, 3) = rs.Fields(1) ' rs("进水流量")
If Not IsNull(rs.Fields(2)) Then F1Book2.NumberRC(i + 3, 4) = rs.Fields(2) 'rs("氧化沟溶氧")
If Not IsNull(rs.Fields(3)) Then F1Book2.NumberRC(i + 3, 5) = rs.Fields(3) 'rs("氧化沟MLSS浓度")
If Not IsNull(rs.Fields(4)) Then F1Book2.NumberRC(i + 3, 6) = rs.Fields(4) 'rs("鼓风机1电流")
If Not IsNull(rs.Fields(5)) Then F1Book2.NumberRC(i + 3, 7) = rs.Fields(5) 'rs("鼓风机2电流")
If Not IsNull(rs.Fields(6)) Then F1Book2.NumberRC(i + 3, 8) = rs.Fields(6) 'rs("鼓风机3电流")
If Not IsNull(rs.Fields(7)) Then F1Book2.NumberRC(i + 3, 9) = rs.Fields(7) 'rs("PLC1备用")
If Not IsNull(rs.Fields(8)) Then F1Book2.NumberRC(i + 3, 10) = rs.Fields(8) 'rs("巴氏计量槽总磷)")
If Not IsNull(rs.Fields(9)) Then F1Book2.NumberRC(i + 3, 11) = rs.Fields(9) 'rs("巴
氏计量槽氨氮")
If Not IsNull(rs.Fields(10)) Then F1Book2.NumberRC(i + 3, 12) = rs.Fields(10) ' rs("巴氏计量槽COD")
If Not IsNull(a7) Then F1Book2.NumberRC(i + 3, 13) = a7 ' rs("巴氏计量槽流量")
' If Not IsNull(rs.Fields(12)) Then F1Book2.NumberRC(i + 3, 14) = rs.Fields(12) 'rs("浓缩池流量")
' If Not IsNull(rs.Fields(13)) Then F1Book2.NumberRC(i + 3, 15) = rs.Fields(13) 'rs("浓缩池液位")
' If Not IsNull(rs.Fields(14)) Then F1Book2.NumberRC(i + 3, 16) = rs.Fields(14) 'rs("加氯加药间流量")
'If Not IsNull(rs.Fields(15)) Then F1Book2.NumberRC(i + 3, 17) = rs.Fields(15) 'rs("污泥泵井液位")
rs.Close
Set rs = Nothing
End If
end2:
i = i + 1
startdate = DateAdd("d", 1, startdate)
Next j
conn.Close
Set conn = Nothing
errorhandler:
End Sub。