COPY FROM
am 08.02.2006 11:03:55 von Bart DegryseThis is a MIME message. If you are reading this text, you may want to
consider changing to a mail reader or gateway that understands how to
properly handle MIME multipart messages.
--=__Part1B39BC1B.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Dear,
I need to do a bulk upload (2,600,000 records) of data into a PostgreSQL =
(v8.0.3) table. I'm trying to achieve this from Visual Basic with ADO and =
psqlODBC (v8.1.2) but I can't get it working. Currently my code looks like =
this. Dim conn As New ADODB.Connection Dim query As String 'DSN =
less connection query =3D "DRIVER=3D{PostgreSQL Unicode};SERVER=3D10.100=
..1.24;PORT=3D2345;DATABASE=3Dbigdb;BoolsAsChar=3D0;TrueIsMi nus1=3D1;Debug=
=3D0;CommLog=3D0" conn.CursorLocation =3D adUseClient conn.Open =
query, "bad", "xxxxxxxx" query =3D "COPY dunn_main (duns, company, =
company_short, zip, phone, employee_number, legal_id, sic_id, source_id) " =
& _ "FROM STDIN WITH NULL AS 'NULL' DELIMITER AS ','" =
conn.Execute query, , adCmdText + adExecuteNoRecords + adAsyncExecute
In the driver logging I can see that it's waiting for the data now, but I =
can't really figure out how to deliver it. Since the source data (as a =
text file with fixed length fields) is only available on client side and =
needs some processing before being ready to import I'm using something =
like this to prepare the data:=20
Private Type Dunn_Record CO_NAME As String * 90 PCODE As =
String * 8 DUNS As String * 9 EMPS_COMP As String * 9 =
LE As String * 2 L As String * 1 TEL_NBR As String * 14 =
US72 As String * 4 crlf As String * 2 End Type Dim record =
As Dunn_Record Dim filehandle As Integer Dim filename As String =
Dim numLines as long Dim line As Long filehandle =3D FreeFile =
filename =3D "E:\source.txt" Open filename For Random Access Read Lock =
Read Write As #filehandle Len =3D Len(record) numLines =3D LOF(1) / =
Len(record) For line =3D 2 to numLines Get #filehandle, line, =
record With record query =3D query & CLng(.DUNS) & "," =
query =3D query & "'" & Replace(Trim(.CO_NAME), "'", "''") & "'," =
query =3D query & "'" & ascii_easy(.CO_NAME) & "'," =
query =3D query & "'" & Trim(.PCODE) & "'," query =3D query & =
phone(.TEL_NBR) & "," If Len(Trim(.EMPS_COMP)) Then query =3D =
query & CLng(.EMPS_COMP) Else query =3D query & "NULL" query =
=3D query & "," If Len(Trim(.LE)) Then query =3D query & =
CLng(.LE) Else query =3D query & "NULL" query =3D query & "," =
query =3D query & CLng(.US72) & "," query =3D query & =
rs!source_id End With 'DELIVER THE DATA IN query TO THE =
DRIVER Next lineI have tried several methods to deliver the prepared =
data to the driver but without any succes.=20
Writing to STDOUT
Private Declare Function GetStdHandle Lib "Kernel32" (ByVal nStdHandle =
As Long) As Long Private Declare Function WriteFile Lib "Kernel32" =
(ByVal hFile As Long, ByVal lpBuffer As String, ByVal nNumberOfBytesToWrite=
As Long, lpNumberOfBytesWritten As Long, lpOverlapped As Any) As Long =
Private Const STD_OUTPUT_HANDLE =3D -11& Dim stdhandle As Long Dim =
llResult As Long stdhandle =3D GetStdHandle(STD_OUTPUT_HANDLE) =
WriteFile stdhandle, query, Len(query), llResult, ByVal 0&Writing to a =
socket
Dim socket As New Winsock With socket .Protocol =3D =
sckUDPProtocol .RemoteHost =3D "10.100.1.24" .RemotePort =3D =
2345 .Connect End With socket.SendData queryExecuting it
conn.Execute queryWriting to some stream
Dim str As New Stream With str .Mode =3D adModeWrite =
..Open End With str.WriteText query
So basically my question is : how do I deliver the prepared data to the =
driver? Any help (tips, working code, example, ...) would be appreciated.=
=20
Best regards
--=__Part1B39BC1B.0__=
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML
>
Dear,
I need to do a bulk upload (2,600,000 records) of data into a =
PostgreSQL (v8.0.3) table. I'm trying to achieve this from Visual Basic =
with ADO and psqlODBC (v8.1.2) but I can't get it working. Currently my =
code looks like this.
Dim conn As New ADODB.Connection
Dim query As String
'DSN less connection
query =3D "DRIVER=3D{PostgreSQL Unicode};SERVER=3D10.100.1.24;PORT=3D23=
45;DATABASE=3Dbigdb;BoolsAsChar=3D0;TrueIsMinus1=3D1;Debug=3 D0;CommLog=3D0"=
conn.CursorLocation =3D adUseClient
conn.Open query, "bad", "xxxxxxxx"
query =3D "COPY dunn_main (duns, company, company_short, zip, phone, =
employee_number, legal_id, sic_id, source_id) " & _
"FROM STDIN WITH NULL AS 'NULL' DELIMITER AS ','"
conn.Execute query, , adCmdText + adExecuteNoRecords + adAsyncExecute
In the driver logging I can see that it's waiting for the data now, but =
I can't really figure out how to deliver it. Since the source data (as a =
text file with fixed length fields) is only available on client side and =
needs some processing before being ready to import I'm using something =
like this to prepare the data:=20
Private Type Dunn_RecordI have tried several methods to deliver the prepared data to the =
CO_NAME As String * 90
PCODE As String * 8
DUNS As String * 9
EMPS_COMP As String * 9
LE As String * 2
L As String * 1
TEL_NBR As String * 14
US72 As String * 4
crlf As String * 2
End Type
Dim record As Dunn_Record
Dim filehandle As Integer
Dim filename As String
Dim numLines as long
Dim line As Long
filehandle =3D FreeFile
filename =3D "E:\source.txt"
Open filename For Random Access Read Lock Read Write As #filehandle =
Len =3D Len(record)
numLines =3D LOF(1) / Len(record)
For line =3D 2 to numLines
Get #filehandle, line, record
With record
query =3D query & CLng(.DUNS) & ","
query =3D query & "'" & Replace(Trim(.CO_NAME), "'", =
"''") & "',"
query =3D query & "'" & ascii_easy(.CO_NAME) & =
"',"
query =3D query & "'" & Trim(.PCODE) & "',"
query =3D query & phone(.TEL_NBR) & ","
If Len(Trim(.EMPS_COMP)) Then query =3D query & CLng(.EMPS_=
COMP) Else query =3D query & "NULL"
query =3D query & ","
If Len(Trim(.LE)) Then query =3D query & CLng(.LE) Else =
query =3D query & "NULL"
query =3D query & ","
query =3D query & CLng(.US72) & ","
query =3D query & rs!source_id
End With
'DELIVER THE DATA IN query TO THE DRIVER
Next line
driver but without any succes.=20
- Writing to STDOUT
Private Declare Function GetStdHandle =
Lib "Kernel32" (ByVal nStdHandle As Long) As Long
Private Declare Function WriteFile Lib "Kernel32" (ByVal hFile As =
Long, ByVal lpBuffer As String, ByVal nNumberOfBytesToWrite As Long, =
lpNumberOfBytesWritten As Long, lpOverlapped As Any) As Long
Private Const STD_OUTPUT_HANDLE =3D -11&
Dim stdhandle As Long
Dim llResult As Long
stdhandle =3D GetStdHandle(STD_OUTPUT_HANDLE)
WriteFile stdhandle, query, Len(query), llResult, ByVal 0&
- Writing to a socket
Dim socket As New Winsock
With socket
.Protocol =3D sckUDPProtocol
.RemoteHost =3D "10.100.1.24"
.RemotePort =3D 2345
.Connect
End With
socket.SendData query
- Executing it
conn.Execute query
- Writing to some stream
Dim str As New Stream
With str
.Mode =3D adModeWrite
.Open
End With
str.WriteText query
So basically my question is : how do I deliver the prepared data to the =
driver? Any help (tips, working code, example, ...) would be appreciated.
p>=20
Best regards
--=__Part1B39BC1B.0__=--