ODBC terminology in the ORDS Account page does not match that used in Access

[jajwilson]

When a user tried to connect to an ORDS database from Access they are asked for the following inputs:

Database: Server: Username: SSL mode: Port: Password:

This differs from the terminology used in the ORDS user account page (Host, Port, ORDS Name : ODBC Name). It's bound to confuse a user at some point or another.

Could you redesign the ORDS user account page so that (besides the project column) it has columns for Server|Port|Database, and add a short note indicating that they should set SSL to 'allow' and use the username and password defined above?

n.b. I'm setting this to critical as we might as well get the whole ODBC thing done properly before building to App.

[thestoat]

I have just fixed this. Please check the text in the next build to make sure you are happy with the change.

[jajwilson]

Thanks, although I notice that my username is being listed as 'jameswilsonitoxacuk' (i.e. without an @ or any full stops) is that really correct?

[thestoat]

Yes, that is your odbc user. Some ODBC clients get confused with the presence of special characters (notably '@' but I did read about '.' too) so they are stripped out

[jajwilson]

Ah! That may explain why I couldn't establish an ODBC connection before then - I was using my email address as my username as I had done with the previous system.

Could you add a note after the username just confirming this format? I.e. add 'Please note that your ODBC username is your registered email address minus the '@' character and any dots.'

[jajwilson]

And indeed I can now establish the ODBC link from Access - Hurrah!

[thestoat]

The slight complication with adding the above note is that it isn't necessarily correct. For example, with that note, one might reasonably assume fred@pickles.com would have an user fredpicklescom. However, fre@dpickles.com would then have the same connection name, which won't be allowed. In practise this is highly unlikely to happen, of course, but it does need to be considered.

[jajwilson]

Ok - but if people can't use their email address as there username then they will have to be assigned an arbitrary but unique string? This will be hard to remember. Are there really any examples of ODBC clients that we know of that can't handle email addresses?

[thestoat]

The user isn't really arbitrary - it is based on their email address and will almost always be their email address minus special characters. Further, as users come on board they will simply use the role name they have been given - why would they expect to use their email address? The role name assigned is specified quite clearly in the users' preferences page. As for problems with special characters, there are various references such as http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html. Yes, I could probably use lots of quotes around such roles to enable the use of some of the special characters but

1. I don't see the need - as long as people know what their ODBC name is, what is the problem?
2. If Postgres is replaced by another database such as SQL, what would happen then?
3. Using special characters may be possible now - but what about in the future and can it be guaranteed to get every ODBC client working correctly?
4. Though the University uses a naming convention such as fred@uni.ox.ac.uk, other institutions (and therefore potential ORDS users) may have addresses with different characters (For example, Google allows an address such as fred+pickles@gmail.com. To have to research every email client now and in the future to ensure compliance is, in my view, fraught with potential problems.

[jajwilson]

Fine. So what's the problem with fre@dpickles.com then? I thought you were concerned that there might be a problem with two people with different email addresses ending up with the same username? If you want to rephrase my suggested sentence at all go ahead.

[thestoat]

If we took the approach that "Please note that your ODBC username is your registered email address minus the '@' character and any dots" then fred@pickles.com would become fredpicklescom. But fre@dpickles.com would also become fredpicklescom. Thus the statement isn't true. We could say: Please note that your ODBC username is based on your registered email address without any special characters ?

[jajwilson]

Ah - so you're saying that if such a situation would arise then the system would need to add other arbitrary characters, thus there's a bit more to the process of generating a username than simply stripping out @s and .s. OK. Go ahead and use your suggested statement then, although (if it's not introducing a further inaccuracy) I think that 'non alpha-numeric characters' is a preferable expression to 'special characters', as many of our users won't think there is anything particularly special about '.'

[thestoat]

Fixed

[jajwilson]

Sorry - closed without verifying!

[jajwilson]

Yep, seems to be working for newly added databases, though not 'backwards compatible'.