×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Periodically run an SQL update statement

thread318-1802157

Periodically run an SQL update statement

Periodically run an SQL update statement

(OP)
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.

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

RE: Periodically run an SQL update statement

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
http://www.mirtheil.com

RE: Periodically run an SQL update statement

(OP)
Thanks mirtheil

That will work perfectly!

Quote (log.txt)

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

RE: Periodically run an SQL update statement

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

RE: Periodically run an SQL update statement

(OP)
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?




RE: Periodically run an SQL update statement

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

RE: Periodically run an SQL update statement

(OP)
This is in C:\PSQL Scripts\ResetAuditDates.sql:

CODE --> 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/psqlv11/wwhelp/wwhimp...

RE: Periodically run an SQL update statement

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

RE: Periodically run an SQL update statement

(OP)
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!

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

RE: Periodically run an SQL update statement

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close