Re: Doubt with WHERE clause on OCIStmtPrepare statement with OCI

From: <albert.medela_at_gmail.com>
Date: 7 Nov 2006 01:18:36 -0800
Message-ID: <1162891116.050699.16690@f16g2000cwb.googlegroups.com>

Thanks a lot for your answer. I put '>' before the code to distingish the 'explanation text' from the 'code'. When I saw my post, I knew it was a bad idea :-)

I didn't know I coudn't put the 'to_date' in the bound variable. But I also tested what you tell me, and it didn't work, neither. But I don't remember what was the error that Oracle gave me. I guess I made another mistake.

I solved the problem in other way without binding any variable. I attached all query in a character string in this way:

string sSQL = "select offc_ipbase,offc_id, offc_name,

offc_tolerance,offc_inactive, offc_monitor, offc_type, coty_bandwith,
offc_center_id  from nexqoffice, connection_type where offc_type =
coty_type and offc_lastsincro > ";

sSQL = sSQL + dSQLDate;
sSQL = sSQL + " order by offc_id";

where dSQLDate =
to_date('%04d/%02d/%02d:00:00:00','yyyy/mm/dd:hh24:mi:ss') replacing year, month and day :-)

Anyway, thaks a lot, and sorry for my english :-)

Albert.

Andy Hassall ha escrit:
> On 6 Nov 2006 02:07:49 -0800, albert.medela_at_gmail.com wrote:
>
> >I'm trying to run under OCI for C/C++ something like:
> >
> >>select offc_ipbase
> >>from nexqoffice, connection_type
> >>where offc_type = coty_type and offc_lastsincro > to_date('2006/11/3:00:00:00', '>yyyy/mm/dd:hh24:mi:ss')
> >>order by offc_id
>
> Your formatting seems to be mangled here, I'm assuming some of the embedded
> ">" are supposed to be on new lines.
>
> >where 'to_date...' is a variable. How Can I do this. I've been testing
> >several (wrong) ways but all of them sends me an error. The code of my
> >last test is:
> >
> >>string sSQL = "select offc_ipbase from nexqoffice, connection_type where offc_type = >coty_type and offc_lastsincro >:dSQLDate order by offc_id";
>
> OK, so you have one bind variable being compared directly with a date.
>
> You can either bind this as a date, or as a string implicitly using the
> current date format.
>
> >>// dSQLDate
> >>char dSQLDate[80]; //to_date('2006/11/3:00:00:00', 'yyyy/mm/dd:hh24:mi:ss')
>
> What do you mean by that comment? You can't put "to_date" in the bound value;
> that's a function, you can only bind values.
>
> >>OCIBind *p_dSQLDate = NULL; // client and server Ips
> >>checkerr(p_err,OCIBindByPos(sqlStatement, &p_dSQLDate , p_err, 1, dSQLDate, 80,
> >>SQLT_CHR, 0, 0, 0, 0, 0, OCI_DEFAULT));
> >
> >Then I make the execute statement. I guess the problem is in the
> >dSQLDate variable, but I do not know which is it.
> >
> >The error that OCI returns me is:ORA-01858: a non-numeric character was
> >found where a numeric was expected.
>
> Put your to_date in the SQL statement itself, and have :dSQLDate as the
> parameter to to_date, i.e. start with:
>
> string sSQL = "select offc_ipbase from nexqoffice, connection_type where
> offc_type = coty_type and offc_lastsincro > to_date(:dSQLDate,
> 'yyyy/mm/dd:hh24:mi:ss') order by offc_id";
>
> ... and just bind the date value.
>
> Incidentally your value is 2006/11/3:00:00:00 - shouldn't there be a leading
> zero on the 3 to completely match the date format?
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Received on Tue Nov 07 2006 - 03:18:36 CST