@DbCommand lookup to SQL data

HCL Domino Domino Forum
1

Hi,I am trying to lookup to SQL database.

I have two problems:

  1. It gets the error 'Data Access Application Layer, DbFunction failed because the data type of the requested coumn is unsupported, if looking up to columns with a datatype of ‘nvarchar’.

As an example:

@DbColumn( “ODBC” : “Nocache” ; “Siebel7QA” ; “UserName” ; “Password”; “S_APP_VIEW” ; “NAME” : “Fail” ; “Distinct”)

@Text has no affect (probably because @Text would just convert the returned value, not the column!!!)

OK, so is there a list of ‘supported data types’? or some way round it not working with nvarchar ? Most of the useful data is in format nvarchar.

  1. So, I tried a lookup to text values (maybe it can support them at least).

This is not returning an error message, but the formula is in a field (Dialogue List), and the idea is, this is the selection of the values that a user can choose from: the actual result is a keywords box with no values to select (running an identical query in the SQL Query Analyzer produces about 40 rows of data:

@DbCommand( “ODBC” : “NoCache” ; “Siebel7QA” ; “USERNAME” ; “PASSWORD”;

“SELECT COMMENTS FROM S_APPLET WHERE COMMENTS LIKE ‘AB%’”: “fail” )

Any ideas? Ta…

2

Subject: @DbCommand lookup to SQL data

With Many Thanks to David Noble:

http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/e999ce794927e7758525710e005ed597?OpenDocument

(And we see again - I should search more first :slight_smile:

At least I can answer the second part of my question.

Still not sure why they were coming up blank, but you can convert data types as part of the SQL select condition.

Which means you can convert from the SQL format called nvarchar into a format called varchar which notes appears to recognise:

So instead of the SELECT statement in my first question, use this one instead:

SELECT convert(varchar, NAME) as VarCharName FROM S_APP_VIEW WHERE NAME LIKE ‘E3M%’

Only problem now is…it cuts of some of the entries for some reason…