TestStand 12.0 Oracle 11g and autogenerate ID

[Curt_C]

Hi,

I'm trying to configure the result processing on my test stations to write to Oracle 11g. I've had success setting up the connection and creating the tables. We are currently writing to Access without any issues. The problem occurs when trying to update the tables in Oracle and the primary key "ID" isn't auto-generated as in Access and TestStand tries to write a NULL value to the Oracle tables throwing an error. Does TestStand have a seq or method to auto-generate the ID value?

Any help would be appreciated!

I've never worked with Oracle before so this is all new to me.

Thanks,

Curt

[Maxwell.B]

Hi Curt,

I'm not much of a help with Oracle, but there is this knowledge base article that seems like it could be useful. 

Display the Unique Step ID of TestStand Steps in the Step Settings Pane

https://knowledge.ni.com/KnowledgeArticleDetails?id=kA00Z000000P7Z9SAK&l=en-US

[Curt_C]

Hi,

After a lot of cursing and swearing about how much I hate Oracle and a lot of Google searches here's how you create an auto-incremented unique ID for the tables.

It all happens in Oracle. Note: Oracle 12 and later have this function built in.

For Oracle 11g:

In the Tablespace management utility create sequences and triggers. Create them for each table in your schema. Hope this helps someone

Curt

//sequence name for uut_result

YOUR_SCHEMA_NAME.UUT_RESULT_SEQ

//trigger name for uut result

UUT_RESULT_INS_TRG

// code for the trigger

BEGIN 
IF(:NEW.ID IS NULL) THEN 
:NEW.ID:=YOUR_SCHEMA_NAME.UUT_RESULT_SEQ.NEXTVAL; 
END IF; 
END;

[sureshprabhu1538]

Can you please explain me in detail

[sureshprabhu1538]

Here you get the clear answer regarding auto generate ID in Oracle 11g in test stand 12.0

7

You can get the returning id into a variable. For example, this code:

$data = array("larry","bill","steve");
$db = OCILogon("scott","tiger");
$stmt = OCIParse($db,"insert into names values (myid.nextval,:name) returning id into :id");

OCIBindByName($stmt,":ID",$id,32);
OCIBindByName($stmt,":NAME",$name,32);

while (list(,$name) = each($data))
{
OCIExecute($stmt);
echo "$name got id:$id\n"; 
}

This gives you the ID got by $name in form of the Oracle applications variable $id. Change your SQL accordingly.