Link to home
Start Free TrialLog in
Avatar of koray_uygun
koray_uygun

asked on

Keyword field on the web that takes his keywords with ODBC

Hi,
i need a simple solution for:
I want to display a form on the web in which there's a keyword type field that takes it's keywords by ODBC. Can you tell me what to put where, how to design the form(i think i must launch an agent on the webqueryopen event and the agent must connect to a SQL server and must fill a hidden field in the mask and the keyword field must take his keyord with Formula from this filled hidden field) Am i right? But i can't make it work. If you think it will be easier for you to send me the notesdatabase (kuygun@msn.com) add  comment that says that you've sent me the mail and i will accept your comment as an answer.
Thanks,
Koray.
Avatar of snocross
snocross

Does the field have to pull the data immediately (realtime) using ODBC or can you have a scheduled agent that pulls the data into a notes database, then the keyword field references that database?  This is how I do it and I can give you examples if you want to try it this way.
This is my scheduled agent that populates a lookup database.

Dim session As New NotesSession
     Dim db As NotesDatabase
     Dim view As Notesview
     Dim doc As Notesdocument
     Dim con As New ODBCConnection
     Dim qry As New ODBCQuery
     Dim res As New ODBCResultSet
     Dim boxType As Long, answer As Integer
     
     Print("Started")
     Set db = session.CurrentDatabase
     Set view = db.GetView("Brokers")
     Set doc = view.GetFirstDocument          
     If Con.ConnectTo("MyDataSource1") Then
          While Not(doc Is Nothing)
               doc.Remove(True)        
               Set doc = view.GetFirstDocument
          Wend  
          Set Qry.Connection = Con
          ' qry.sql = "SELECT broker.BrokerNumber, broker.BrkrName, broker.RgnlSlsMgrCde, broker.searchtype, broker.location, broker.RgnlMgrUserID, broker.CAAName, Brkcntac.WWMLNM FROM Broker, Brkcntac WHERE Brkcntac.WWAN8 =* Broker.BrokerNumber"
          qry.sql = "select aban8, abalph, abac02, abat1, abac01, RGNLUSRID, CAANAME, wwmlnm from MyLibraryName.MyFilename left outer join MyLibraryName.MyFilename2 on aban8 = wwan8 and wwdss5 > 0 order by aban8"
         
          Set Res.Query = Qry
          Res.Execute
          While Res.IsEndOfData = False
               Res.NextRow
               Set doc = New NotesDocument(db)
               doc.from = session.Username
               doc.BrokerNumber = Res.GetValue(1)
               doc.BrokerName = Res.GetValue(2)
               doc.RSMNum = Res.GetValue(3)
               doc.ActiveStatus = Res.GetValue(4)
               doc.BLine = Res.GetValue(5)
               doc.OurRSM = Res.GetValue(6)
               doc.OurCAA = Res.GetValue(7)
               doc.BrokerContact = Res.GetValue(8)            
               Call doc.save(True,True)
          Wend              
          Call view.refresh
          Res.Close(DB_Close)
          con.DisConnect
     Else
     End If    
     Print("Finished")
Next my keyword field will have a reference to the lookup database:

@DbColumn( "" : "NoCache" ; "MyLookupDatabase" ; "MyDirectory\\MyFileName" ;1 )
You can use a simple dbcommand to get the values to the field. But there is a limit of 64K. If you feel that you will not hit that mark then, use this

@DbCommand( "ODBC" : "NoCache" ; data_source  ; user_ID1 : user_ID2 ; password1 : password2 ; command_string : null_handling  )

eg:
@DbCommand("ODBC";"PUBLISHERS";"dbo":"";"vanilla":"";  
"SELECT au_lname FROM dbo.authors WHERE contract=1 AND state='CA' ")

In case of web, since the scripts are executed at server, you have to configure the dsn at server.

Sno, it has been long since I saw you on EE. How is it going ? BTW, how is your health now ??

