BCP within Delphi

Question

Post reply

BCP within Delphi

Gabor Nyul

SSCrazy Eights

Points: 9459
More actions
August 8, 2003 at 6:17 am

#84108

Hi forum,
How can I use the BCP functions (bcp_init, bcp_batch...) within Delphi usind ADO?
Or how to load large amount of data fast into a table using Delphi.
Any idea?
Thanks
Gabor

Bye
Gabor

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Gabor,

quote:
How can I use the BCP functions (bcp_init, bcp_batch...) within Delphi usind ADO?

in BOL there are examples for use with C. Maybe you can apply this to Delphi?

Although I don't know Delphi, there must be something equivalent to the Shell command, so that you can execute command line stuff.

Also you can call the Win32 API function ShellExecute(Ex, for Unicode)

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

Frank,

Unfortunatly this is not the point.

Of cours I can call system command out of Delphi.

But what I have to do is programatically send the data to SQLServer (i.e via bcp_sendrow) as I've done in C programmming language.

The main problem why your solution is not working is the fact that on my application server I do not have SQL client installed (i.e. no BCP.exe) just only MDAC (ie.e ODBC and OLDB).

Of cours I can generate separated inserts but it does not make sense to do for several thousends of rows. It's just too slow.

I cannot use bulk insert neither because I am accessing the SQLServer over SOCKS server and that one does not allow file mapping.

I could do this task in C but I just simply don't want to use several programing languages for the same project.

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Gabor,

quote:
Unfortunatly this is not the point.
Of cours I can call system command out of Delphi.
But what I have to do is programatically send the data to SQLServer (i.e via bcp_sendrow) as I've done in C programmming language.
The main problem why your solution is not working is the fact that on my application server I do not have SQL client installed (i.e. no BCP.exe) just only MDAC (ie.e ODBC and OLDB).

aah, things becoming clearer now!

Are you trying to batch input data into SAP ?

quote:
I could do this task in C but I just simply don't want to use several programing languages for the same project.

kick Delphi out !

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 4

I would.

But as of today there is no other tool where I can program quickly, produce a single .exe file, and having that .exe running fast on every 32 bit Windows system (Win9x. NT, W2K, XP)

Try to do that in VB, C#...

VB is coming with all the runtime DLL, ActiveX staff, C# and VB.NET with all the .NET Framework.

C, C++ could be a solution but you need "years" to the simpliest application.

What I need is a single executable with eventually some of my application DLLs also being able to run in console mode. That's all.

Any suggestions?

Gabor

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
I would.
But as of today there is no other tool where I can program quickly, produce a single .exe file, and having that .exe running fast on every 32 bit Windows system (Win9x. NT, W2K, XP)
Try to do that in VB, C#...
VB is coming with all the runtime DLL, ActiveX staff, C# and VB.NET with all the .NET Framework.
C, C++ could be a solution but you need "years" to the simpliest application.

I know what you mean. And if you haven't got the time to learn it properly, you may damage a lot of things.

quote:
What I need is a single executable with eventually some of my application DLLs also being able to run in console mode. That's all.

does this file need to reside on the clients or could it be an alternative to place it on the server and allow access and execution?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 6

It could be anywhere.

The only problem I actually have is that the file server where the input files are located are not in the same network segment as the DB server.

From the security point of view only the SQLServer traffic is allowed, no Netbios (therefore no file mapping or UNC file access).

On the fileserver I have all the ODBC and OLEDB staff but no SQLServer client tools (that one is a licencing issue).

I have already written hundreds of low level DB staff in C, C++ (using dblib and ODBC) but not in Delphi.

Bye
Gabor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

quote:
I have already written hundreds of low level DB staff in C, C++ (using dblib and ODBC) but not in Delphi.

and you can't think of using C this time.

Well, I think you should use ADO in Delphi. Doesn't speed up things, though, I guess.

Are you doing this at regular intervals with how many rows?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 8

I think I will use C for this time. I just hate mixing up development languages.

The app. will run each morning in the login script each file has between 20 and 500 rows and there are 3.000-4.000 files each morning.

The goal is to insert as quick as possible to insure the concurrency

Bye
Gabor