Is there a way for me to write a batch file to run an SQL statement in
a Pervasive table. Or is there a way to write a VB script to execute
the SQL statement. Or is there any free software that will help me do
either of these 2 things.

If you have PSQLv9 and are doing SQL statements that do not return any
data, then the included PVDDL tool will work just fine.

If you have an older version, or you need return codes back, or more
flexible "INSERT from file" capabilities, then our SQLExec tool is a
good place to start. It's not free, but the cost is fairly low. Go to
www.goldstarsoftware.com/sqlexec.asp to see what it can do.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 03/2007 ***


dsuellentrop (AT) gmail (DOT) com wrote:

Bill Bach wrote:
Can sqlexec be used to extract data from one odbc database and insert
it into another?

Say, on a PC we have two ODBC databases called :

production
mis

Could sqlexec run statements such as

insert into mis.table (<field_list>)
select <field_list> from production.table
where production.table.field = <some_value>


Guy
-- --------------------------------------------------------------------
Guy Dawson I.T. Manager Crossflight Ltd
gnues (AT) crossflight (DOT) co.uk

You can use one SQLEXEC command to extract data to a comma-delimited,
and another to import comma-delimited. While I haven't tried, the code
may even be diasy-chainable to allow a single command to work, but it
would still execute two statements separately.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 03/2007 ***

Guy Dawson wrote:

dsuellentrop (AT) gmail (DOT) com wrote:
This is a quick, dirty and fast VBscript using ADO. Sorry about the
lack of comments, but I thought it was throw away.


Const FOR_READING = 1
Const FOR_WRITING = 2
Const FOR_APPENDING = 8


'************************************************* ************************************************** ****************
' Object Variables
'************************************************* ************************************************** ****************
Dim objFSO 'File System Object
Dim objConn 'ADO Connection Object
Dim objRecordSet 'ADO Record Set Object
Dim objTempOutputFile 'Text File Object
Dim objSortFile 'SortedText File Object
Dim objDialog
Dim strOutputPath

Dim TempOutputFile
Dim OutputFile

strComputer = "."



'************************************************* ************************************************** ****************
' Create Objects
'************************************************* ************************************************** ****************
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objConn = WScript.CreateObject("ADODB.Connection")
Set objRecordSet = WScript.CreateObject("ADODB.Recordset")


'************************************************* ************************************************** ****************
' Connect to the database file
'************************************************* ************************************************** ****************
objConn.ConnectionString = "DSN=TESTSCRIPT"
objConn.Open()

'************************************************* ************************************************** ****************
' Read all data if MMDDYY01.sav position 223 = A
'************************************************* ************************************************** ****************

Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Daily Decision Files|*.sav|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "P:\data"

intResult = objDialog.ShowOpen

If intResult = 0 Then
Wscript.Quit
Else


End If


Set objSortFile = objFSO.OpenTextFile(objDialog.FileName, FOR_READING)

'************************************************* ************************************************** ****************
' Set all the Items
'************************************************* ************************************************** ****************

i=1
do while not objSortFile.AtEndOfStream

strLine = objSortFile.ReadLine


If Mid(strLine,223,1) = "A" then


strAccount=Mid(strLine,5,20)

strISN="0000000000" & Mid(strLine,53,10)

strUserID=Mid(strLine,213,8)

strVDate=Mid(strLine,233,4) & Mid(strLine,227,2) & Mid(strLine,230,2)

strvTime="060000"

'This constructs the SQL statement

dim strSQL 'as String
strSQL=""
strSQL=strSQL & "UPDATE SigProfile "
strSQL=strSQL & "SET VerifiedFlag='Y', VerifiedBy='" & strUserID &
"',VerifiedDate='" & strVDate & "', VerifiedTime='" & strvTime & "' "
strSQL=strSQL & "WHERE AccountNumber='" & strAccount & "' AND
ItemSequence='" & strISN & "'"



objRecordSet.Open strSQL, objConn

i=i+1

else


end if

Loop

MsgBox "The Process has updated " & i & " records. "


objSortFile.Close
Set objConn = nothing
Set objRecordSet = nothing