~Hemanth
Hello Heman, yes I figured I should check in and see what's new.  Man you are really pulling away in the number one spot.  My health is the same at this point.  I have comfortable bunk-beds in my van now and take naps at lunch!
Interesting. Must remember this one :-)
Welcome back to EE sno, long time no see... (I benefited from your abscence to overtake you in top-15)
Avatar of koray_uygun

ASKER

I am interested in taking the keyords real time. I can't make work Hemanth's dbcommand so i've created an agent with snocross's code and this agent is in the WebQueryOpen but it doesn't work neither, in noteslog it says you are not allowed to run the agent on the server.
Do you know how can i have this permission?
Yes, you need to be in the correct section in the server document.
If you don't have access to the server document in the domino directory (also known as Name & Address Book) you will have to ask your administrator to put your name in there (or better to put you in a group that is in this section).

The section to look for is on the Security tab under the section Agent Restrictions.
You need to be in the field "Run restricted LotusScript/Java agents", either with your name or by the fact that you are a member of a group that is listed in this field.

Regards,
JM
dbcommand works fine with the Notes client.
Thank youjerrith, now i can run agents on the server. but the agent doesn't work ;-)
Here's a simple agent that i can't run :
Sub Initialize
     Dim sess As New notessession
     Dim db As NotesDatabase
     Dim doc As Notesdocument
     Dim liste As Variant
     
     Set db = sess.CurrentDatabase
     Set doc=sess.documentcontext
     
     liste="koray"
     doc.ListeGenere=liste  
     
End Sub
this agent is shared and run when selected..

Why doesn't it run?
Please??
Hementh, the dbcommand works fine on the notes client but not on the web, do you have an idea why?
No more help?
Where and how is the agent called?
I see no reason why it shouldn't run. To save the changes you made you also need to save the document:
   Call doc.Save(True, False)
unless this is done during WebQuerySave, in that case the document should automatically be saved (unless there's a SaveOptions field with a value "0" on the form).

Also when the agent is run from WebQueryOpen it should run fine.

I've tested to put this code in a WebQueryOpen agent with the following properties:
Run : Manually From Agent List
On : Run once (@Commands may be used)

In the form's WebQueryOpen event :
@Command([ToolsRunMacro]; "NameOfTheAgent");

I've tested this and it works fine.

Do you get an error when running the agent? If so, which one?

A good idea to debug web agents is to create an agent log database and log debugging info to this database.

Here's how to do it:

1) Create an agent log, based on the Agent log template (for example in the folder logs and call it agents.nsf, thus the path would be logs/agents.nsf"

2) In your agent open this log and use an on error goto to log errors to the database. Below is a skeleton of how to build your agent including debugging info.

Sub Initialize
    REM --------------------------------------------------
    REM Initialize session and variables
    REM --------------------------------------------------
    Dim session as New NotesSession
    Dim agent as NotesAgent
    Dim db as NotesDatabase
    Dim agentLog as NotesLog
    Const LogFile$="logs/agents.nsf"

    REM --------------------------------------------------
    REM Initialize the agent
    REM --------------------------------------------------
    Set agentLog=New NotesLog(db.Title + " - " + agent.Name)
    Call agentLog.OpenNotesLog(db.Server, LogFile$)

    REM --------------------------------------------------
    REM Initialize error routine and create an agent
    REM started entry in the log
    REM --------------------------------------------------
    On Error Goto LogError
    Call agentLog.LogAction("Agent started at " & format$(Now, "dd/mm/yyyy"))

    REM --------------------------------------------------
    REM Start your regular code here
    REM You can write any debugging info to the agent log
    REM using the LogAction method
    REM --------------------------------------------------

    REM --------------------------------------------------
    REM Close agent and leave agent
    REM --------------------------------------------------
    Call agentLog.LogAction("Agent finished at " & format$(Now, "dd/mm/yyyy"))
    Call agentLog.Close
    Exit Sub

    REM --------------------------------------------------
    REM Error routine : logs an error and resumes on the
    REM next line
    REM --------------------------------------------------
