• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1609
  • Last Modified:

VBScript data from Pervasive V8.6

I got this code from Windows Scripting Solutions.  I've modified it to what I think my database needs.  What am I missing?  The code was originally written to access MSSQL data but I am hitting a V8.6 engine.  Do I need the Integrated Security=SSPI line?

' Created by.: Arthur Zubarev
' Purpose....: Extract fields and records from a table of your choice and display in your default browser
' Note.......: The scripts is built around the use of the native driver for MS SQL Server,
'              but it is not limited to query other sources, integrated security used.
OPTION EXPLICIT

Dim DataSrc 'Holds the name of the database server
Dim DBName 'Holds the database name
Dim sTable 'Holds the table name to query

'** Setting database connection parameters **
DataSrc = "nash-max"
DBName = "MAXSAM"
sTable = "Product Structure"

'Query table
Call QueryTable(sTable)

Function QueryTable(sTable)

On Error Resume Next

   Dim Conn 'As ADODB.Connection
   Dim Rs 'As ADODB.Recordset
   Dim f 'As Recordser field
   Dim sConnect 'As String
   Dim sResult 'As string

   'Construct the connection string
   'sConnect= "Provider=sqloledb;" & _
   sConnect= "Provider=pervasiveoledb;" & _
             "Data Source=" & DataSrc & ";" & _
             "Initial Catalog=" & DBName & ";" & _
             "Integrated Security=SSPI"
 
  ' Establish the connection.
   Set Conn = CreateObject("ADODB.Connection")
   Conn.ConnectionString = sConnect
   Conn.Open
 
   'If the connection can be opened proceed, otherwise throw an exception
   If err.number <> 0 Then
 MsgBox "Cannot open database connection to " & DataSrc, vbOKOnly, "Connection Error"
 Stop
   End if
 
   'Get fields and their values
   Set Rs = CreateObject("ADODB.recordset")
 
   Rs.Open "Select top 10 * From " & sTable, Conn
 
   'If the query cannot be processed throw an exception
   If err.number <> 0 Then
 MsgBox "Cannot process query", vbOKOnly, "SQL Error"
 Stop
   End if
 
   If Rs is Nothing Then SResult = "No data was retrieved." & vbCrLf
 
  Do Until Rs.EOF
    For Each f in Rs.Fields
     SResult = SResult & TRIM(f.name) & " &nbsp;" & vbTab & TRIM(f.value) & vbCrLf
    Next
    SResult = sResult & vbCrLf
   Rs.MoveNext
  Loop
 
   'Closing
   Rs.Close
   Conn.Close
 
   'Cleanup
   Set Rs = Nothing
   Set Conn = Nothing
 
   'Write to an HTML file and show
   Call WriteAndDispaly(sResult)
 
   End Function
 
   Function WriteAndDispaly(sText)
 dim filesys, filehtml
 
 'Open file
 Set filesys = CreateObject("Scripting.FileSystemObject")
 Set filehtml = filesys.CreateTextFile("c:\QryResults.html", True)
 
 filehtml.WriteLine("<html>")
 filehtml.WriteLine("<head>")
 filehtml.WriteLine("</head>")
 filehtml.WriteLine("<body>")
 filehtml.WriteLine("<PRE><H4>")
 filehtml.WriteLine("Extract from table: " & sTable & vbCrLf )
 filehtml.WriteLine(sText)
 filehtml.WriteLine("</H4></PRE>")
 filehtml.WriteLine("</body>")
 filehtml.WriteLine("</html> ")
 
 'Close file
 filehtml.Close
 
 'Launch it
 Dim WshShell, strCmd
 Set WshShell = CreateObject("WScript.Shell")
 strCmd = "c:\QryResults.html"
 WshShell.Run strCmd
   End Function
   'No more code exists below this line



TIA,

Tim
0
gibneyt
Asked:
gibneyt
  • 4
  • 4
1 Solution
 
mirtheilCommented:
What error/behavior are you seeing?
I do see that your connection string is a little off.  You have:
sConnect= "Provider=pervasiveoledb;" & _
             "Data Source=" & DataSrc & ";" & _
             "Initial Catalog=" & DBName & ";" & _
             "Integrated Security=SSPI"
It should be:
sConnect= "Provider=pervasiveoledb;" & _
             "Data Source=" & DataSrc & ";" & _
             "Location=" & SerrverName & ";"
You could also use ODBC with a connection string like:
sConnect= "DSN=PSQLDSNName"
0
 
Bill BachPresidentCommented:
Mirtheil's correction should do it for you.  

For full ODBC connection string information, your best bet is to look at the online help manuals and search for "ODBC Connection String".  It has information on other options that you may find helpful, such as UID and PWD.
0
 
gibneytAuthor Commented:
The error returned is:

Connection error                                                              X
Cannot open database connection to nash-max

                                    OK

And then the second MsgBox error follows too.

Are you listing the server's name twice?  Why?

Tim

0
Independent Software Vendors: 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!

 
mirtheilCommented:
I'm sorry, I didn't realise that you were specifying the server name as the Data Source parameter.
It should be:
sConnect= "Provider=pervasiveoledb;" & _
             "Data Source=" & DBName & ";" & _
             "Location=" & SerrverName & ";"
0
 
gibneytAuthor Commented:
I would like to use Pervasives calls rather than ODBC.
0
 
gibneytAuthor Commented:
OK.

This gets the connection made:

   sConnect= "Provider=pervasiveoledb;" & _
             "Data Source=" & DBName & ";" & _
             "Location=" & DataSrc & ";"

Then i get the SQL Error MsgBox

I'll play and get back to you.

One thing that keeps happening.  After I get the two errors the process wscript.exe goes to 100% and does not close cleanly.  I think that if the script runs correctly wscript will probably go away but what can be done to have it exit cleanly upon the error=true?

Tim
0
 
mirtheilCommented:
Why?  There's no performance gain by using OLEDB instead of ODBC.  That being said, Setting the Data Source to the Pervasive DBName and the Location to the server name should address the connection issue.
0
 
mirtheilCommented:
Your SQL error is because of the Table name.  If you've got a table name with spaces, it needs to be enclosed in double quotes:

   Rs.Open "Select top 10 * From " & chr(34) sTable & chr(34), Conn
0
 
gibneytAuthor Commented:
Thanks mirtheil.

Gotta love ASCII!

Tim
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now