[jozi68]

Hi all,
I need to have a windows server export data to a text file (delimeted) on a weekly basis. It is a basic sql select query. I have no idea where to start. I have worked on MS sql a lot, but never Pervasive. I will need step by step instructions please. I have never even seen pervasive before. I need to go to a client next week to do this. That will be the first time I will see Pervasive. Any help will be much appreciated.
regards,
Jozi

[mirtheil]

Before you get step by step instructions, you need to give us a little more information.
Specifically:
1. What is the exact version of PSQL is being used? The easiest way to tell is to get the version of the NTDBSMGR.EXE and W3ODBCEI.DLL on the server.
2. Do you have DDFs for your data files? If not, can you get them from the vendor of the application?

As for a short answer to the original question, if you've got DDFs and an ODBC driver for Pervasive then you can use any number of tools to export the data. In fact, we offer a tool called DataXtraction (http://www.mirtheil.com/dataxtraction.asp) that can be used to export data from an ODBC data source to CSV. The export can also be automated with command line parameters and scheduled using the WIndows Scheduler.

[jozi68]

Thanks alot for the reply.
I will try to find out what the version is.
I don't know about DDFs, the application being used is AccPac, so maybe there are DDFs included? I hope to have more info later in the week.

[mirtheil]

I believe AccPac does include DDFs. If it doesn't you should be able to get them from AccPac.

[jozi68]

I managed to open the Pervasive.SQL Control Centre. The version there is 7.90.230.026.
I managed to create a query and save it as a .sql file.
Is there a way to call this query from the commandline? And save the results in a text file?
I'm thinking if that is possible, then I could call it from Windows Scheduler.
Please tell me whether I am way off on the wrong track or not.

[mirtheil]

You cannot call the SQL file directly and there are no Pervasive supported command line tools for issuing queries. You can use my DataXtraction (http://www.mirtheil.com/dataxtraction.asp) to execute the query and export the results to a CSV text file. It can be scheduled to run through Windows Scheduler. A trial version is available for download at the link above.

[jozi68]

Thanx for the reply.
Unfortunately my boss will not buy any more software.
I managed to create a stored procedure that can return a resultset.
Do you know of a way I can call the stored procedure from a vb6 application?
I think I will be able to create an ODBC datasource.
Some sample code will be greatly appreciated.

[mirtheil]

Running a stored procedure is the same as running a SELECT statement. I don't have any small code snippets handing. There are a number of options for accessing ODBC through VB.

[jozi68]

Thanx, I finally got it right to send a query to the Database. The weird thing is; it is only returning 100 rows! I need all rows!
The query is very simple:
Select col1, col2, col3 from table1
Is there a way to get all rows?
I think I can only send thru one line at a time.

[mirtheil]

PSQL doesn't limit result sets to 100. How many records are in the table?
What's the exact query?

[jozi68]

select (about 16 different columns, I dont have it with me right now) from apibh (which is the invoice table af accpac).
Same thing with apven (which is the vendor table of accpac). There are about 23000 records in apibh and about 3000 in apven.
When I run my query through the Pervasive control centre, I get all the records, but if I do this through the VB app, It only gives me 100 each.

I'm sure there's a setting somewhere, but I have no idea where.

[mirtheil]

If PCC works and displays all records but your code doesn't then there might be something in your code.
There aren't any settings from the PSQL side that would limit records.
What's your code?

[jozi68]

Dim cn As rdoConnection
Dim en As rdoEnvironment

strConnect = "DRIVER={Pervasive ODBC Client Interface};ServerDSN=PDataSource;ServerName=10.12. 10.61.1583;TCPPort=1583;TransportHint=TCPPX;UID= .admin.user1;PWD=99977"

Set cn = New rdoConnection
cn.Connect = strConnect
cn.EstablishConnection

Set ResSet = cn.OpenResultset("select AMTINVCTOT, AUDTDATE, CNTBTCH, CNTITEM, from apibh where AUDTDATE > 20050630;", rdOpenKeyset, rdConcurValues)
cn.BeginTrans

If ResSet.RowCount > 0 Then
Open CStr("C:\Program Files\CECaveng\PDATAapibh.txt") For Output As #2
ResSet.MoveFirst
For i = 0 To ResSet.RowCount - 1
Print #2, Trim(Trim(ResSet.rdoColumns(0))) & "|" & Trim(ResSet.rdoColumns(1)) & "|" & Trim(ResSet.rdoColumns(2)) & "|" & Trim(ResSet.rdoColumns(3))
ResSet.MoveNext
Next i
End If
Close #2
ResSet.Close

cn.Close

[mirtheil]

Are you sure the query ("select AMTINVCTOT, AUDTDATE, CNTBTCH, CNTITEM, from apibh where AUDTDATE > 20050630") returns more than the records you're getting? Because that WHERE clause could certainly limit the number of rows coming back.
If you've confirmed the query, then I'm not sure what's wrong. I haven't used RDO in a number of years. It might have something to do with the ".RowCOunt" but I'm not sure.

[jozi68]

yes, the query is right. It should bring back about 23000 records.
I realize RDO is a bit outdated - can you recommend a different access method? If yes, can you give me some somple code? I am obviously using vb6. Your help is greatly appreciated.