Re: Doubt with WHERE clause on OCIStmtPrepare statement with OCI

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