Skip site navigation (1) Skip section navigation (2)

COPY FROM

From: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
To: <pgsql-odbc(at)postgresql(dot)org>
Cc: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
Subject: COPY FROM
Date: 2006-02-08 10:03:55
Message-ID: s3e9d02f.034@webaccess.indicator.be (view raw or flat)
Thread:
Lists: pgsql-odbc
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

Responses

pgsql-odbc by date

Next:From: Sistemas C.M.P.Date: 2006-02-08 14:03:02
Subject: Obtain Serial Value
Previous:From: Joshua D. DrakeDate: 2006-02-06 22:36:28
Subject: Re: ODBCng looking for testers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group