Pervasive v8 database - Data source name too long

[rccoates]

I have been trying to connect and read data from a Pervasive v8 database.

I have created a DSN which tests successfully.

Initially, I was creating a VB script to read the database and transfer the data to another database.
The connection opens successfully both using a DSN or connection string.

When the code gets to opening a recordset. I get the following error.

Run-time error -2147457259 (800004005)
[Microsoft][ODBC Driver Manager] Data source name too long

Function Retrieve_Vendor_Data()
Dim pCnn, pRst, Cnn, Rst
Set pCnn = CreateObject("ADODB.Connection")
Set pRst = CreateObject("ADODB.Connection")
Const adStateClosed = 0
Const adOpenStatic = 3
Const adUseClient = 3
Const adLockBatchOptimistic = 4
Dim bVendorExists
Dim iNextID


'Retrieve Data from Vendor Table
If pCnn.State = adStateClosed Then
'pCnn.Open "DSN=APACC;"
pCnn.Open "Provider=PervasiveOLEDB;Data Source=U:\Vol_1\ACTW\APDSN;"
End If

[red]
pRst.Open "SELECT * FROM Vendors WHERE number like '[A-Z]%' order by number", pCnn

Point of error
[/color]

Connection Strings that are returned

pCnn.Open "DSN=APACC;"

Provider=MSDASQL.1;Extended Properties="DSN=APACC;ServerName=192.168.1.112.1583;
ServerDSN=apacc;ArrayFetchOn=1;ArrayBufferSize=8;
TransportHint=TCP;DecimalSymbol=.;
ClientVersion=8.70.014.000;CodePageConvert=1252;
AutoDoubleQuote=0;"

pCnn.Open "Provider=PervasiveOLEDB;Data Source=U:\Vol_1\ACTW\APDSN;"
Provider=PervasiveOLEDB.8.70;Data Source=apacc;Location=192.168.1.112;Cache Authentication=False;Encrypt Password=False;Mask Password=False;Persist Encrypted=False;Persist Security Info=False;Impersonation Level=Anonymous;Mode=ReadWrite;Protection Level=None;Auto Translate=False;Port=1583;Pessimistic Read Lock=False;CommandOnly=False;DirectOnly=False;
LocalTCP=False




Any Ideas?

 

[mirtheil]

First off, do all of the connection strings you've posted display the error you are seeing?
Second, what happens if you change the SQL used for the pRst.Open call? Change it to "SELECT * FROM Vendors" and try it. Does it still fail?
All of the connection strings look fine.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[rccoates]

Thanks for the reply.

Yes, All the connection strings show the same error.
And the error still occurs if the SQL Statement is reduced to 'select * from vendors'

I don't know what to look at next. I have verified the database engine is the same.

 

[mirtheil]

Does this VBScript work:

Dim objConn
dim rsTemp
dim sHeaders
dim sRecords
dim filOutput
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Set filOutput = fso.CreateTextFile("class.csv", True)
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "DSN=demodata"
mySQL = "Select * from class"
set rstemp=objConn.Execute(mySQL)
if rstemp.eof then
	filOutput.Write "No records matched" & chr(13)
	filOutput.Write mySQL & chr(13) & "So cannot make table..."
	objConn.Close
	filOutput.Close
	set filOutput=nothing
	set fso=nothing
	set objConn=nothing
	Wscript.End
end if

for each whatever in rstemp.fields
      sHeaders = sHeaders & chr(34) & whatever.name & chr(34) & "," 
next
sHeaders = left(sHeaders, len(sHeaders) -1)
filOutput.WriteLine sHeaders 
DO UNTIL rstemp.eof
	for each rec in rstemp.fields
      thisfield=rec.value
      if isnull(thisfield) then
         thisfield=shownull
      end if
      if trim(thisfield)="" then
         thisfield=showblank
      end if
	  sRecords = sRecords & chr(34) &  thisfield & chr(34) & ","
	next
	sRecords = left(sRecords, len(sRecords) -1)
	filOutput.WriteLine sRecords
	rstemp.movenext
	sRecords = ""
LOOP
rstemp.close
set rstemp=nothing
objConn.Close
set objConn=nothing
filOutput.Close
set filOutput=nothing
set fso=nothing

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[rccoates]

Thanks I got that to work and substituted the code I needed in place.

 

[mirtheil]

That's good to hear. What was the problematic code?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com