/usr/share/doc/odbc-postgresql/docs/howto-vblo.html is in odbc-postgresql 1:09.00.0310-2.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 | <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<title>psqlODBC HOWTO - Visual Basic Large Objects</title>
</HEAD>
<body bgcolor="#ffffff" text="#000000" link="#ff0000" vlink="#a00000" alink="#0000ff">
<h1>psqlODBC HOWTO - Visual Basic Large Objects</h1>
<p>
<i>
Author: Denis Gasparin (denis@edistar.com)<br>
Release Date: 29 October 2001<br>
Description: Example based Mini-Howto on Pgsql Large Objects Interface and Visual Basic
</i>
<br><br>
This document tells about using Large Objects and Visual Basic. All the main connection
interfaces available in VB are discussed: DAO, ADO and RDO.
<br><br>
Requirements to get the subroutines to work:
<br>
<ul>
<li>DAO, ADO and RDO interfaces available in VB</li>
<li>Created the lo type using the appropriate functions available in contrib/lo in the PostgreSQL source tree</li>
<li>Installed and properly configured the latest version of the PostgreSQL ODBC driver.</li>
</ul>
In the example the database used has one table with only two fields. Here is the SQL definition:
<br>
<blockquote>
<code>
CREATE TABLE MYTABLE(<br>
MAIN INTEGER,<br>
OBJECT LO<bR>
);
</code>
</blockquote>
The ODBC DSN I used in the example is named pgsql_test_blob. To insert a record, I
suggest you to use the INSERT sql statement instead of the AddNew method (available
in DAO, RDO and ADO). The AddNew method force you to declare a Recordset and this is
bad because when you open it, VB creates a cursor and has to pass all the records in
the table, slowing your application significantly.
<br><br>
I think the examples are very simple and self explanatory. Some tips about which
interface to use:
<br>
<ul>
<li>ADO and RDO are the best interfaces to connect to PostgreSQL. Personally, I think ADO
is better only because it is new and actively supported by Microsoft. RDO is the
old interface and it is not more developed.</li>
<li>DAO is very, very heavy and I suggest you not use it unless you are forced to do so.</li>
</ul>
<h2>DAO (Data Access Objects)</h2>
<blockquote>
<pre>
Private Sub DAO_Connect()
Dim chunk() As Byte
Dim fd As Integer
Dim flen As Long
Dim ws As Workspace
Dim cn As Database
Dim rs As DAO.Recordset
Dim strConnection As String
' Initialize the DB Engine
Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=pgsql_test_blob;"
Set cn = ws.OpenDatabase("", False, False, strConnection)
' Open the table MYTABLE
Set rs = cn.OpenRecordset("MYTABLE")
'
' Add a new record to the table
'
rs.AddNew
rs!main = 100 '' a random integer value ''
fd = FreeFile
Open "mydocument" For Binary Access Read As fd
flen = LOF(fd)
If flen = 0 Then
Close
MsgBox "Error while opening the file"
End
End If
' Get the blob object into the chunk variable
ReDim chunk(1 to flen)
Get fd, , chunk()
' Store it in the database
rs!object.AppendChunk chunk()
' Update changes
rs.Update
' Close the file
Close fd
' Close the record set
rs.Close
'
' Read the blob object from the first record of MYTABLE
'
Set rs = Nothing
' Open the table
Set rs = cn.OpenRecordset("MYTABLE")
' Open a file for writing
fd = FreeFile
Open "mydocument" For Binary Access Write As fd
flen = rs!object.FieldSize
ReDim chunk(1 to flen)
' Get it from the database
chunk() = rs!object.GetChunk(0, flen)
' ...and put it into the file
Put fd, , chunk()
' Close all...
rs.Close
Close fd
cn.Close
Close
End Sub
</pre>
</blockquote>
<h2>ADO (ActiveX Data Objects)</h2>
<blockquote>
<pre>
Private Sub ADO_Store()
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim chunk() As Byte
Dim fd As Integer
Dim flen As Long
Dim main As ADODB.Parameter
Dim object As ADODB.Parameter
' Connect to the database using ODBC
With cn
.ConnectionString = "dsn=pgsql_test_blob;"
.Open
.CursorLocation = adUseClient
End With
' Here is an example if you want to issue a direct command to the database
'
'Set cmd = New ADODB.Command
'With cmd
' .CommandText = "delete from MYTABLE"
' .ActiveConnection = cn
' .Execute
'End With
'Set cmd = Nothing
'
' Here is an example of how insert directly into the database without using
' a recordset and the AddNew method
'
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "insert into MYTABLE(main,object) values(?,?)"
cmd.CommandType = adCmdText
' The main parameter
Set main = cmd.CreateParameter("main", adInteger, adParamInput)
main.Value = 100 '' a random integer value ''
cmd.Parameters.Append main
' Open the file for reading
fd = FreeFile
Open "mydocument" For Binary Access Read As fd
flen = LOF(fd)
If flen = 0 Then
Close
MsgBox "Error while opening the file"
End
End If
' The object parameter
'
' The fourth parameter indicates the memory to allocate to store the object
Set object = cmd.CreateParameter("object", _
adLongVarBinary, _
adParamInput, _
flen + 100)
ReDim chunk(1 to flen)
Get fd, , chunk()
' Insert the object into the parameter object
object.AppendChunk chunk()
cmd.Parameters.Append object
' Now execute the command
Set rs = cmd.Execute
' ... and close all
cn.Close
Close
End Sub
Private Sub ADO_Fetch()
'
' Fetch the first record present in MYTABLE with a lo object stored
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim fd As Integer
Dim flen As Long
Dim chunk() As Byte
' Connect to the database using ODBC
With cn
.ConnectionString = "dsn=pgsql_test_blob;"
.Open
.CursorLocation = adUseClient
End With
' Open a recordset of the table
Set rs = New ADODB.Recordset
rs.Open "MYTABLE", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' Get the len of the stored object
flen = rs!object.ActualSize
' Initialize the file where to store the blob
fd = FreeFile
Open "mydocument" For Binary Access Write As fd
ReDim chunk(1 to flen)
' Get it from the database
chunk() = rs!object.GetChunk(flen)
' ... and store in the file
Put fd, , chunk()
Close
End Sub
</pre>
</blockquote>
<h2>RDO (Remote Data Objects)</h2>
<blockquote>
<pre>
Private Sub RDO_Store()
Dim cn As New RDO.rdoConnection
Dim rs As RDO.rdoResultset
Dim cmd As RDO.rdoQuery
Dim fd As Integer
Dim flen As Long
Dim chunk() As Byte
' Connect to the database using ODBC
With cn
.Connect = "dsn=pgsql_test_blob;"
.LoginTimeout = 3
.CursorDriver = rdUseOdbc
.EstablishConnection rdDriverNoPrompt, True
End With
' Create the INSERT statement to store the record in the database
Set cmd = cn.CreateQuery("insert", _
"insert into MYTABLE (main,object) values(?,?)")
' Insert the first parameter
cmd.rdoParameters(0).Value = 100 '' a random integer value ''
' Open the file for reading
fd = FreeFile
Open "mydocument" For Binary Access Read As fd
flen = LOF(fd)
If flen = 0 Then
Close
MsgBox "errore in apertura file"
End
End If
ReDim chunk(1 To flen)
' Get it ...
Get fd, , chunk()
' and store into the parameter object
cmd.rdoParameters(1).Type = rdTypeLONGVARBINARY
cmd.rdoParameters(1).AppendChunk chunk()
' Finally execute the INSERT statement
cmd.Execute
' Close all
Close
End Sub
Private Sub RDO_Fetch()
'
' Fetch the first record present in MYTABLE with a lo object stored
Dim cn As New RDO.rdoConnection
Dim rs As RDO.rdoResultset
Dim fd As Integer
Dim flen As Long
Dim chunk() As Byte
' Connect to the database using ODBC
With cn
.Connect = "dsn=pgsql_test_blob;"
.LoginTimeout = 3
.CursorDriver = rdUseOdbc
.EstablishConnection rdDriverNoPrompt, True
End With
' Open the table
Set rs = cn.OpenResultset("select * from MYTABLE", rdOpenKeyset)
' Get the length of the file
flen = rs!object.ColumnSize
' Initialize the file where to store the object
fd = FreeFile
Open "mydocument" For Binary Access Write As fd
ReDim chunk(1 To flen)
' Get it from the database
chunk() = rs!object.GetChunk(flen)
Put fd, , chunk()
Close
End Sub
</pre>
</blockquote>
</p>
</body>
</html>
|