Re: OCIDefineByPos and ratrieving a DATE column

On 22 Feb 2006 06:31:59 -0800, "Herode" <cbrun_at_geomapgis.com> wrote:

>Here is another bug (in my code, I mean...) I can't get rid off. I try
>to read a DATA column with the following code :

 DATE, not DATA?

>int nCol = 0;
>st = ::OCIParamGet( m_pStmt, OCI_HTYPE_STMT, m_pErr, &colDesc, nCol + 1
>);
>st = ::OCIAttrGet( colDesc, OCI_DTYPE_PARAM, &lst.size, 0,
>OCI_ATTR_DATA_SIZE, m_pErr );
>st = ::OCIAttrGet( colDesc, OCI_DTYPE_PARAM, &lst.type, 0,
>OCI_ATTR_DATA_TYPE, m_pErr );
>st = ::OCIAttrGet( colDesc, OCI_DTYPE_PARAM, &buf, &nameLg,
>OCI_ATTR_NAME, m_pErr );
>lst.colName.Format( "%.*s", nameLg, buf );
>
>lst.size = 51;
>// buffer for the DATE. Orale doc says that SQLT_DAT is char[ 7 ] but
>that's obviously a joke...

 No, not a joke, it's the size of the internal representation of the DATE datatype. You want a string representation, which will be longer.

>lst.m_buf = new char[ lst.size ];
>st = ::OCIDefineByPos( m_pStmt, &defH, m_pErr, nCol + 1,
>lst.m_buf, lst.size, SQLT_STR, &lst.m_nullInd, 0, NULL,
>OCI_DEFAULT );
>
>//and fetch...
>st = ::OCIStmtFetch2( m_pStmt, m_pErr, 1, OCI_FETCH_NEXT, 1,
>OCI_DEFAULT );
>cout << lst.colName.GetBuffer( 0 ) << " = " << (char*) lst.m_buf <<
>endl;
>-------------------------------------------------------------------------------------
>
>The output gives me the YY/MM/DD date, but looses the timestamp (hours,
>minutes & so on).

 Set NLS_DATE_FORMAT, and/or use an explicit conversion in the query, e.g.:

    TO_CHAR(col, 'YYYY-MM-DD HH24:MI:SS')

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