How to set NULL 1

[Cagliostro]

Hi,
How can I insert a null value into a table by using OCI or OO4O. I use C++.
Thanks,
Ion Ion Filipski

filipski@excite.com

 

[Turkbear]

Hi, in general, NULL is the value - so whatever the assignment operator is use NULL as the 'right' side -
FIELD2 := NULL for instance...

 

[Cagliostro]

Ok, you are not attentive. I want to do this through OCI or OO4O
for example I want to do:
....
insert into xxx(a, b, c)
values(123, 543435435, 456);
insert into xxx(a, b, c)
values(456, NULL, 123);
.....
In C++ I do:
....
OCIStmtPrepare(..&stmt....
"insert into xxx(a, b, c)valuesqq, :ww, :ee)" ...);
...
OCIBindByName(..., "qq", ... &x ...);
OCIBindByName(..., "ww", ... &y ...);
OCIBindByName(..., "ww", ... &z ...);
....
*x = 123;
*y = 543435435;
*z = 456;
...
OCIStmtExecute(...stmt....);
....
*x = 456;
*y = ?????????what to do here I don't know
....

*z = 123;
OCIStmtExecute(....stmt....);
....
Ion Filipski

filipski@excite.com

 

[sem]

You should set an indicator variable to -1. Regards, Dima

 

[Cagliostro]

Sorry, but I can't understand, what kind of indicator? Could you please give me a sample? Ion Filipski

filipski@excite.com

 

[sem]

Normally it's called indp and pass a pointer:

sword OCIBindByPos ( OCIStmt *stmtp,
OCIBind **bindpp,
OCIError *errhp,
ub4 position,
dvoid *valuep,
sb4 value_sz,
ub2 dty,
dvoid *indp,//<==indicator
ub2 *alenp,
ub2 *rcodep,
ub4 maxarr_len,
ub4 *curelep,
ub4 mode );


Read this article for details:

http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89857/oci02bas.htm#422173

Regards, Dima

 

[Chao]

Here is a VB sampel:

dim cn as adodb.connection
dim strSQL as string

...
set cn = new adodb.connection
cn.open database, user, password
...
cn.begintrans
strSQL = &quot;insert into XXX(a, b, c) values (1, 'test', null)&quot;
cn.execute strSQL
...
cn.CommitTrans

 

[Cagliostro]

I asked how to do it with OO4O, not with ADO. By the way, do you suggest me to pass parammeters by concatenating strings? This is the worst way to pass parramtetters to a query. Look at the code below:


Dim oraSession As New OracleInProcServer.OraSessionClass
Dim oraDb As OracleInProcServer.OraDatabase
Set oraDb = oraSession.OpenDatabase(&quot;mis&quot;, &quot;usr/pwd&quot;, 0&)
Dim insStmt As OraSqlStmt
Dim query As String
query = &quot;insert into xxx(a, b, c) &quot; _
& &quot; valuesqq, :ww, :ee)&quot;

Dim x As Integer
Dim y As String
Dim z As String
'initialize z, y, z in some way
oraDb.Parameters.Add &quot;qq&quot;, x, ORAPARM_INPUT
oraDb.Parameters(&quot;qq&quot.serverType = ORATYPE_NUMBER

oraDb.Parameters.Add &quot;ww&quot;, accessRs.Fields(&quot;data1&quot, ORAPARM_INPUT
oraDb.Parameters(&quot;ww&quot.serverType = ORATYPE_VARCHAR2

oraDb.Parameters.Add &quot;aa&quot;, accessRs.Fields(&quot;data2&quot, ORAPARM_INPUT
oraDb.Parameters(&quot;aa&quot.serverType = ORATYPE_VARCHAR2

Set insStmt = oraDb.CreateSQL(query, 0&)

oraSession.BeginTrans
While someCondidion
...do something
oraDb.Parameters(&quot;qq&quot.Value = x
oraDb.Parameters(&quot;ww&quot.Value = y 'I waht there a null to insert
oraDb.Parameters(&quot;ee&quot.Value = z
insStmt.Refresh
Wend
oraSession.CommitTrans
oraDb.Close
Ion Filipski

filipski@excite.com

 

[Cagliostro]

Look at the code below:


Dim oraSession As New OracleInProcServer.OraSessionClass
Dim oraDb As OracleInProcServer.OraDatabase
Set oraDb = oraSession.OpenDatabase(&quot;mis&quot;, &quot;usr/pwd&quot;, 0&)
Dim insStmt As OraSqlStmt
Dim query As String
query = &quot;insert into xxx(a, b, c) &quot; _
& &quot; valuesqq, :ww, :ee)&quot;

Dim x As Integer
Dim y As String
Dim z As String
'initialize z, y, z in some way
oraDb.Parameters.Add &quot;qq&quot;, x, ORAPARM_INPUT
oraDb.Parameters(&quot;qq&quot.serverType = ORATYPE_NUMBER

oraDb.Parameters.Add &quot;ww&quot;, accessRs.Fields(&quot;data1&quot, ORAPARM_INPUT
oraDb.Parameters(&quot;ww&quot.serverType = ORATYPE_VARCHAR2

oraDb.Parameters.Add &quot;aa&quot;, accessRs.Fields(&quot;data2&quot, ORAPARM_INPUT
oraDb.Parameters(&quot;aa&quot.serverType = ORATYPE_VARCHAR2

Set insStmt = oraDb.CreateSQL(query, 0&)

oraSession.BeginTrans
While someCondidion
...do something
oraDb.Parameters(&quot;qq&quot.Value = x
oraDb.Parameters(&quot;ww&quot.Value = y 'I waht there a null to insert
oraDb.Parameters(&quot;ee&quot.Value = z
insStmt.Refresh
Wend
oraSession.CommitTrans
oraDb.Close
Ion Filipski

filipski@excite.com