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.
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.
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("MyDataSourc e1") 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")
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("MyDataSourc
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 )
@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";"PUBLISH ERS";"dbo" :"";"vanil la":"";
"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
@DbCommand( "ODBC" : "NoCache" ; data_source ; user_ID1 : user_ID2 ; password1 : password2 ; command_string : null_handling )
eg:
@DbCommand("ODBC";"PUBLISH
"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)
Welcome back to EE sno, long time no see... (I benefited from your abscence to overtake you in top-15)
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?
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
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
ASKER
dbcommand works fine with the Notes client.
ASKER
Thank youjerrith, now i can run agents on the server. but the agent doesn't work ;-)
ASKER
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??
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??
ASKER
Hementh, the dbcommand works fine on the notes client but not on the web, do you have an idea why?
ASKER
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.S erver, 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)
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.S
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?
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?
ASKER
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... :-(
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?
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?
ASKER
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";"Temptat ion";"";"" ;"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.
@DbCommand("ODBC";"Temptat
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
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
ASKER
Yes snocross.
Hemanth, this dbcolumn doesn't work :
@DbColumn( "ODBC" : "NoCache" ; "Temptation" ; "" ;"" ;"AGENTS" ;"MATRI":"Discard";"Ascend ing" )
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.
Hemanth, this dbcolumn doesn't work :
@DbColumn( "ODBC" : "NoCache" ; "Temptation" ; "" ;"" ;"AGENTS" ;"MATRI":"Discard";"Ascend
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.
ASKER
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).
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello Koray,
it is getting quiet here :-)
Did you succeed with this?
Do you need some support?
it is getting quiet here :-)
Did you succeed with this?
Do you need some support?
ASKER
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.
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
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