mysql - 类型不匹配 MySQL

Question

我有一个 VBScript，它占用一些系统资源并将它们存储在一个变量中，我想将它们写入 MySQL 表。

当我执行 SQL 语句时出现错误

Type mismatch: '[string: "insert into monitor "]'

我用谷歌搜索并知道它试图将错误的数据传递给数据类型。

MySQL 表

CREATE TABLE monitor (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pcname VARCHAR(30),
  cpu decimal(4,2),
  hdd decimal(4,2),
  mem decimal(4,2),
  rdate TIMESTAMP
);

示例输出:

+----+-------------+------+-------+-------+---------------------+| id | pcname      | cpu  | hdd   | mem   | rdate               |+----+-------------+------+-------+-------+---------------------+|  1 | HOSTNAME-PC | 4.00 | 39.26 | 74.28 | 2016-04-21 12:16:04 |+----+-------------+------+-------+-------+---------------------+

Set variables

Dim Connection
Dim ConnectionString
Dim Recordset
Dim SQL
dim cpu 
dim hdd 
dim mem
dim host

获取主机名并存储在主机中:

Set wshShell = CreateObject( "WScript.Shell" )
strRegValue = "HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\Hostname"
strHostName = wshShell.RegRead( strRegValue )
host = strHostName

获取cpu并存入cpu:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor WHERE Name = '_Total'")
For Each objItem In colItems
    cpu = objItem.PercentProcessorTime
Next

获取硬盘空间并存入硬盘

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_LogicalDisk Where Name='C:'",,48)
For Each objItem In colItems
    intFreeSpace = objItem.FreeSpace
    intTotalSpace = objItem.Size
    pctFreeSpace = Round((intFreeSpace/intTotalSpace)*100, 2)
    hdd = pctFreeSpace
Next

获取内存空间并存入mem:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
GB = 1024 *1024 * 1024
For Each objItem In colItems
    intTotal = Round(objItem.TotalPhysicalMemory / GB, 3)
Next
Set colItems1 = objWMIService.ExecQuery("Select * from Win32_PerfFormattedData_PerfOS_Memory",,48)
For Each objItem1 In colItems1
    intAvail =  Round(objItem1.AvailableBytes / GB, 3)
Next
pctFree = Round((intAvail/intTotal)*100, 2)
mem = pctFree

SQL语句出错:

"Type mismatch: '[string: "insert into monitor "]'"

SQL = "insert into monitor (pcname,cpu,hdd,mem) values ('"+host+"','"+cpu+"','"+hdd+"','"+mem+"')"

其余代码:

ConnString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=servername; DATABASE=dbname; " &_
    "UID=username;PASSWORD=password; OPTION=3"

Set Connection = CreateObject("ADODB.Connection")
Set Recordset = CreateObject("ADODB.Recordset")

Connection.Open ConnString

Recordset.Open SQL,Connection

If Recordset.EOF Then
    Wscript.Echo("No records returned.")
Else
    Do While NOT Recordset.Eof   
      WScript.Echo Recordset("pcname")
      WScript.Echo Recordset("cpu")
      WScript.Echo Recordset("hdd")
      WScript.Echo Recordset("mem")
      WScript.Echo "<br>"   
      Recordset.MoveNext    
    Loop
End If

Recordset.Close
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

Answer 1

问题是您正在尝试基于插入查询创建记录集。 Recordset 的主要目标是显示 数据库中的数据，但如果您执行插入查询，则不会显示任何内容。

你有两个选择:

1. 使用连接对象的 Execute method 执行插入查询:

   Connection.Execute sql, , 128 --128=0x80=adExecuteNoRecords
   

然后使用选择或表名在监控表上打开记录集。

1. 使用选择打开监控表上的记录集对象并使用记录集对象的 AddNew method .

   Recordset.Open "SELECT * FROM monitor", Connection, 1
   Recordset.AddNew
   Recordset.Fields("pcname").Value = xxx
   ...
   Recordset.Update
   

更新:

正如@Ekkehard.Horner 指出的那样:十进制列的值不应用单引号引起来。尽管 MySQL 会默默地将字符串转换为数字(除非启用了严格的 sql 模式)，但正确的做法是不要将数字作为字符串传递。