Insert CLOB

[richdb]

Hi,

I want to insert data into a clob field with SQL, but i got problems with the
4000 car. limit of SQL (it's an Oracle 10G database).

I'm using a ZConnection and a ZQuery, this works for all values except the clob field. The data of the clob field is coming from a memo field in a microsoft access database. And this query will been repeat for all the rows in the database.

Code: Select all

SQL := 'insert into geografisch values (' + actie + ', ' + actie2 + ', ' + actie3 + ', ' + actie4 + ', ' + actie5 + ', ' + actie6 + ', ' + actie7
            + ', ' + actie8 + ', ' + actie9 + ', ' + actie10 + ', ' + actie11 + ')';
           ZConnection1.ExecuteDirect(SQL);

Can someone help me ? What is the best way to insert/store clob fields when using Zeos?

Thank you.

[mdaems]

Do I recognize some other ducht speaker

Anyway, here's my advise. I didn't test this however. I've no Oracle server available.
Instead of generating these insert statements you could open a ZQuery with sql = 'select * from geografisch where 1=0' (condition to avoid loading data that's already in the table).
Then for every record:

Code: Select all

ZQuery1.Append;
ZQuery1.FieldbyName('Fieldname1').AsString := actie;
...
ZQuery1.Post

A first scan of the oracle specific code makes me think this doesn't create a very long sql string but prepares a query with placeholders that are bound one by one at execution time.

Mark

[richdb]

Yes u did recognize that well .... oeps dutch words in the code

But thanks for the answer, it works!