Re: Antw: COPY FROM

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.