Selecting primary key when field name is unknown 1

[ChrisQuick]

I am in the proccess of writing an application that retieves records from Oracle, and allows the user to update them through ODBC. I need to be able to dynamically determine based on a select statement what the primary field is for the table the user selects. Is there a way to select all the fields of a table EXCEPT the primary key when the primary key field name is unknown? If not, is there a way of selecting just the primary field. If I can at least isolate it, I could work around it. I guess the last option, if the other two aren't workable, is a boolean to determine if each field is (or is not) the primary. <br>
<br>
Any help will be appreciated (but not in monetary terms).

 

[bitbrain]

I’ll be interested to see what kind of advice you get on this one. My thoughts are…<br>
<br>
Read the Oracle catalog to get the primary key information. Don’t know what the format is in Oracle but in SQL Server you can read the master database to get information about the tables and columns. There may be security considerations.<br>
<br>
Assuming you can get access to the catalog and identify the name of the key field… Write code to dynamically build the SQL statement by concatenating strings, pseudo code would be something like:<br>
<br>
Read catalog using table name as key, retrieve all column names<br>
StrSQL = “SELECT “<br>
For each column name <br>
If column name &lt;&gt; primary key name<br>
StrSQL = strSQL & column name & “, “<br>
End if<br>
Next<br>
<br>
StrSQL = strSQL & “FROM “ table name<br>
<br>
(or something like that)<br>
<br>
<br>
Alternatively, populate a grid control with all but the key field as follows:<br>
<br>
Execute “SELECT * FROM “ & table name<br>
For each row in recordset<br>
For each field in fields collection<br>
If field name &lt;&gt; primary key name<br>
Grid value = field value<br>
End if<br>
Next<br>
Next<br>
<br>
Happy coding!<br>

 

[MikeLacey]

for the table cspr.cshdprsp<br>
<br>
select column_name<br>
&nbsp;&nbsp;&nbsp;&nbsp;from all_cons_columns<br>
&nbsp;&nbsp;&nbsp;&nbsp;where constraint_name = (<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select constraint_name<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from all_constraints<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where owner='CSPR'<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and table_name='CSHDPRSP'<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and constraint_type='P'<br>
&nbsp;&nbsp;&nbsp;&nbsp<br>
;<br>
<br>
This works for oracle 7.3.4 but i've not tested it on any other versions. It can be safely tested on other versions without harm as it attempts no updates.<br>
<br>
Mike<br>
<p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href= > </a><br>

 

[carp]

Mike's query will work in all versions of Oracle7/8.