Trying to link to ODBC Foxpro using javascript 1

[thero]

Up until 3 days ago I'd never used Lotus Notes and I've been given the task of linking form fields in Domino to our company database (which is Foxpro based) using ODBC.

What I need and can't find is an example code of someone doing something like this, as well as the javascript objects to make SQL calls to ODBC.

Any advice/tips/URL's with relevant info would also be much appreciated.

Thanks,
Roshan

 

[gamesman]

DO you need to manipulate the data, or just view it?

 

[thero]

At the moment my brief is to simply search through and display select data although the next phase is to allow limited editing of the source database.

 

[gamesman]

You'll need to the foxpro to your odbc ini and then you can utilize notes commands @dblookup, @dbcolumn and for actual commands @dbcommand. Since you'll be using foxpro as your rdbs you'll not get much function out of column, lookup will create table like views, bu tneither of these allow any manipulation.

@dbcolumn("ODBC":"NoCache";data-source;user_id1:user_id2;password1assword2;table;column;null_handling;"DISTINCT":sort)

@dblookup("ODBC":"NoCache";data-source;user_id1:user_id2;password1assword2;table;column;null_handling;key_column;key;"DISTINCT":sort)


@DbCommand("ODBC":"NoCache";data-source;user_id1:user_id2;password1assword2;command_string:null-handling)

ODBC is required to let notes you are using an ODBC source.

NoCache is optional. The information is cached when gotten. NoCache forces notes to get the information from the source without check the cache. This increases time and utilization but if your data is constantly changing you'll want to use it.

data-source is in this case your foxpro db.

User_id and passwords are only required if the db requires them. Notes can support 2. I generally use null formatting. ie "":"". This way no one see's the id/password. It works either to tell notes that no password is needed or to prompt the user for his/her id/password. This won't work in an agent if a password is required.

DISTINCT, sort, Table and column are pretty self evident. Notes will support owner.table definitions as well as views.
Use with key_column and key to have notes create a select statement in the format of SELECT column WHERE key_column = key. You can use colons to seperate key values. this works as an 'or' statement. You'll have to use @DbCommand for ands.

For null handling ues either "Fail" which generates an error message and returns no data; or "Discard" which will remove null values (and tell you that it did it) or "replacement value" the value itself not the term. Any sorting you request will take place before the replacement. Notes will tell you that it replaced nulls. Also if replacement value must b eof the same type or convertable to teh same type of data that's being retirieved.

DbCommand's "command string" is your friend. You can use either SQL (acceptable foxpro syntax, nots doesn't do conversion THIS GOES FOR DATE FORMATTING AS WELL) or a command that foxpro will recognize or a stored procedure you have created in your db.

Double quotes around everything. Only NoCache is optional. blank quotes fro any entries that don't exist ie uesr_id or key.

Good luck

 

[thero]

Thanks gamesman - I didn't know of the existence of @DbCommand and it seems to be working in bringing the data into my form.

My problem now is trying to search this field using a search key entered by a user. If anyone has any suggestions they'd again be much appreciated.

 

[gamesman]

For a search key you can use the @dbLookup with it's "key" command and search against the lookup you've already done.

Or; have the users use dblookup first to filter on the key and then use dbcommand to manipulate the data set.