Solved

Keyword field on the web that takes his keywords with ODBC

Posted on 2002-05-16
27
287 Views
Last Modified: 2013-12-18
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
Comment
Question by:koray_uygun
  • 11
  • 6
  • 5
  • +2
27 Comments
 
LVL 5

Expert Comment

by:snocross
ID: 7013849
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
 
LVL 5

Expert Comment

by:snocross
ID: 7013866
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
 
LVL 5

Expert Comment

by:snocross
ID: 7013876
Next my keyword field will have a reference to the lookup database:

@DbColumn( "" : "NoCache" ; "MyLookupDatabase" ; "MyDirectory\\MyFileName" ;1 )
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7013923
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
 
LVL 5

Expert Comment

by:snocross
ID: 7013947
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
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7015704
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
 

Author Comment

by:koray_uygun
ID: 7015712
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
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7015726
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
 

Author Comment

by:koray_uygun
ID: 7015729
dbcommand works fine with the Notes client.
0
 

Author Comment

by:koray_uygun
ID: 7015751
Thank youjerrith, now i can run agents on the server. but the agent doesn't work ;-)
0
 

Author Comment

by:koray_uygun
ID: 7015758
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
 

Author Comment

by:koray_uygun
ID: 7015772
Hementh, the dbcommand works fine on the notes client but not on the web, do you have an idea why?
0
 

Author Comment

by:koray_uygun
ID: 7015795
No more help?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7015797
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
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7015800
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
 

Author Comment

by:koray_uygun
ID: 7015855
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
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7015880
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
 

Author Comment

by:koray_uygun
ID: 7016032
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
 
LVL 5

Expert Comment

by:snocross
ID: 7016037
Have you defined the ODBC source on the web server?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7016038
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
 

Author Comment

by:koray_uygun
ID: 7016054
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
 

Author Comment

by:koray_uygun
ID: 7016065
The error message is operation time out (on the web)
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7016094
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
 
LVL 10

Accepted Solution

by:
zvonko earned 200 total points
ID: 7016240
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
 
LVL 10

Expert Comment

by:zvonko
ID: 7039114
Hello Koray,

it is getting quiet here :-)

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

0
 

Author Comment

by:koray_uygun
ID: 7039547
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
 
LVL 10

Expert Comment

by:zvonko
ID: 7039875
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now