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@goldstarsoftware.com
	http://www.goldstarsoftware.com
	*** Chicago: Pervasive Service & Support Class - 03/2007 ***


dsuellentrop@gmail.com wrote:

> 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.

Bill Bach wrote:
> 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.

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@crossflight.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@goldstarsoftware.com
	http://www.goldstarsoftware.com
	*** Chicago: Pervasive Service & Support Class - 03/2007 ***

Guy Dawson wrote:

> Bill Bach wrote:
> > 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.
> 
> 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@crossflight.co.uk

dsuellentrop@gmail.com wrote:
> 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.

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