Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Wed Apr 26, 2017 8:51 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 11 posts ] 
Author Message
 Post subject: SQL - determine Table name from multi-table SQL Statement
PostPosted: Tue Apr 23, 2013 11:30 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
Here's an SQL statement (I've split and spaced for readability):
SELECT
tbl_Contacts.Contact_ID, tbl_Contacts.Last_Name, tbl_Contacts.First_Name,
tbl_Contacts.Middle_Name, tbl_Contacts.DateOfBirth, tbl_Contacts.Gender,
tbl_Contacts.Starting_Balance, tbl_Contacts.IsCriminal, tbl_Contacts.Details,
tbl_Contacts.Colour_ID,

tbl_Colours.Colour_ID, tbl_Colours.Colour_Name, tbl_Colours.Colour_Area_ID

FROM (tbl_Contacts INNER JOIN tbl_Colours ON tbl_Contacts.Colour_ID = tbl_Colours.Colour_ID)

WHERE tbl_Contacts.Colour_ID = 1 AND tbl_Contacts.Contact_ID = 5;

When I extract the field names via a Recordset.IdxField(i).name loop, the field names are as follows:
Field 1 : Contact_ID
Field 2 : Last_Name
Field 3 : First_Name
Field 4 : Middle_Name
Field 5 : DateOfBirth
Field 6 : Gender
Field 7 : Starting_Balance
Field 8 : IsCriminal
Field 9 : Details
Field 10 : Colour_ID
Field 11 : Colour_ID
Field 12 : Colour_Name
Field 13 : Colour_Area_ID


As you can see, fields 10 & 11 have the same field name "Colour_ID". Is there any way to retrieve the table name from the above SQL statement for a particular field?

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Wed Apr 24, 2013 12:12 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
I'm amazed that the SQL did not fail with an "ambiguous column name" error.
But that not withstanding....

There are two ways.... One I consider the "right" way and the other is "another way" :)

