Re: Antw: COPY FROM - Mailing list 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.Holschen@hela-food.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.Degryse@indicator.be> schrieb am Mittwoch, 8.
Februar 2006
um 11:03 in Nachricht <s3e9d02f.034@webaccess.indicator.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.