Periodically run an SQL update statement 1

[Tabled]

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.

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.

 

[mirtheil]

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:

pvddl.exe demodata "c:\scripts\ResetDate.sql" -log "c:\scripts\log.txt

Mirtheil

http://www.mirtheil.com

 

[Tabled]

Thanks mirtheil

That will work perfectly!

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

 

[mirtheil]

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

http://www.mirtheil.com

 

[Tabled]

Thanks for your response mirtheil, really big help!

This is what I'm trying:

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?

 

[mirtheil]

WHat's in "C:\PSQL Scripts\ResetAuditDates.sql"? Seems there is a problem with the SQL Statement in the SQL file.

Mirtheil

http://www.mirtheil.com

 

[Tabled]

This is in C:\PSQL Scripts\ResetAuditDates.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.

https://docs.actian.com/psql/psqlv1....htm#href=sqlref/syntaxref.03.90.html#2344642

 

[mirtheil]

That's odd. The syntax does look correct. What version of PSQL are you using? Does it work within the Pervasive Control Center?

Mirtheil

http://www.mirtheil.com

 

[Tabled]

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!

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'))

 

[mirtheil]

Looking at the documentation, inner join isn't supported in the Update statement. Outer joins are supported but inner joins aren't.

Mirtheil

http://www.mirtheil.com