Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
abeebe

Member
Registered:
Posts: 29
Reply with quote  #1 
Hello, I am trying to open a recordset and populate it with a SQL script that is being defined earlier in the script.  The code below is what I am currently doing, but it doesn't seem to be populating the recordset:

    Set r = ework.CreateObject("ADODB.Recordset")
        r.CursorType = 3                    'Static cursor.
        r.LockType = 2                      'Pessimistic Lock.
        r.Open ework.SelectSQL(s)

s is defined earlier as a simple SQL script that returns two fields with about 67 rows.  When I try to look at the fields and even try to write this to a log file, the log file does not get written and the values for each field is blank.

Any ideas on a quick solution?

Also, I am using v7.6.4.8

Thanks

Alan
0
jwoodhull

Member
Registered:
Posts: 10
Reply with quote  #2 
Alan,

Here is a sterilized excerpt showing how we have handled connections to the ework database inside VBScript before:

'This sub creates email text.
sub vbCreateEmail

dim dbEwork
dim rsEwork
dim sql
Dim WshShell=ework.CreateObject("WScript.Shell")
Dim bLog = true
Dim resultString
if bLog then
WshShell.LogEvent 0, "Starting vbCreateEmail"
end if

Set dbEwork = ework.CreateObject("ADODB.Connection")
Set rsEwork = ework.CreateObject("ADODB.Recordset")
dbEwork.ConnectionString = "DSN=EWork; UID=;PWD="
dbEwork.Open


sql = "SELECT foo, bar from fubar WHERE (FolderID = '" & ework.FolderID & "')"
Set rsEwork = dbEwork.Execute(sql)
if NOT (rsEwork.BOF and rsEwork.EOF) then
resultString = rsEwork("foo") & " " & rsEwork("bar")
end if
rsEwork.Close
dbEwork.Close
'...
end sub



Also note the LogEvent call using WshShell.  If your log file isn't working, this is an alternative for adding debugging messages.  Hope this helps.

-John
0
abeebe

Member
Registered:
Posts: 29
Reply with quote  #3 
Hello jwoodhull:

Thanks for the return on this.

So, I have one question, I noticed that you use the connection string, but leave out the user id and password.  What if we have a different dsn between environments?  I would like to just do a simple query and put it into a recordset, but it seems Metastorm doesn't allow you to have a connectionless recordset.  I am doing just a single query and don't need to write any data back to the database, so I was hoping to just fill in the recordset and use the data.

Thanks again and hopefully this helps some.

Alan
0
abeebe

Member
Registered:
Posts: 29
Reply with quote  #4 
Ok everyone, may have found a solution since Metastorm does not allow a connectionless recordset to be created/populated.  Instead of hard coding in the DSN information, you can use a registry key lookup to get the information.

The first part is where you do the connection string and you basically have to put in the registry key information.  Now, since the key has in some extra data, we are removing this with the replace function.  Also, make sure to get the actual registry key. Ours is on a 64-bit box, so that is why the "Wow6432Node" part is in the call to the function.

The second part is the function that is used to pull the data from the registry.

Hopefully this will help you all out there who need to build a recordset in VBScript and also keep out hard-coded usernames and passwords in the database.  This will allow the DBAs to change the database password occasionally and only have to update it in one place in the registry of the server.

I can't take full credit for this since my boss helped me with this quite a bit.

Alan

con1.connectionstring=Replace(readFromRegistry("HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Metastorm\e-work\Engine\Database\CONNECTION"),"ODBC;", "")

function readFromRegistry (strRegistryKey)
    Dim WSHShell, value

    On Error Resume Next
    Set WSHShell = CreateObject("WScript.Shell")
    value = WSHShell.RegRead( strRegistryKey )

    if err.number <> 0 then
        readFromRegistry= ""
    else
        readFromRegistry=value
    end if

    set WSHShell = nothing
end function

0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!