OCI: OCIDefineByPos vs. OCIBindByName and OCI_UCS2ID?

From: Mike Jensen <mike_jensen*nospam*99_at_yahoo.com>
Date: Fri, 10 May 2002 08:42:36 -0500
Message-ID: <3cdbcd7b$1_5@goliath2.newsgroups.com>

I have a whole set of stored procedures and OCI calls that work just fine with OCIBindByName - and am passing around null terminated strings. Been working for a couple of years. Now we want to move to a UTF-8 database - so I'm working to migrate my code.

win2k - so I'm taking my utf strings -> windows wide char -> passing them in with char set of OCI_UCS2ID.

When I retrieve data, I'm not receiving the last character. If I retrieve SQLT_CHR instead of SQLT_STR, then I get all the data and space padding, which I would need to trim off. I can pass in BindByName, and it'll match my varchar2 keys just fine (accountid, etc.), so ingoing it seems fine.

So, can I either do an OCIDefineByPos() for a stored procedure call ("begin stored_proc(:var1,:var2); end;") or am I missing something obvious here?

---

Here is code that works:
BeginOracleSession();

OCIStmt *stmt;
OCIBind *bndp1;

utext ename[256];
memset(ename,0,sizeof(ename));

ub2 csid = OCI_UCS2ID;
ub2 csform = SQLCS_IMPLICIT;
sb2 indicator = 0;

char *callstring1 = "select text from t01 where id = 50";

checkerr(errhp,OCIHandleAlloc( (dvoid *) envhp, (dvoid **)
&stmt,OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

checkerr(errhp,OCIStmtPrepare(stmt, errhp, (unsigned char *)callstring1,(ub4) strlen((char *) callstring1),(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); OCIDefine *dfnp = (OCIDefine *) 0;

checkerr(errhp, OCIDefineByPos (stmt, &dfnp, errhp, (ub4)1, (dvoid *)ename,(sb4)sizeof(ename), SQLT_STR,(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT));

checkerr(errhp, OCIAttrSet((dvoid *) dfnp, (ub4) OCI_HTYPE_DEFINE,(dvoid *)
&csid, (ub4) 0, (ub4)OCI_ATTR_CHARSET_ID, errhp));

checkerr(errhp,OCIStmtExecute(svchp, stmt, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));

I get wide char 'Test' back as I expect.

---

Now code that doesn't work:
BeginOracleSession();

OCIStmt *stmt;
OCIBind *bndp1;
sb2 name_col_len = 255;

utext ename[256];
memset(ename,0,sizeof(ename));

ub2 csid = OCI_UCS2ID;
ub2 csform = SQLCS_IMPLICIT;
sb2 indicator = 0;

char *callstring1 = "begin select text into :RET from t01 where id = 50; commit; end;";

checkerr(errhp,OCIHandleAlloc( (dvoid *) envhp, (dvoid **)
&stmt,OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

checkerr(errhp,OCIStmtPrepare(stmt, errhp, (unsigned char *)callstring1,(ub4) strlen((char *) callstring1),(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr(errhp,OCIBindByName(stmt, &bndp1, errhp, (unsigned char *)":RET",(sb4)strlen((char *)":RET"),(dvoid *) ename,sizeof(ename), SQLT_STR,(dvoid *) &indicator,(ub2 *)0,(ub2 *)0, (ub4)0,(ub4 *)0, OCI_DEFAULT)); checkerr(errhp,OCIAttrSet((dvoid *) bndp1, (ub4) OCI_HTYPE_BIND, (dvoid *)
&csform,(ub4) 0, (ub4)OCI_ATTR_CHARSET_FORM, errhp));

checkerr(errhp,OCIAttrSet((dvoid *) bndp1, (ub4) OCI_HTYPE_BIND, (dvoid *)
&csid,(ub4) 0, (ub4)OCI_ATTR_CHARSET_ID, errhp));

checkerr(errhp,OCIAttrSet((dvoid *) bndp1, (ub4) OCI_HTYPE_BIND, (dvoid *)
&name_col_len,(ub4) 0, (ub4)OCI_ATTR_MAXDATA_SIZE, errhp));

checkerr(errhp,OCIStmtExecute(svchp, stmt, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));

I only get wide char 'Tes' with this.

-----------== Posted via Newsgroups.Com - Uncensored Usenet News ==----------

   http://www.newsgroups.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Ulimited Fast Downloads - 19 Servers =----- Received on Fri May 10 2002 - 08:42:36 CDT