BorlandTalk.com

Roger Venad · Guest

Hello

My problems :- Getting information for dbExpress which BDE API used to do.

My present problem is getting referential integrity information of a table
for different db servers.

Well I am sure that by using sql select on the database directly (system
views) I will be able to get the information's for referential keys or
others.

But my point is that the problem is not on getting the information's using
system tables of db server but since I am migrating my applications from BDE
to DbExpress and the code uses

DbiOpenRIntList(DBase.Handle,PCHAR(FindOwner(DBase.DatabaseName,Table)),nil,
cur or other API of BDE. I have to formulate the query or result to get a
similar result which the API of BDE returns.

It is a shame that Borland does not provide substitutes for the API call
with dbExpress or similar solutions.

Analyzing the query that dbe does for DbiOpenRIntList I found the following
statements below.

I do not think there is an easy solution since we work with MSSQL, ORACLE,
DB2, AS400 etc.

If someone could help me or some hidden authors who said that it is not
painful in migrating app from bde to dbExpress will be able to help me or
give me some idea.

Sorry for my English. Moreover I am a bit let down by the above problem

thanks in advance

Here is the SQL I used for Oracle database but here i do not get the
complete informations that bde returns

select c.constraint_name, c.table_name child_table, c.column_name
child_column, p.table_name parent_table, p.column_name parent_column,
'MASTER' AS COL_MASTER
from sys.all_constraints l, sys.all_cons_columns c, sys.all_cons_columns p
where l.constraint_type = 'R' and l.constraint_name = c.constraint_name and
l.r_constraint_name = p.constraint_name and l.owner = c.owner
and l.r_owner = p.owner and c.position = p.position and l.owner = 'AAA' and
l.table_name = 'MENU_MASTER'
ORDER BY parent_table

Here is the log of SQL Explorer
1 16.53.30 Log started for: delphi32
2 16.53.36 Log started for: Untitled Application
3 16.53.47 SQL Prepare: ORACLE - select owner, object_name,
object_type, created from sys.all_objects where object_type in ('TABLE',
'VIEW' ) and owner = 'WCM' and object_name = 'MOVIMENTI' order by 1 ASC, 2
ASC
4 16.53.47 SQL Execute: ORACLE - select owner, object_name,
object_type, created from sys.all_objects where object_type in ('TABLE',
'VIEW' ) and owner = 'WCM' and object_name = 'MOVIMENTI' order by 1 ASC, 2
ASC
5 16.53.47 SQL Stmt: ORACLE - Fetch
6 16.53.47 SQL Stmt: ORACLE - Fetch
7 16.53.47 SQL Stmt: ORACLE - EOF
8 16.53.47 SQL Stmt: ORACLE - Close
9 16.53.47 SQL Prepare: ORACLE - select b.r_owner, b.constraint_name,
'MASTER', b.owner, b.table_name, b.delete_rule from sys.all_constraints a,
sys.all_constraints b where a.owner = 'WCM' and a.table_name = 'MOVIMENTI'
and a.constraint_type = 'P' and a.owner = b.r_owner and
a.constraint_name = b.r_constraint_name union select a.owner,
a.constraint_name, 'DETAIL', b.owner, b.table_name, a.delete_rule from
sys.all_constraints a, sys.all_constraints b where a.owner = 'WCM' and
a.table_name = 'MOVIMENTI' and a.constraint_type = 'R' and a.r_owner =
b.owner and a.r_constraint_name = b.constraint_name order by 1 ASC, 2 ASC
10 16.53.47 SQL Execute: ORACLE - select b.r_owner, b.constraint_name,
'MASTER', b.owner, b.table_name, b.delete_rule from sys.all_constraints a,
sys.all_constraints b where a.owner = 'WCM' and a.table_name = 'MOVIMENTI'
and a.constraint_type = 'P' and a.owner = b.r_owner and
a.constraint_name = b.r_constraint_name union select a.owner,
a.constraint_name, 'DETAIL', b.owner, b.table_name, a.delete_rule from
sys.all_constraints a, sys.all_constraints b where a.owner = 'WCM' and
a.table_name = 'MOVIMENTI' and a.constraint_type = 'R' and a.r_owner =
b.owner and a.r_constraint_name = b.constraint_name order by 1 ASC, 2 ASC
11 16.53.47 SQL Stmt: ORACLE - Fetch
12 16.53.47 SQL Stmt: ORACLE - Fetch
13 16.53.47 SQL Stmt: ORACLE - EOF
14 16.53.47 SQL Stmt: ORACLE - Close
15 16.53.47 SQL Prepare: ORACLE - select owner, index_name, uniqueness
from sys.all_indexes where table_owner = 'WCM' and table_name = 'MOVIMENTI'
order by owner ASC, index_name ASC
16 16.53.47 SQL Execute: ORACLE - select owner, index_name, uniqueness
from sys.all_indexes where table_owner = 'WCM' and table_name = 'MOVIMENTI'
order by owner ASC, index_name ASC
17 16.53.47 SQL Stmt: ORACLE - Fetch
18 16.53.47 SQL Stmt: ORACLE - Fetch
19 16.53.47 SQL Stmt: ORACLE - Fetch
20 16.53.47 SQL Stmt: ORACLE - Fetch
21 16.53.47 SQL Stmt: ORACLE - Fetch
22 16.53.47 SQL Stmt: ORACLE - Fetch
23 16.53.47 SQL Stmt: ORACLE - Fetch
24 16.53.47 SQL Stmt: ORACLE - Fetch
25 16.53.47 SQL Stmt: ORACLE - Fetch
26 16.53.47 SQL Stmt: ORACLE - EOF
27 16.53.47 SQL Stmt: ORACLE - Close
28 16.53.47 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_CHIAVE_PROGRESSIVA' order by column_position ASC
29 16.53.47 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_CHIAVE_PROGRESSIVA' order by column_position ASC
30 16.53.47 SQL Stmt: ORACLE - Fetch
31 16.53.47 SQL Stmt: ORACLE - Fetch
32 16.53.47 SQL Stmt: ORACLE - EOF
33 16.53.47 SQL Stmt: ORACLE - Close
34 16.53.47 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_MOVIMENTI_CERTMOV' order by column_position ASC
35 16.53.47 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_MOVIMENTI_CERTMOV' order by column_position ASC
36 16.53.47 SQL Stmt: ORACLE - Fetch
37 16.53.47 SQL Stmt: ORACLE - Fetch
38 16.53.47 SQL Stmt: ORACLE - EOF
39 16.53.47 SQL Stmt: ORACLE - Close
40 16.53.47 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_MOVIMENTI_DIP' order by column_position ASC
41 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_MOVIMENTI_DIP' order by column_position ASC
42 16.53.48 SQL Stmt: ORACLE - Fetch
43 16.53.48 SQL Stmt: ORACLE - Fetch
44 16.53.48 SQL Stmt: ORACLE - EOF
45 16.53.48 SQL Stmt: ORACLE - Close
46 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_10'
order by column_position ASC
47 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_10'
order by column_position ASC
48 16.53.48 SQL Stmt: ORACLE - Fetch
49 16.53.48 SQL Stmt: ORACLE - Fetch
50 16.53.48 SQL Stmt: ORACLE - EOF
51 16.53.48 SQL Stmt: ORACLE - Close
52 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_8'
order by column_position ASC
53 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_8'
order by column_position ASC
54 16.53.48 SQL Stmt: ORACLE - Fetch
55 16.53.48 SQL Stmt: ORACLE - Fetch
56 16.53.48 SQL Stmt: ORACLE - EOF
57 16.53.48 SQL Stmt: ORACLE - Close
58 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_9'
order by column_position ASC
59 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_9'
order by column_position ASC
60 16.53.48 SQL Stmt: ORACLE - Fetch
61 16.53.48 SQL Stmt: ORACLE - Fetch
62 16.53.48 SQL Stmt: ORACLE - EOF
63 16.53.48 SQL Stmt: ORACLE - Close
64 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'NRIGA' order
by colmn_position ASC
65 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'NRIGA' order
by column_position ASC
66 16.53.48 SQL Stmt: ORACLE - Fetch
67 16.53.48 SQL Stmt: ORACLE - Fetch
68 16.53.48 SQL Stmt: ORACLE - EOF
69 16.53.48 SQL Stmt: ORACLE - Close
70 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'SYS_C002719'
order by column_position ASC
71 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'SYS_C002719'
order by column_position ASC
72 16.53.48 SQL Stmt: ORACLE - Fetch
73 16.53.48 SQL Stmt: ORACLE - Fetch
74 16.53.48 SQL Stmt: ORACLE - Fetch
75 16.53.48 SQL Stmt: ORACLE - Fetch
76 16.53.48 SQL Stmt: ORACLE - Fetch
77 16.53.48 SQL Stmt: ORACLE - Fetch
78 16.53.48 SQL Stmt: ORACLE - Fetch
79 16.53.48 SQL Stmt: ORACLE - Fetch
80 16.53.48 SQL Stmt: ORACLE - EOF
81 16.53.48 SQL Stmt: ORACLE - Close
82 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_cons_columns where owner = 'WCM' and constraint_name =
'SYS_C003650' order by position ASC
83 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_cons_columns where owner = 'WCM' and constraint_name =
'SYS_C003650' order by position ASC
84 16.53.48 SQL Stmt: ORACLE - Fetch
85 16.53.48 SQL Stmt: ORACLE - Fetch
86 16.53.48 SQL Stmt: ORACLE - Fetch
87 16.53.48 SQL Stmt: ORACLE - Fetch
88 16.53.48 SQL Stmt: ORACLE - EOF
89 16.53.48 SQL Stmt: ORACLE - Close
90 16.53.48 SQL Prepare: ORACLE - select owner, object_name,
object_type, created from sys.all_objects where object_type in ('TABLE',
'VIEW' ) and owner = 'WCM' and object_name = 'RAPPORTI' order by 1 ASC, 2
ASC
91 16.53.48 SQL Execute: ORACLE - select owner, object_name,
object_type, created from sys.all_objects where object_type in ('TABLE',
'VIEW' ) and owner = 'WCM' and object_name = 'RAPPORTI' order by 1 ASC, 2
ASC
92 16.53.48 SQL Stmt: ORACLE - Fetch
93 16.53.48 SQL Stmt: ORACLE - Fetch
94 16.53.48 SQL Stmt: ORACLE - EOF
95 16.53.48 SQL Stmt: ORACLE - Close
96 16.53.48 SQL Prepare: ORACLE - select owner, index_name, uniqueness
from sys.all_indexes where table_owner = 'WCM' and table_name = 'RAPPORTI'
order by owner ASC, index_name ASC
97 16.53.48 SQL Execute: ORACLE - select owner, index_name, uniqueness
from sys.all_indexes where table_owner = 'WCM' and table_name = 'RAPPORTI'
order by owner ASC, index_name ASC
98 16.53.48 SQL Stmt: ORACLE - Fetch
99 16.53.48 SQL Stmt: ORACLE - Fetch
100 16.53.48 SQL Stmt: ORACLE - Fetch
101 16.53.48 SQL Stmt: ORACLE - Fetch
102 16.53.48 SQL Stmt: ORACLE - Fetch
103 16.53.48 SQL Stmt: ORACLE - Fetch
104 16.53.48 SQL Stmt: ORACLE - Fetch
105 16.53.48 SQL Stmt: ORACLE - Fetch
106 16.53.48 SQL Stmt: ORACLE - Fetch
107 16.53.48 SQL Stmt: ORACLE - EOF
108 16.53.48 SQL Stmt: ORACLE - Close
109 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_RAPPORTI_DIPEND' order by column_position ASC
110 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_RAPPORTI_DIPEND' order by column_position ASC
111 16.53.48 SQL Stmt: ORACLE - Fetch
112 16.53.48 SQL Stmt: ORACLE - Fetch
113 16.53.48 SQL Stmt: ORACLE - EOF
114 16.53.48 SQL Stmt: ORACLE - Close
115 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_RAPPORTI_MASTRO' order by column_position ASC
116 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name =
'IDX_RAPPORTI_MASTRO' order by column_position ASC
117 16.53.48 SQL Stmt: ORACLE - Fetch
118 16.53.48 SQL Stmt: ORACLE - Fetch
119 16.53.48 SQL Stmt: ORACLE - EOF
120 16.53.48 SQL Stmt: ORACLE - Close
121 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_3'
order by column_position ASC
122 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_3'
order by column_position ASC
123 16.53.48 SQL Stmt: ORACLE - Fetch
124 16.53.48 SQL Stmt: ORACLE - Fetch
125 16.53.48 SQL Stmt: ORACLE - EOF
126 16.53.48 SQL Stmt: ORACLE - Close
127 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_4'
order by column_position ASC
128 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_4'
order by column_position ASC
129 16.53.48 SQL Stmt: ORACLE - Fetch
130 16.53.48 SQL Stmt: ORACLE - Fetch
131 16.53.48 SQL Stmt: ORACLE - EOF
132 16.53.48 SQL Stmt: ORACLE - Close
133 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_5'
order by column_position ASC
134 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_5'
order by column_position ASC
135 16.53.48 SQL Stmt: ORACLE - Fetch
136 16.53.48 SQL Stmt: ORACLE - Fetch
137 16.53.48 SQL Stmt: ORACLE - EOF
138 16.53.48 SQL Stmt: ORACLE - Close
139 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_6'
order by column_position ASC
140 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_6'
order by column_position ASC
141 16.53.48 SQL Stmt: ORACLE - Fetch
142 16.53.48 SQL Stmt: ORACLE - Fetch
143 16.53.48 SQL Stmt: ORACLE - EOF
144 16.53.48 SQL Stmt: ORACLE - Close
145 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_7'
order by column_position ASC
146 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'INDICE_7'
order by column_position ASC
147 16.53.48 SQL Stmt: ORACLE - Fetch
148 16.53.48 SQL Stmt: ORACLE - Fetch
149 16.53.48 SQL Stmt: ORACLE - Fetch
150 16.53.48 SQL Stmt: ORACLE - EOF
151 16.53.48 SQL Stmt: ORACLE - Close
152 16.53.48 SQL Prepare: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'SYS_C003302'
order by column_position ASC
153 16.53.48 SQL Execute: ORACLE - select column_name from
sys.all_ind_columns where index_owner = 'WCM' and index_name = 'SYS_C003302'
order by column_position ASC
154 16.53.48 SQL Stmt: ORACLE - Fetch
155 16.53.48 SQL Stmt: ORACLE - Fetch
156 16.53.48 SQL Stmt: ORACLE - Fetch
157 16.53.48 SQL Stmt: ORACLE - Fetch
158 16.53.48 SQL Stmt: ORACLE - EOF
159 16.53.48 SQL Stmt: ORACLE - Close
160 16.53.48 SQL Prepare: ORACLE - select b.column_name from
sys.all_constraints a ,sys.all_cons_columns b where a.owner = 'WCM' and
a.constraint_name = 'SYS_C003650' and a.r_owner = b.owner and
a.r_constraint_name = b.constraint_name order by position ASC
161 16.53.48 SQL Execute: ORACLE - select b.column_name from
sys.all_constraints a ,sys.all_cons_columns b where a.owner = 'WCM' and
a.constraint_name = 'SYS_C003650' and a.r_owner = b.owner and
a.r_constraint_name = b.constraint_name order by position ASC
162 16.53.48 SQL Stmt: ORACLE - Fetch
163 16.53.48 SQL Stmt: ORACLE - Fetch
164 16.53.48 SQL Stmt: ORACLE - Fetch
165 16.53.48 SQL Stmt: ORACLE - Fetch
166 16.53.48 SQL Stmt: ORACLE - EOF
167 16.53.48 SQL Stmt: ORACLE - Close

Thanks
Roger Venad

George Christoforakis · Guest

What information do you currently miss?
There are some API function that return the tablename, field and indexes of
the database (i don't know if more exist).
But there are not much faster than what you did because I tried both. What
you did on oracle I had to do it on mssql 2000. I prefered this way because
I had more control on it. Basically getting the metadata in this way is
safer in case you wish to change components that do not support the apis.

George Christoforakis.

"Roger Venad" <roger_venad (AT) hotmail (DOT) com> wrote

Roger Venad · Guest

Well the informations that I currently miss is for Referential Integrity in
Oracle. To get information of the referenced table, fields etc. Because in
my components that were done with BDE I used the API of BDE DbiOpenRIntList
to control on delete, insert and modify of a row in a table according to the
result returned by the API. With the select I had send before I do not get
informations of the constraints with constraint type = 'P'.
Thanks in advance.
Roger Venad

"George Christoforakis" <nospam (AT) nospam (DOT) info> wrote

George Christoforakis · Guest

It is best if you post a question on database.sqlservers newsgroups and ask
how to retrieve the constraint type P.
I think you will find more answers there as it is a general question on
sqlservers and not on dbexpress.

good luck,
George Christoforakis.

"Roger Venad" <roger_venad (AT) hotmail (DOT) com> wrote