🚀 go-pugleaf

Hi again,

Do you get a ORA-01036?

Actually looking back at the code we had loads of problems with Oracle's
OLEDB driver (and the alternatives had problems of there own so not much
point changing). Do you get a ORA-01036. What we did to get over this
problem dynamically write the SQL so that if a value is null you explicitly
set it to NULL in the SQL statement.

i.e. your final SQL should look something like ...

INSERT INTO MYTABLE (COL1, COL2, COL3) VALUES(:COL1, NULL, :COL3)

This implementation probably calls for an object (or record structure) to
hold the column names associated with your table.

This means that in your code you would of looped through your columns of
your object and found that
1. the value for COL1 was not NULL so you set-up a :COL1 parameter
placeholder which you set the value of later
2. the value for COL2 was NULL so you use set it explicitly as NULL
3. the value for COL3 was not NULL so you set-up a :COL3 parameter
placeholder which you set the value of later

Furthermore as you are now dynamically writing the SQL we also found you
need to set the  Parameter type property.
Param.ParameterObject.Type_ := COL1.ColumnType

So it also means that you will need to store away the fieldtype(column type)
of each column in your table so that you can set this value which we did
after the parameter's value had been set. If your using Oracle you can look
this up before hand using USER_TABLES views.


Hope this helps

Let us know if resolves the problems you were having.

P.S. If your planning to insert into TEXTBLOBS let us know as we've
implemented a workaround for this to.

FrontMan Solutions Ltd

Independent Software Consulting
mailto:Services.FrontManSolutions@ic24.net



Arnie Mauer <arnie@marketexpert.com> wrote in message
news:3b24b35d$1_1@dnews...
> Hmmm.  Interesting.  But, it doesn't work.
>
> BTW, using the Oracle OLE DB provider.
>
> When I assign NULL, the TParameter Value is 0 and the Size is -1.  On an
> INSERT, it actually puts a "0" into the table column.  I really want an
> empty
> value, not zero.  However, I found that when I assign a NULL AnsiString or
> empty C string and then set the TParameter Size to -1, it seems to give
> the desired result.  It seems unhappy only when the Size is zero.
>
> "BM" <m02x7z00@cwcom.net> wrote in message news:3b23a7eb_2@dnews...
> > First of all I'm more familiar to Delphi so I apologies for any
> > discrepancies.
> > Can't tell you why these things happen but from our experience if you
have
> a
> > NULL string you must set the parameter to NULL (i.e check its a blank
> > string- if it is set Parameter.Value = NULL) otherwise you see this
error.
> > Another advisable thing to do is initialise all parameters to NULL i.e.
> > before any parameters are set is loop through them and set their value
to
> > NULL.
> >
> > BM
> >
> >
> > Arnie Mauer <arnie@marketexpert.com> wrote in message
> > news:3b20ed67_2@dnews...
> > > In a parameterized INSERT statement in a TADOQuery, when setting
> > > q->Parameters->ParamByName( "foo" )->Value = "";
> > > will cause q->ExecSQL() to throw an excpetion of: "Parameter object is
> > > improperly defined.  Inconsistent or incomplete information
> > > was provided."  Same goes for a NULL AnsiString.
> > >
> > > The TParameter Value == NULL and Length == 0.  The DataType
> > > has been properly set to ftString after the assignment.  What's
> > > wrong with a NULL string?
> > >
> > >
> >
> >
>
>