|
Re: updating records into RDBMS from a text file
by Abigail (Deacon) on Jul 12, 2001 at 16:13 UTC
|
Well, what the fastest way is depends on the format of your
text file, the structure of your tables, the number records
already in the table, the number of records in the file,
the number of indices on the table, the number of expected
locks during your actions, and of course, the RDBMS itself.
Some databases can read straight from a text file, if it has
the appropriate format. Other can't, or can't with your
format.
There are too many unknown factors to answer your question.
But even if you gave me the factors, I would hesitate to
answer; it sounds too much like you're trying to get people
do your work. What have you done yourself so far?
-- Abigail | [reply] |
|
|
Sorry for being too cryptic!
Till now what I have done is:
I am reading the delimited text file and am taking each line into an array
I have constructed the SQL statement (Update table..) and again put it into an array
as the Update statements will have to be executed one at a time right?
What I would like help on is the interaction with the RDBMS like
opening database connection and then actually executing the Update commands that I have constructed put into the array
I can't use the RDBMS utilities to read the text file but I want the external utility (that I am building) to do it.
Thanks once again!
Joe
| [reply] |
|
|
Well, it looks like you have no experience with connecting
to a database from without Perl. Luckely, there are
various modules that do a lot of work for you. You should
check out the DBI module, and a DBD
driver for the RDBMS you work with. DBI is a
generic interface, giving the same interface to many different
database servers. There are also packages for specific
databases, which will give you more features - but you lose
in portability. Take your pick ;-)
-- Abigail
| [reply]
[d/l]
[select] |
Re: updating records into RDBMS from a text file
by bwana147 (Pilgrim) on Jul 12, 2001 at 15:50 UTC
|
Well, given the little input, here are a few pointers:
- DBI: for DB connection
- open: to open your text file
- the obligatory perlre and split to help you parse your text file
You may also try here.
--bwana147
| [reply] |
|
|
Thanks bwana147, for your reply!
Joe
| [reply] |
Re: updating records into RDBMS from a text file
by Hofmator (Curate) on Jul 12, 2001 at 15:40 UTC
|
Show us what you have so far - we are not here to hack solutions for you.
And what do you mean by fast - I hope this is refering to program execution speed and not targeted at us ;)
-- Hofmator
| [reply] |
|
|
Thanks Hofmator, for your input!
Joe
| [reply] |
Re: updating records into RDBMS from a text file
by barndoor (Pilgrim) on Jul 12, 2001 at 16:47 UTC
|
Many relational databases have very efficient built-in utilities for uploading data from files into tables. (Sybase has the bcp utility, MySQL has the LOAD DATA statement). These are 'usually' very efficient and can be set up to handle different data delimiters, formats, etc.
For most cases it's worth using these tools rather than doing extra work to write your own import utilities. | [reply] |
Re: updating records into RDBMS from a text file
by agoth (Chaplain) on Jul 12, 2001 at 15:53 UTC
|
Broadly:
- open the file
- open database connection
- Iterate over file
- foreach line, extract the data you want
- insert / update into database
Modules you might want to use:
DBI
You will more likely be better off doing more research on perl.com or searching this site for DBI / Database, then coming back with specific problems
| [reply] |
|
|
Thanks Agoth, for your input!
Joe
| [reply] |