LogError:
    Call agentLog.LogError(Err, "Initialize: " + Error$ + " in line " + Cstr(Erl))
    Resume Next
End Sub


If you use subroutines or functions to be able to have detailed debugging you need to have an error routine in the subroutine or function. Here's how to :

Sub ExampleSub
    On Error Goto LogLocalError

    rem your code here

    Exit Sub
LogLocalError:
    Call agentLog.LogError(Err, "ExampleSub: " + Error$ + " in line " + Cstr(Erl))
    Resume Next
End Sub

Function ExampleFunction
    On Error Goto LogLocalError

    rem your code here

    Exit Function
LogLocalError:
    Call agentLog.LogError(Err, "ExampleFunction: " + Error$ + " in line " + Cstr(Erl))
    Resume Next
End Function

Hope this helps you on your way to find the error.

(Sorry, took some time to type this)
A possible reason why the command won't work on web is the following:
When you perform the DbColumn lookup on the client, you can access any server/resource that is available on the client.
When you perform the DbColumn through your web client, the lookup runs on the server and thus it can only access the current server and resources available on that server.

Where is the ODBC resource located?
I am frenh, what do you mean by resource? The database i want to connect is on an other server and i have a odbc client on the notes server.

Thanks for the lof code but my problem seems to be the agent launch, it seems not to launch at all. I don't know what to do... :-(
There is the cause of the formula not working. You probably don't have access on the server to the database on the other server.
Because when you run the lookup on the server (when run from the web client) you use the server's ID and this user doens't have access to the network resource (drive or share) where the ODBC database resides.

When your agent doesn't start, check to see that you have the correct name in the calling @Command.
If the agent is set to Manually run from Agent List, it is listed with brackets around it's name, but you do not need to include those brackets to call the agent.

For example an agent called "(Open ODBC database)" would be called using the command
   @command([ToolsRunMacro]; "Open ODBC database");

Is this your problem?
We have tested and seen that the notes server can connect to the database server. A form that contains a computed field that has that formula :
@DbCommand("ODBC";"Temptation";"";"";"SELECT MATRI FROM AGENTS")
Runs on the Notes client on the server and on the notes client on a distant user. But still don't work on the web.
Have you defined the ODBC source on the web server?
Koray, Sorry that DbCommand , with odbc doesn't work on web. So try using DBColumn

syntax:
@DbColumn( "ODBC" : "NoCache" ; data_source  ; user_ID1 : user_ID2 ; password1 : password2 ; table ; column : null_handling ; "Distinct"  : sort )

For example, check designer help on this command
Yes snocross.
Hemanth, this dbcolumn doesn't work :
@DbColumn( "ODBC" : "NoCache" ; "Temptation"  ; "" ;"" ;"AGENTS" ;"MATRI":"Discard";"Ascending" )

in the value of a computed field nether in the formula of ta keyword field. We don't put login password because we are on an NT network and our connection to the database works with the nt certification.
The error message is operation time out (on the web)
The problem is that the domino server doesn't use the NT certification. The server is logged on as a system user when running as a service (which probably is the case).

That's why it can't connect to the resource. When you perform the same in a notes client (on the server or remote it doesn't matter) it will authenticate using the currently logged on user. There's the difference why it's not working on web.
This is the same reason why from the server you cannot open a file on a network share. (zvonko can probably tell if I'm right in my assumption or not).
ASKER CERTIFIED SOLUTION
Avatar of zvonko
zvonko

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello Koray,

it is getting quiet here :-)

Did you succeed with this?
Do you need some support?

Sorry it WAS an urgent question because i was giving a lesson and what i was teaching didn't work :-) so i haven't tried it again because i am on something else now but i will probably try it soon to make it work.

Thanks,
Koray.
Hello Koray,

two things:
1.) You granted points to me in spite other supported you much more :)
2.) You grated the points with the multiplication of B :(

Next time do it better!
For this time thanks for the points :)

So long,
zvonko