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 = "DRIVER={PostgreSQL Unicode};SERVER=10.100.1.24;PORT=2345;DATABASE=bigdb;BoolsAsChar=0;TrueIsMinus1=1;Debug=0;CommLog=0" conn.CursorLocation = adUseClient conn.Open query, "bad", "xxxxxxxx" query = "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:
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 = FreeFile filename = "E:\source.txt" Open filename For Random Access Read Lock Read Write As #filehandle Len = Len(record) numLines = LOF(1) / Len(record) For line = 2 to numLines Get #filehandle, line, record With record query = query & CLng(.DUNS) & "," query = query & "'" & Replace(Trim(.CO_NAME), "'", "''") & "'," query = query & "'" & ascii_easy(.CO_NAME) & "'," query = query & "'" & Trim(.PCODE) & "'," query = query & phone(.TEL_NBR) & "," If Len(Trim(.EMPS_COMP)) Then query = query & CLng(.EMPS_COMP) Else query = query & "NULL" query = query & "," If Len(Trim(.LE)) Then query = query & CLng(.LE) Else query = query & "NULL" query = query & "," query = query & CLng(.US72) & "," query = 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.
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 = -11& Dim stdhandle As Long Dim llResult As Long stdhandle = GetStdHandle(STD_OUTPUT_HANDLE) WriteFile stdhandle, query, Len(query), llResult, ByVal 0&Writing to a socket
Dim socket As New Winsock With socket .Protocol = sckUDPProtocol .RemoteHost = "10.100.1.24" .RemotePort = 2345 .Connect End With socket.SendData queryExecuting it
conn.Execute queryWriting to some stream
Dim str As New Stream With str .Mode = 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.
Best regards