类型不匹配MySQL

Question

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

当我得到的SQL语句，我得到一个错误

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

我用Google搜索，知道它的尝试错误的数据传递给数据类型。

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 | 
+----+-------------+------+-------+-------+---------------------+

设置变量

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 

获取硬盘空间，并存储在HDD

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

变化

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

到

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

应该是所有设置

Answer 2

的概率lem是你正试图创建一个基于插入查询的记录集。 Recordset的主要目标是显示来自数据库的数据，但是如果您执行插入查询，则不会显示任何内容。

你有2种选择：

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

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

然后使用select或表打开监视器表中的记录名称。

1. 使用select在监视器表上打开记录集对象，并使用记录集对象的AddNew method。

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

UPDATE：

正如@ Ekkehard.Horner指出：小数列的价值不应该由单引号包围。尽管MySQL会默默地将字符串转换为数字（除非启用严格的sql模式），但正确的是不要将数字作为字符串传递。