Re: Oracle database question
If I can latch onto Scott's question, does anyone know if a 'batch insert' is possible in Oracle (using the LV Database Connectivity Toolkit)? I want to insert up to 200 records (or as many as I can!) with a single "execution". I've been able to achieve one with MSDE, but not Oracle. Thanks for any help. Becca MacMaster - Original Message - From: "Scott Serlin" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 09, 2004 2:50 PM Subject: FW: Oracle database question Can anyone tell me if it is possible to treat an Oracle database like a giant array? More to the point, do I have to always write a complete record (or row) of data into a table or is there a way to place a piece of data into one single column within the row and come back later and place a different piece of data in the same row but different column? Example: I wrote the first piece of data like this: Table testtime testdate dut serialnum productline 12:00 Later on I wanted to add another data point in the same row but different column while still maintaining the data previously entered: Table testtime testdate dut serialnum productline Can I do this? Is it allowed in Oracle? 12:00 100100 Once the row is complete, I would move onto the next row. Table testtime testdate dut serialnum productline 12:00 010603 1100100 widget 1:00 010603 2100101 widget
Re: Oracle database question
Hi Scott. I'm not familiar with LVs Database Toolkit, but I can try to
address your question from a pure SQL standpoint. Maybe you already
figured this out, but:
You don't always have to write a full row to a table. As long as the
the columns you will be writing to are designed with ALLOW NULL or given
a DEFAULT value.
Your first write should creat the new row using an INSERT statement such
as:
INSERT INTO mytable values (testtime) values ('12:00')
The empty rows will be NULL or contain the DEFAULKT you specified when
creating the table. And then the next column should be written with an
UPDATE statement:
UPDATE mytable SET serialnum = 100100 WHERE testtime = '12:00'
Though, in reality, you should use something else beside time to
identify the row you are updating.
And the next columns are written as:
UPDATE mytable SET serialnum = 100100 WHERE testtime = '12:00'
UPDATE mytable SET serialnum = 100100 WHERE testtime = '12:00'
PS. The SQL statements above might need to be tweeked for us on Oracle.
Hope that helps a bit.
***
John Paul Osborne
http://members.aol.com/josborne01
***
Scott Serlin wrote on 1/9/2004, 8:50 PM:
> Can anyone tell me if it is possible to treat an Oracle database like
> a giant array? More to the point, do I have to always write a
> complete record (or row) of data into a table or is there a way to
> place a piece of data into one single column within the row and come
> back later and place a different piece of data in the same row but
> different column?
>
> Example:
> I wrote the first piece of data like this:
>
> Table
> testtime testdate dut serialnum productline
> 12:00
>
>
> Later on I wanted to add another data point in the same row but
> different column while still maintaining the data previously entered:
>
> Table
> testtime testdate dut serialnum productline Can I do this? Is it
> allowed in Oracle?
> 12:00 100100
>
> Once the row is complete, I would move onto the next row.
>
> Table
> testtime testdate dut serialnum productline
> 12:00 010603 1100100 widget
> 1:00010603 2100101 widget
>
>
>
>
>
RE: Oracle database question
Hello Scott, I am a MySQL user, so you have to take what I say with a handfull of salt. You should be able to do what you descibed. If Oracle is similar to MySQL in essence, then these comments may be useful. In the simplest INSERTions MySQL wants its data entered as complete rows. But you can direct it to place data in a particular column by using the SET column function. In the insert statement you first indicate the table and row of interest then direct its attention to a particular column. I won't trouble you with syntactical info. Another method would be to SELECT the entire row of interest and use that data to formulate an entirely new row with the new entry, then UPDATE the whole row. I have to believe that Oracle can perform one or bith of these methods. To administer the DB as a big array, you need to use an auto-incremented primary key column. For example create a column called dataID and set it to be the primary key for the table. Then every time a new row is added it automatiaclly gets a row index, starting from 0 and going up. Looking at what you have described for a table, it seems similar to the data warehousing that I do. When I perform a test I create two tables. One is for "metadata" that contains "single point" info about the test. Such as testDate, testTIME, UUT_ID, ProjectPhase, testOPERATOR. Then there is a data table containing the thousands of data points generated duriong the test. Every row of data in both tables receives the same testID for that test. The metatdata table has one row created with a testID created automatically and incremented automatically. Every row of the data table gets the same testID (as the metadata entry) and each row gets a unique dataID that is auto-incremented. You can query the metadata to find tests with similarities that you want to examine. Like, what is the testID of the tests where temperature was maintained at 150degreeC? Then you retrieve from the data table for anaylsis using those testIDs. You would have 2 arrays in the DB the row and column coordinates of which would be metatdata:(testID,column_name), and data:(dataID,column_name). Whenever I write about DB stuff I wonder if it really makes sense in the end. Good luck, Mike Ross > -Original Message- > From: Scott Serlin [mailto:[EMAIL PROTECTED] > Sent: Friday, January 09, 2004 2:50 PM > To: info-labview > Subject: FW: Oracle database question > > > > Can anyone tell me if it is possible to treat an Oracle > database like a giant > array? More to the point, do I have to always write a > complete record (or > row) of data into a table or is there a way to place a piece > of data into one > single column within the row and come back later and place a > different piece > of data in the same row but different column? > > Example: > I wrote the first piece of data like this: > > Table > testtime testdate dut serialnum productline > 12:00 > > > Later on I wanted to add another data point in the same row > but different > column while still maintaining the data previously entered: > > Table > testtime testdate dut serialnum productline Can I do this? Is it > allowed in Oracle? > 12:00 100100 > > Once the row is complete, I would move onto the next row. > > Table > testtime testdate dut serialnum productline > 12:00 010603 1100100 widget > 1:00 010603 2100101 widget > > > > > >
RE: Oracle database question
Hi Scott:
It depends, mainly on your datatype definitions, Oracle has a very
solid Time/Date type definition, if you want to update or insert into a
table where all datatypes are of the same kind ( varchar2) for instance it
will be pretty easy to do insertions.
But if you are very concerned about making queries where the time is very
important to analyze your results (which happens in most cases), then you
have to define your time column as date datatype, and then is when you have
to be carefull.
Whenever you have to write to a date field you have to use the date time
conversion function in order that the information is stored in the way you
really wanted.
The sintaxis for this functions is normally not very friendly depending upon
how you write your data, but a way to overcome this very easily is to use
the SYSDATE keyword.
SYSDATE will store the current time in the appropriate format in your
database, you must realize it is the date at the time of the insert
statement and not at the time when you Acquired your data, but for most
systems (like mine here) this is good enough.
It used to be an excellent reference page in the internet for Oracle, it was
on Princeton University website, but it has been lock out since like 5
months ago, so you have to rely on Oracle's help system for you sintaxis
definition.
--
Second, please read the SQL tutorial at w3schools, whenever you generate a
record for the first time using INSERT, the second time you want to add
data you have to use an UPDATE statement
Based on the information you have below, you may have to generate the new
row using an INSERT command
INSERT INTO TableName(testime) values('12:00');
Then add the remaining data with UPDATE statements
Like :
UPDATE TableName SET testdate='01-13-2004' where ID=whatever your ID is;.
And so on.
, OR
you may want to do all at once
INSERT INTO TableName(testime,serialnum,productline)
values('12:00','0010001','widget');
This is the same regardless you are using MySQL or Oracle.
Thanks
Diego
-Original Message-
From: Scott Serlin [mailto:[EMAIL PROTECTED]
Sent: Friday, January 09, 2004 2:51 PM
To: [EMAIL PROTECTED]
Subject: FW: Oracle database question
Can anyone tell me if it is possible to treat an Oracle database like a
giant array? More to the point, do I have to always write a complete record
(or row) of data into a table or is there way to place a piece of data into
one single column within the row and come back later and place a different
piece of data in the same row but different column?
Example:
I wrote the first piece of data like this:
Table
testtime testdate dut serialnum productline
12:00
Later on I wanted to add another data point in the same row but different
column while still maintaining the data previously entered:
Table
testtime testdate dut serialnum productline Can I do this? Is it
allowed in Oracle?
12:00 100100
Once the row is complete, I would move onto the next row.
Table
testtime testdate dut serialnum productline
12:00 010603 1100100 widget
1:00010603 2100101 widget
RE: Oracle database question
Thanks everyone for the database help. I will work with everyone's suggestions. > -Original Message- > From: Scott Serlin [mailto:[EMAIL PROTECTED] > Sent: Friday, January 09, 2004 2:50 PM > To: info-labview > Subject: FW: Oracle database question > > > > Can anyone tell me if it is possible to treat an Oracle > database like a giant > array? More to the point, do I have to always write a > complete record (or > row) of data into a table or is there a way to place a piece > of data into one > single column within the row and come back later and place a > different piece > of data in the same row but different column? > > Example: > I wrote the first piece of data like this: > > Table > testtime testdate dut serialnum productline > 12:00 > > > Later on I wanted to add another data point in the same row > but different > column while still maintaining the data previously entered: > > Table > testtime testdate dut serialnum productline Can I do this? Is it > allowed in Oracle? > 12:00 100100 > > Once the row is complete, I would move onto the next row. > > Table > testtime testdate dut serialnum productline > 12:00 010603 1100100 widget > 1:00 010603 2100101 widget > > > > > >
