Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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.
0
koray_uygun
Asked:
koray_uygun
  • 11
  • 6
  • 5
  • +2
1 Solution
 
snocrossCommented:
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.
0
 
snocrossCommented:
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")
0
 
snocrossCommented:
Next my keyword field will have a reference to the lookup database:

@DbColumn( "" : "NoCache" ; "MyLookupDatabase" ; "MyDirectory\\MyFileName" ;1 )
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
HemanthaKumarCommented:
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
0
 
snocrossCommented:
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!
0
 
Jean Marie GeeraertsApplication EngineerCommented:
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)
0
 
koray_uygunAuthor Commented:
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?
0
 
Jean Marie GeeraertsApplication EngineerCommented:
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
0
 
koray_uygunAuthor Commented:
dbcommand works fine with the Notes client.
0
 
koray_uygunAuthor Commented:
Thank youjerrith, now i can run agents on the server. but the agent doesn't work ;-)
0
 
koray_uygunAuthor Commented:
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??
0
 
koray_uygunAuthor Commented:
Hementh, the dbcommand works fine on the notes client but not on the web, do you have an idea why?
0
 
koray_uygunAuthor Commented:
No more help?
0
 
Jean Marie GeeraertsApplication EngineerCommented:
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)
0
 
Jean Marie GeeraertsApplication EngineerCommented:
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?
0
 
koray_uygunAuthor Commented:
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... :-(
0
 
Jean Marie GeeraertsApplication EngineerCommented:
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?
0
 
koray_uygunAuthor Commented:
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.
0
 
snocrossCommented:
Have you defined the ODBC source on the web server?
0
 
HemanthaKumarCommented:
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
0
 
koray_uygunAuthor Commented:
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.
0
 
koray_uygunAuthor Commented:
The error message is operation time out (on the web)
0
 
Jean Marie GeeraertsApplication EngineerCommented:
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).
0
 
zvonkoCommented:
thank you JM for inviting me here, but I think I can not help with this.

Domino server user name has nothing to do with ODBC user name and password; you can provide any user name as parameter for ConnectTo() method.

The problem with Domino user name running as windows service is only for file shares. Just actually is a question open with my complete answer in it (it is an one liner agent :-)
If interested then look here:
http://www.experts-exchange.com/lotusnotes/Q.20301083.html

Sorry,
zvonko

0
 
zvonkoCommented:
Hello Koray,

it is getting quiet here :-)

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

0
 
koray_uygunAuthor Commented:
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.
0
 
zvonkoCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 11
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now