Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Lotusscript problem with ODBC and SQL Server

Posted on 2003-03-12
15
Medium Priority
?
2,498 Views
Last Modified: 2013-12-18
Hi guys, I'm having a big problem trying to WRITE a record from a notes database to an SQL database using ODBC.  The problem is in the line where I call res.UpdateRow I get the following error:

"Connection is busy with results for another hstmt"



Sub Initialize
     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 x As Variant
     Dim ds As String
     Dim user As String
     Dim pw As String
     
     Counter = 0
     con.SilentMode = True
     
     Print("Agent started")
     
     Set db = New NotesDatabase("", "payreqst.nsf")
     Set view = db.GetView("System\Unpaid Requests")
     Set doc = view.GetFirstDocument
     
'set odbc variables
     ds = "MyServer"
     user = "MyUserName"
     pw = "MyPassword"    
     
'  Connect to BusinessServer1
     If Con.ConnectTo(ds, user, pw) Then
          Set Qry.Connection = Con
         
'  Select all the records in order to create the results set
         
          qry.SQL = "SELECT * FROM DealTest"
          Set Res.Query = Qry
          If Not Res.Execute() Then
               Print("Error: " & res.GetErrorMessage(DB_LASTERROR))
          End If

          Do Until doc Is Nothing
               x = Ucase$(doc.DN(0))
               doc.DN = x
               Call doc.save(True,True)
               
               Call res.Addrow()
               
               Call res.SetValue(1,  Left$(doc.DN(0),10))
               
               If Not res.UpdateRow Then
                    Messagebox res.GetExtendedErrorMessage,, res.GetErrorMessage
                    con.Disconnect
                    Exit Sub
               End If
'                              
               Set doc = view.GetNextDocument( doc )
               
          Loop
0
Comment
Question by:snocross
  • 9
  • 4
  • 2
15 Comments
 
LVL 5

Author Comment

by:snocross
ID: 8120418
By the way the following line is really as follows:  
ds = "BusinessServer1"

I was just changing passwords and stuff so you wouldn't see.
0
 
LVL 5

Author Comment

by:snocross
ID: 8120427
Also an important note:  The code works fine when I am just reading from the ODBC source.  The problem only occurs when doing the UpdateRow.
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 500 total points
ID: 8120905
Try this after res.Execute statement

res.LastRow
res.FirstRow

Then start updating rows.

~Hemanth
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:AndrewJayPollack
ID: 8121160
ick.

First, stop using lsxodbc, you need to move to using nlsxlc.dll instead.  Sample code is on my website.

The problem is that you're locking the connection with your sql read, or with a previous sql read session.  

the row is locked.  try setting writeback to false (should be a property of the connection).

Also, reboot your workstation, it may have a cached session open.
0
 
LVL 5

Author Comment

by:snocross
ID: 8121720
Also an important note:  The code works fine when I am just reading from the ODBC source.  The problem only occurs when doing the UpdateRow.
0
 
LVL 5

Author Comment

by:snocross
ID: 8121871
Son of a.... didn't mean to repost... ok I will try some of your ideas right now.
0
 
LVL 5

Author Comment

by:snocross
ID: 8121990
Heman it looks like your idea worked!!  Drew, I checked for the writeback property but didn't see it...
0
 
LVL 5

Expert Comment

by:AndrewJayPollack
ID: 8122072
Sno, moving the cursor to get around a bug is hokey at best. you're going to run into a ton more bugs with that old odbc lsx.  you need to move to the connectors.
0
 
LVL 5

Author Comment

by:snocross
ID: 8122073
I do not understand WHY it worked but it worked!!
0
 
LVL 5

Author Comment

by:snocross
ID: 8122559
Man, you are scaring me Drew!  I finally got it to work and I'm under a time crunch but now you think I should switch how I do it?
0
 
LVL 5

Expert Comment

by:AndrewJayPollack
ID: 8122588
I wouldn't roll anything new into production using lsxodbc.  I was buggy when it was still the only game in town, and is no longer rev'd.  Its kept in only for backward compatiblity.

As I said, there is a sample db on my system that reads odbc.  it includes (free) a connect() function, an "executesql()" function, and a complex resultloop that does way more than you'd want.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 8139413
The results has to be cached in order to set any additions or updates to the resultset. My method does that, it is  not documented.

0
 
LVL 5

Author Comment

by:snocross
ID: 8152666
You guys are amazing.  Thanks.
0
 
LVL 5

Author Comment

by:snocross
ID: 8195945
Hey Andrew if I switch from LSXODBC to your solution on your website will it work when scheduling the agent in a database on our AS400?  In other words, I want my notes database on the as400 to push and pull data from our SQL server.
0
 
LVL 5

Expert Comment

by:AndrewJayPollack
ID: 8196454
yeah.  The implementation for download should work under os/400 provided the right connectors are available on that machine.  I've never tried ODBC on OS/400, but I'm sure the oracle and db2 connectors should work directly.

The code on my end is set up for generic odbc, but if you switched where it says "odbc2" to "db2" it will use the db2 connector.   That's one nice thing about *lclsx.dll

:-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 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