ODBCResultSet - rs.getvalue("tablename.field") does not work

HCL Domino Domino Forum
1

Hi, I am using ODBCResultSet to access data from an access database. I have a company table linked to a contact table and consultant table. the contact table and consultant tables both have a field calles firstName. Why oh why oh why doesn’t rs.getvalue(“contact.firstName”) work? Please don’t tell me I have to alias each field using AS (SELECT contact.firstName AS contactFirstName), because the tables are massive and at the moment I’m using SELECT contact.*.

Any advice would be greatly appreciated as time is running out here!!!

Thanks in advance

2

Subject: ODBCResultSet - rs.getvalue(“tablename.field”) does not work

select * from contactrs.getvalue(“firstName”)

3

Subject: RE: ODBCResultSet - rs.getvalue(“tablename.field”) does not work

are you takin the p*ss or what

4

Subject: ODBCResultSet - rs.getvalue(“tablename.field”) does not work

Gwyn,

Try using query.SQL = “SELECT * FFROM Tablename Tablename”

That should let you use Resultset.Getvalue(“Tablename.Fieldname”)

5

Subject: ODBCResultSet - rs.getvalue(“tablename.field”) does not work

i obviously havent explained myself very well as you have both replied the same. appologies.

i’ve got round the problem now but let me start again cos this is a problem i hit time and time again.

i have an sql statement which reads from 3 tables as follows:

SELECT company., consultant., contact.* FROM (company LEFT JOIN consultant ON company.id = consultant.customerid) LEFT JOIN contact ON company.id = contact.customerid;

Now, my consultant and contact tables each have a field named FirstName. In other languages you would distinguish between the two when accessing the data using a resultset by using tablename.field. eg:

to access the FirstName field from contact table:

rs.getvalue(“contact.FirstName”)

or form the consultant table:

rs.getvalue(“consultant.FirstName”)

…but in Lotus Script all I get returned from the getvalue function is “False”. Which suggests that it is looking for a field called “contact.FirstName”, or “consultant.FirstName”, which isn’t the case.

It appears to me that the only way around this in LS is to alias each duplicate field name, but the draw back of this is that you are not able to use SELECT * FROM table, you have to explicitly list each and every field. Annoying!

Anyway, it doesn’t matter a great deal, it’s not a massive problem, more of a curiosity.

Thanks

6

Subject: RE: ODBCResultSet - rs.getvalue(“tablename.field”) does not work

I’m no SQL expert, far from it in fact, but I’m curious as to why you specify company., consultant. in your select.

I don’t do many Joins, but In cases like theses, I just use “SELECT * FROM library.table1 table1, library.table2 table2”

and then use table1.field in my res.getvalue.

I’m not sure if that makes a difference.