Re: COPY FROM - Mailing list pgsql-odbc

----- Original Message -----

From: Bart Degryse

To: pgsql-odbc@postgresql.org

Cc: Bart Degryse

Sent: Wednesday, February 08, 2006 11:03 AM

Subject: [ODBC] COPY FROM

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 line

I 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 query
  

• Executing it
  

      conn.Execute query
  

• Writing 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