?
Solved

Lotusscript problem with ODBC and SQL Server

Posted on 2003-03-12
15
Medium Priority
?
2,457 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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

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!

Question has a verified solution.

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

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

777 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