psqlODBC 使用指南 - Visual Basic

作者：Dave Page ([email protected])
发布日期：2001 年 11 月 13 日
描述：从 Visual Basic 访问 PostgreSQL 的示例迷你指南

本文档提供了一些示例代码，帮助您开始使用 Visual Basic 和 PostgreSQL。

使子例程正常工作所需的条件

• Visual Basic 5/6
• VB 项目中对 Microsoft ActiveX 数据对象的引用
• PostgreSQL 数据源。

CREATE TABLE vbtest(
   id int4,
   data text,
   accessed timestamp
);

代码

Sub Main()
Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
 
  'Open the connection
  cn.Open "DSN=<MyDataSourceName>;" & _
          "UID=<MyUsername>;" & _
          "PWD=<MyPassword>;" & _
          "Database=<MyDatabaseName>"
 
  'Clear the table
  cn.Execute "DELETE FROM vbtest;"
  
  'For updateable recordsets we would typically open a Dynamic recordset.
  'Forward Only recordsets are much faster but can only scroll forward and 
  'are read only. Snapshot recordsets are read only, but scroll in both 
  'directions. 
  rs.Open "SELECT id, data, accessed FROM vbtest", cn, adOpenDynamic, adLockOptimistic
 
  'Loop though the recordset and print the results
  'We will also update the accessed column, but this time access it through 
  'the Fields collection. ISO-8601 formatted dates/times are the safest IMHO.
  While Not rs.EOF
    Debug.Print rs!id & ": " & rs!data
    rs.Fields("accessed") = Format(Now, "yyyy-MM-dd hh:mm:ss")
    rs.Update
    rs.MoveNext
  Wend
 
  'Add a new record to the recordset
  rs.AddNew
  rs!id = 76
  rs!data = 'More random data'
  rs!accessed = Format(Now, "yyyy-MM-dd hh:mm:ss")
  rs.Update

  'Insert a new record into the table
  cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random data');"

  'Refresh the recordset to get that last record...
  rs.Requery

  'Get the record count
  rs.MoveLast
  rs.MoveFirst
  MsgBox rs.RecordCount & " Records are in the recordset!"

  'Cleanup
  If rs.State <> adStateClosed Then rs.Close
  Set rs = Nothing
  If cn.State <> adStateClosed Then cn.Close
  Set cn = Nothing
End Sub

有用函数

' The escapeString function can be used to fix strings for us in INSERT and 
' UPDATE SQL statements. It will take a value, and return it ready for 
' use in your statement as NULL, or quoted with backslashes and single quotes
' escaped.

Function escapeString(vValue As Variant) As String

  If IsNull(vValue) Then
    escapeString = "NULL"
  else
    escapeString = "'" & Replace(Replace(vValue, "", ""), "'", "''") & "'" 
  end if

End Function