Hi,
In HADB we had a local IMPORT command that formatted a standard SQL
INSERT statement based on the table layout and local command options,
had that statement prepared by the server, and then formatted data in
the binary format expected by the server.
This meant that we could reuse the implementation of prepared statement
execution. There are also two possible options to avoid an excessive
number of roundtrips: Either prepare a multiple-row INSERT statement or
prepare a single-row INSERT statement and execute it with multiple
parameter sets in one roundtrip.
There is currently a limitation in that an INSERT only can insert data
into one table at once, so it is not a solution for loading data from a
file into multiple tables.
Thanks,
Roy
Eric Day wrote:
Hi everyone,
I would suggest adding a new command at the protocol level and use
the same row/data protocol being used to return rows to instead
send rows for bulk insert. This way the server gets them pre-parsed,
we can use the same bits in the protocol library (just in reverse),
and then clients can implement the interface any way they like (CSV,
binary, ...). The command would take a table name as a parameter of
where to insert and then just needs to verify types on the way in. We
could also support parallel inserts by just using multiple commands
inserting into the same table.
-Eric
On Fri, Nov 28, 2008 at 06:51:53PM -0800, Jim Winstead wrote:
On Fri, Nov 28, 2008 at 6:41 PM, Brian Aker <[EMAIL PROTECTED]> wrote:
Hi!
On Nov 28, 2008, at 3:36 PM, Jim Winstead wrote:
in the way that the protocol is used to tell the client to send a
file.
What would make for a better protocol?
the problem, as i recall, is that what happens with LOAD DATA LOCAL
INFILE 'foo' is that the server sends a packet back to the client that
says "send the file named 'foo'". you don't have to think very hard to
find problems with that.
All we need, I believe, is the column headers and some sort of inline of the
data. The difference between LOAD DATA and a typical INSERT is that LOAD
DATA bypasses the parser.
Other then that, same handler calls.
an advantage to it being server-side is that it accessible from all
clients, and you don't have to do a straight load of the data, but you
can modify the data as it is going in. you can do all of that by
pre-processing the data with perl or something, but i simply have
found LOAD DATA LOCAL more convenient in many cases.
i use LOAD DATA LOCAL more often for ad-hoc data loads than for
anything i do routinely. so performance isn't my concern, convenience
is.
jim
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
