Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Periodically run an SQL update statement 1

Status
Not open for further replies.

Tabled

Technical User
Apr 14, 2020
5
GB
I need to run an update query periodically.

It basically set's audit dates to 000000. these dates are set as and when people up date stock records or when someone runs the 'update cycle count dates'. So I need it to run at night, ready for the morning's counts.

SQL:
UPDATE INVENTORY_MST2 INNER JOIN INVENTORY_MSTR ON INVENTORY_MST2.PART = INVENTORY_MSTR.PART SET INVENTORY_MST2.DATE_CYCLE = '000000'
WHERE ((Not (INVENTORY_MSTR.CODE_ABC)='A' And Not (INVENTORY_MSTR.CODE_ABC)='B'));

I could probably do this with access but it would be nice to keep it all within pervasive somehow and not having to install access run-time etc.
 
There is not a way to schedule a SQL statement within PSQL. You don't mention what version you are using. In PSQL v11 and later (and maybe some earlier versions), there is a command line tool called pvddl.exe that can connect to a database, run a script file with one or more SQL statements and even log the results. This tool could be run as a Windows Scheduled Task.
For example, you could save your SQL in a file called "ResetAuditDates.sql" with your SQL (tested before hand to make sure it doesn't do something wrong). You would then create the scheduled task pointing to the pvddl.exe with the correct command line parameters. I tested running pvddl.exe with the following command line (didn't create a task but that should be easy) and it updated the database:
Code:
pvddl.exe demodata "c:\scripts\ResetDate.sql" -log "c:\scripts\log.txt

Mirtheil
 
Thanks mirtheil

That will work perfectly!

log.txt said:
15-APR-2020 08:54:49.072 | pvddl: UPDATE INVENTORY_MST2 INNER JOIN INVENTORY_MSTR ON INVENTORY_MST2.PART = INVENTORY_MSTR.PART SET INVENTORY_MST2.DATE_CYCLE = '000000'
WHERE ((Not (INVENTORY_MSTR.CODE_ABC)='A' And Not (INVENTORY_MSTR.CODE_ABC)='B'));
15-APR-2020 08:54:49.088 | pvddl: A table or view name must be specified for update.
15-APR-2020 08:54:49.098 | pvddl: The data definitions have been updated.

I will need to specify which database to run the script on?
we have a number of database's, apologises I'm very new to PSQL and only really have experience with MS access -_-
With access I just connect to the relevant database and then run my script/s...
 
Yes, you will need to specify the database name. If you run pvddl.exe from a command line, it will show all of the command line options. You can also look in the documentation. In my example, "demodata" is the either the databasename or the Server DSN name.

Mirtheil
 
Thanks for your response mirtheil, really big help!

This is what I'm trying:
Code:
C:\PSQL\bin\pvddl.exe GLOBALSEL "C:\PSQL Scripts\ResetAuditDates.sql" -username xxx -password xxx -log "C:\PSQL Scripts\log.txt

GLOBALSEL is the serverDSN (see attached image)
I still get:
pvddl: A table or view name must be specified for update.

My sql syntax is wrong somehow?




 
 https://files.engineering.com/getfile.aspx?folder=f9b4539c-93ba-4310-92f7-f90865edc761&file=odbc.JPG
This is in C:\PSQL Scripts\ResetAuditDates.sql:

SQL:
UPDATE INVENTORY_MST2 INNER JOIN INVENTORY_MSTR ON INVENTORY_MST2.PART = INVENTORY_MSTR.PART SET INVENTORY_MST2.DATE_CYCLE = '000000'
WHERE ((Not (INVENTORY_MSTR.CODE_ABC)='A' And Not (INVENTORY_MSTR.CODE_ABC)='B'))

I'm specifying the table after the update statement, seems to be correct.
 
Pervasive 13.31

The SQL statement works in access with the PSQL tables linked.
I've run it in the PCC SQL editor, see attached image, from that image you can see the tables exist and I'm logged into that database.

Thanks for any help, i'm very new to this!

Edit:
This might work. I'm just doing a backup and I'll test it out!
SQL:
UPDATE INVENTORY_MST2 SET INVENTORY_MST2.DATE_CYCLE = '000000' FROM INVENTORY_MSTR 
WHERE ((Not (INVENTORY_MSTR.CODE_ABC)='A' And Not (INVENTORY_MSTR.CODE_ABC)='B'))
 
 https://files.engineering.com/getfile.aspx?folder=02e830e8-a041-45f8-bb83-5560010d60d9&file=tables.JPG
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top