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

Re: Antw: COPY FROM

From: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
To: <Thomas(dot)Holschen(at)hela-food(dot)de>,<pgsql-odbc(at)postgresql(dot)org>
Cc: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
Subject: Re: Antw: COPY FROM
Date: 2006-02-08 16:34:40
Message-ID: s3ea2bc5.055@webaccess.indicator.be (view raw or flat)
Thread:
Lists: pgsql-odbc
Hi Thomas,
That's the way I have it working already. With 2,700,000 records it takes quite a lot of time though. According to the PostgreSQL manual bulk uploads perform better with the COPY FROM statement. If only I could get it to work...
Thanks anyway.

>>> "Thomas Holschen" <Thomas(dot)Holschen(at)hela-food(dot)de> 2006-02-08 17:30 >>>

Hi Bart,

Just create an ODBC Entry on your local computer,
add a Refernce to ActiveX Dataobjects 2.8 oder 2.7 to your vbp
Projekt,
open an ADODB.connection to the Server and send SQL-Insert Statements.

Little Example
Private Function Insert()
    Dim DBS As New ADODB.Connection
    Dim SQLString As String
    DBS.Open "Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties=DSN=YourODBCDatabaseName;"
    
    SQLString = "Insert into SomeTable (Field1,Field2,Field3) Values
('111','aaa','bbb')"
    DBS.Execute SQLString
End Function

Hope that helps... Your Mail is a little bit "unreadable" ;-)

regards, Thomas.





-- 

_____________________________________________
Hela Gewürzwerk Hermann Laue GmbH & Co.KG
EDV
Thomas Holschen 
Beimoorweg 11
22926 Ahrensburg

Tel. : +49 4102/496-381
http://www.hela-food.de 
_____________________________________________

>>> "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be> schrieb am Mittwoch, 8.
Februar 2006
um 11:03 in Nachricht <s3e9d02f(dot)034(at)webaccess(dot)indicator(dot)be>:

> 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;TrueIsMin
> us1=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 St
ring, 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
Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen. Wenn 
Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, 
informieren Sie bitte den Absender und löschen Sie diese E-Mail. Das unerlaubte 
Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet. Aus 
Rechts- und Sicherheitsgründen ist die in dieser E-Mail gegebene Information nicht 
rechtsverbindlich.

This e-mail contains confidential and/or privileged information. If you are not the 
intended addressee or have received this e-mail in error please notify the sender and 
delete this e-mail. Any unauthorized copying, disclosure or distribution of the material 
in this e-mail is strictly forbidden. Due to legal and security reasons the information 
contained in this e-mail is not legally binding.


pgsql-odbc by date

Next:From: Tim ClarkeDate: 2006-02-08 16:34:49
Subject: LATIN1/9 conversion....
Previous:From: Thomas HolschenDate: 2006-02-08 16:30:53
Subject: Antw: COPY FROM

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