Doubt with WHERE clause on OCIStmtPrepare statement with OCI

#1

Hello,
I'm trying to run under OCI for C/C++ something like:

Quote:

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

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:

Quote:

string sSQL = "select offc_ipbase from nexqoffice, connection_type where offc_type = >coty_type and offc_lastsincro >:dSQLDate order by offc_id";

//Prepare statement
checkerr(p_err, OCIStmtPrepare(sqlStatement,p_err,(unsigned char *)
const_cast<const char *>(sSQL.c_str()),
strlen(sSQL.c_str()),OCI_NTV_SYNTAX,OCI_DEFAULT));

//Define Output Variables

OCIDefine * defnp = (OCIDefine *) 0;
short name_length=15;
char name[15];
checkerr(p_err, OCIDefineByPos(sqlStatement, &defnp, p_err, 1, (dvoid *) name,
name_length+1, SQLT_STR, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0,
OCI_DEFAULT));

// dSQLDate
char dSQLDate[80]; //to_date('2006/11/3:00:00:00', 'yyyy/mm/dd:hh24:mi:ss')
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.

Thank you very much.

#2

On 6 Nov 2006 02:07:49 -0800, albert.medela (AT) gmail (DOT) com wrote:

Quote:

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.

Quote:

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.

Quote:

// 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.

Quote:

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 (AT) andyh (DOT) co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

#3

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:

Quote:

On 6 Nov 2006 02:07:49 -0800, albert.medela (AT) gmail (DOT) 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 (AT) andyh (DOT) co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

#4

On 7 Nov 2006 01:18:36 -0800, albert.medela (AT) gmail (DOT) com wrote:

Quote:

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.

OK, but I'd say you should consider trying again in that direction.

Quote:

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 :-)

This is not a good solution because it will hard parse for each different date
value - the golden rule with Oracle is to always use bind variables and not to
embed variable values in the statement. (There's exceptions to the rule, but
they're very rare indeed).

--
Andy Hassall :: andy (AT) andyh (DOT) co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Doubt with WHERE clause on OCIStmtPrepare statement with OCI

comp.databases.oracle.misc comp.databases.oracle.misc

Doubt with WHERE clause on OCIStmtPrepare statement with OCI - 11-06-2006 , 04:07 AM

Re: Doubt with WHERE clause on OCIStmtPrepare statement with OCI - 11-06-2006 , 06:23 PM

Re: Doubt with WHERE clause on OCIStmtPrepare statement with OCI - 11-07-2006 , 03:18 AM

Re: Doubt with WHERE clause on OCIStmtPrepare statement with OCI - 11-07-2006 , 10:12 AM