The right way is to rename the second occurance. This removes the possibilty of the above error, and gives proper access to both occurrances (assuming you really even need to keep them both [doesn't the INNER JOIN link equal values???]...

tbl_Colours.Colour_ID as Colour_ID2


The "other way" is to reference the field by index instead of by name

rs.field(11)


but I'm betting you could just leave it out of your select statement and not have to worry about it... just keep ONE

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Wed Apr 24, 2013 1:28 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
Thanks Dave. Yes, the query works as is and I believe it does as the table name is explicitly used. I thought by explicitly including the table name that I could also retrieve that table name later.

In the example provided above, yes, the data would be the same as it's the linking field but still I couldn't extract the correct table for given field name. This may be a problem for me when dealing with same field names across multiple tables that are not linked or related (ie. notes, details, audit time stamp etc. that type of data).

At present I'm in the process of writing a class with functions that handles all the SQL creation routines and related stuff. At the window level I merely pass in the relevant table(s), Keyfield(s), Join data, Where etc. On the window, relevant controls (sub-classed TextField etc.) have properties (Table name, Field name) which are the controls to receive the recordset data. My idea is to simply cycle through these controls, gather the relevant data from the recordset (from the class), the matching data being Table & Field names.


[Thinking Aloud]
I might have to reconsider my approach but I didn't want to hardcode SQL statements at the window level, though having said that, if controls have to be created for the window anyway, that's a form of hardcoding, if that makes sense.

I might look at the SQL creation routine again. If using an alias (which I was trying to avoid) I'm thinking that the field alias name could be the field name prepended with an "n_" . Left/First table = 1_, Second table = 2_ etc.
[/Thinking Aloud]

_________________
Steve
rs2012 r2.1 Windows 7.


Last edited by superjacent on Wed Apr 24, 2013 7:59 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Wed Apr 24, 2013 7:46 am 
Offline
User avatar

Joined: Fri Nov 10, 2006 4:10 pm
Posts: 1855
Location: Michigan, USA
Greetings!

I'm not sure how useful this may or may not be, but I go about this a bit differently:

(I'll refer the CRUD (Create Read Update Delete) model.)

I have two class types when referring to databases: tables and views. I use Tables for Creating, Updating, and Deleting. Reading, on the other hand, I use views. Queries are definitely the most used aspect of databases, and are almost always more complex than "Select * from mytable". Views are a great way to contain complex queries, give another layer for versioning, and adjust names to avoid conflicts. (The versioning benefit comes about that if you change the table, you can keep the view and adjust it to support old queries from the new table structure.)

So in your situation, I would just create a view that fits exactly what I'm looking for, and use that for your custom controls.

Hope this helps! :)

_________________
Windows 8 x64
Windows XP Pro SP3
Ubuntu 11.04 via Virtual Box
RS Enterprise 2012r1.1

Programming Tutorials & Free Projects: http://www.JasonTheAdams.com
"Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton


Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Wed Apr 24, 2013 10:10 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
Thanks Jason, your suggestions make sense (it's the same approach I took for an Access database that I'm trying to convert the front-end to RS ---- Xojo). Having said that, as I review the Access code, the code is very repetitive (written many years ago) and I cringe a little knowing how much more efficient and generic the code could be. I'm not prepared to spend the time improving the access code , I'd rather start afresh with RS. ("if it ain't broke don't fix it" - that pops into my head)

I'm at the point of fine-tuning the SQL (viewing) process. There are a heap of windows and I am attempting not to specifically create the SQL field list at the window level. At the window level I pass in the table names and only specifically reference keyfields, JOIN, ORDER, WHERE components etc. Knowing the table name the extraction of the field names are possible and therefore I thought superfluous if specifically passed in at the window level. So, if I can't clearly delineate a field from a particular multi-table SQL statement I think I might have to modify my approach and simply - hardcode - the fields required at the window level and also pass them into the Class SQL creation functions.

The editing, adding of data is a piece of cake, in my Access database it is not possible to directly edit/add from the viewing screen, click a button and open up a specific form (window) in modal mode - do the edit/add and return to the calling form. I will be following this same approach.

If I've waffled on, I apologise. I've sort of come to a halt as I'm undecided on what approach to take regarding the SQL creation process - regarding the fields - hardcode it at the window level or not.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Thu Apr 25, 2013 12:33 am 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
Since you're creating the sql field list in code, it doesn't matter how verbose it gets, so concatenate the table and field names for the alias, rather than use a number. That way, you can do the same when you loop through the controls to populate them.

select tbl_Contacts.Contact_ID as tbl_Contacts_Contact_ID, ...


Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Thu Apr 25, 2013 1:10 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
timhare wrote:
Since you're creating the sql field list in code, it doesn't matter how verbose it gets, so concatenate the table and field names for the alias, rather than use a number. That way, you can do the same when you loop through the controls to populate them.

select tbl_Contacts.Contact_ID as tbl_Contacts_Contact_ID, ...


Thanks Tim. This makes sense and achieves what I want to achieve without too much extra effort required. This approach is what I'm leaning towards.

I'm assuming then that it is in fact impossible to directly extract the table name of a field from a multi-table SQL statement.

<Thinking Aloud Again>
From a theoretical point of view I suppose it is possible to extract the table name of a given field from a multi-table SQL statement but requires a number of steps. I noticed that the returned field names are in order of the original SQL field list and therefore a string array could be maintained where each element would be the table name of the corresponding field. Therefore when scanning controls it's a matter of checking against the array to determine the correct associated table name. Knowing this, the correct Field index number of the recordset is used to retrieve and populate the control.
</Think Aloud Again>

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Thu Apr 25, 2013 2:13 am 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
superjacent wrote:
I'm assuming then that it is in fact impossible to directly extract the table name of a field from a multi-table SQL statement.

Correct. If you run the sql in a query browser, you'll notice that the columns shown do not have table names either.

Quote:
<Thinking Aloud Again>
From a theoretical point of view I suppose it is possible to extract the table name of a given field from a multi-table SQL statement but requires a number of steps. I noticed that the returned field names are in order of the original SQL field list and therefore a string array could be maintained where each element would be the table name of the corresponding field. Therefore when scanning controls it's a matter of checking against the array to determine the correct associated table name. Knowing this, the correct Field index number of the recordset is used to retrieve and populate the control.
</Think Aloud Again>

Yes, the columns are returned in the same order as the fields listed in the sql. So assuming that you traverse the controls in the same order, you could use idxfield to retrieve the value. I personally would use the alias as there's no possibility of error.


Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Thu Apr 25, 2013 2:39 am 
Offline

Joined: Mon May 30, 2011 12:56 am
Posts: 702
In practice, I find that in these cases, Access tends to noble the field name in the resulting output.
You might get one of them as Expr1, or you might find the file names are actually
tbl_Contacts_Colour_ID and tbl_Colours_Colour_ID


BUT:

Since you are joining the tables on Color_ID, they by definition have to both be the same value.

tbl_Contacts.Colour_ID = tbl_Colours.Colour_ID


Therefore in this case, you dont even need to select both columns.
Omit one of them and all ambiguity goes away.


Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Thu Apr 25, 2013 4:06 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
kermit wrote:
Since you are joining the tables on Color_ID, they by definition have to both be the same value.
tbl_Contacts.Colour_ID = tbl_Colours.Colour_ID


Already addressed.
superjacent wrote:
In the example provided above, yes, the data would be the same as it's the linking field but still I couldn't extract the correct table for given field name. This may be a problem for me when dealing with same field names across multiple tables that are not linked or related (ie. notes, details, audit time stamp etc. that type of data).

kermit wrote:
Therefore in this case, you dont even need to select both columns.
Omit one of them and all ambiguity goes away.

You are right regarding the ambiguity but I think you're missing my point. I don't want to or preferring not to, for every window, hardcode the field list; knowing the tables required should be sufficient. Yes, if hardcoding the field list then only the absolute fields required for the window would be included. If generically coding the field list some windows will not display all the fields, I can live with that.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
 Post subject: Re: SQL - determine Table name from multi-table SQL Statemen
PostPosted: Thu Apr 25, 2013 6:59 am 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
timhare wrote:
Since you're creating the sql field list in code, it doesn't matter how verbose it gets, so concatenate the table and field names for the alias, rather than use a number. That way, you can do the same when you loop through the controls to populate them.

select tbl_Contacts.Contact_ID as tbl_Contacts_Contact_ID, ...


I have now coded for this and currently testing. The re-code only took a couple of minutes, only had to add to an existing line the string manipulation for the 'AS' clause.

Throughout testing it became obvious that only alpha-numeric characters were allowed. Couldn't use a dot notation, or ! or $ etc. In the end had to settle on 3 underscores as I extensively use single underscores in table and field names and could foresee future searches being problematic (which underscore is the delimiter). I definitely do not use 2 or more underscores together, so 3 of them should suffice.

_________________
Steve
rs2012 r2.1 Windows 7.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: Bing [Bot] and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group