Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2007
    Posts
    14

    Red face Unanswered: Scheduled export to text - help please!

    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

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Nov 2007
    Posts
    14
    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.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I believe AccPac does include DDFs. If it doesn't you should be able to get them from AccPac.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Nov 2007
    Posts
    14
    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.

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Nov 2007
    Posts
    14
    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.

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  9. #9
    Join Date
    Nov 2007
    Posts
    14
    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.

  10. #10
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    PSQL doesn't limit result sets to 100. How many records are in the table?
    What's the exact query?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  11. #11
    Join Date
    Nov 2007
    Posts
    14
    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.

  12. #12
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  13. #13
    Join Date
    Nov 2007
    Posts
    14
    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

  14. #14
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  15. #15
    Join Date
    Nov 2007
    Posts
    14
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •