Sitemap

Amicuk Programming Answers

SQL statements:help

2015-10-11   Views:1

Advertisement

here is a sample code of my application.... int initialDelay = 10000; // start after 10 second int period = 10000*60*60*24*30; // repeat every month Timer timer = new Timer(); TimerTask task = new TimerTask() { public void run() { try { MyDBConnectio

here is a sample code of my application....
int initialDelay = 10000; // start after 10 second
int period = 10000*60*60*24*30; // repeat every month
Timer timer = new Timer();
TimerTask task = new TimerTask() {
public void run() {
try {
MyDBConnection jdbc = new MyDBConnection();
jdbc.init();
Connection conn = jdbc.getMyConnection();
Statement stmt = conn.createStatement();
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = new Date();
String[] tbl = {"emticket","emattachment","emhistory","emtracker"};
progTxt.setText("");
Logs l = new Logs();
l.append("Auto Archive");
date.getTime();
//Archive data
progTxt.append("\n\n");
progTxt.append(" Archiving Data...\n ");
stmt.execute("select * into outfile 'c:/emticket " + dateFormat.format(date) + ".csv' fields terminated by ',' enclosed by '\"' lines terminated by '\\n' from emticket where RcvDateTime between date_sub(curdate(),interval 90 day) and curdate()");
stmt.execute("select * into outfile 'c:/emattachment " + dateFormat.format(date) + ".csv' fields terminated by ',' enclosed by '\"' lines terminated by '\\n' from emattachment where EmID in (select EmID from emticket where RcvDateTime between date_sub(curdate(),interval 90 day) and curdate())");
stmt.execute("select * into outfile 'c:/emhistory " + dateFormat.format(date) + ".csv' fields terminated by ',' enclosed by '\"' lines terminated by '\\n' from emhistory where TicketID in (select TicketID from emticket where RcvDateTime between date_sub(curdate(),interval 90 day) and curdate())");
stmt.execute("select * into outfile 'c:/emtracker " + dateFormat.format(date) + ".csv' fields terminated by ',' enclosed by '\"' lines terminated by '\\n' from emtracker where TicketID in (select TicketID from emticket where RcvDateTime between date_sub(curdate(),interval 90 day) and curdate())");
progTxt.append("Done.\n\n");
//Delete data
progTxt.append(" Deleting Data... ");
stmt.execute("delete emticket, emattachment, emhistory, emtracker from emticket, emattachment, emhistory, emtracker where emattachment.EmID=emticket.EmID and emhistory.TicketID=emticket.TicketID and emtracker.TicketID=emtracker.TicketID and where RcvDateTime
"_______"DATE_SUB(CURDATE(),INTERVAL 90 DAY) and CURDATE())");
System.out.println(stmt.getUpdateCount());
progTxt.append("Done.\n\n");
Here's what i want to do....I want to backup file for the current date back to 3 mos...e.g 2007-05-23 down to 2007-02-23...then delete the remaining records...before"02-23-2007 to 05-23-2007"....this is where the query part i that doesn't work....
Delete data
progTxt.append(" Deleting Data... ");
stmt.execute("delete emticket, emattachment, emhistory, emtracker from emticket, emattachment, emhistory, emtracker where emattachment.EmID=emticket.EmID and emhistory.TicketID=emticket.TicketID and emtracker.TicketID=emtracker.TicketID and where RcvDateTime
"_______"DATE_SUB(CURDATE(),INTERVAL 90 DAY) and CURDATE())");
System.out.println(stmt.getUpdateCount());
progTxt.append("Done.\n\n")
I tried putting <>=which means not equal in the "_____"but it fails to work..can somebody help me to work out with this query.

The replay answer
Advertisement
I don't know MySQL, but shouldn't you just use <= ? I also don't understand what the final part 'and CURDATE()' is for...

Go to See the other 2 answers

Logical Operations in SQL decode function ?

Category:DefaultRelease time:2015-10-11Views:130

Hi, Is it possible to do Logical Operations in SQL decode function like '>' '<' '>=' '<=' '<>' not in in not null is null eg... select col1 ,order_by,decode ( col1 , > 10 , 0 , 1) from tab; select col1 ,order_by,decode ( col1 , <&g[More]

Unable to capture the parameter values from a PL/SQL procedure

Category:DefaultRelease time:2015-10-11Views:130

hi. i'm trying to capture the parameter values of a PL/SQL procedure by calling inside a anonymous block but i'm getting a "reference to uninitialized collection error" ORA-06531. Please help me regarding. i'm using following block for calling t[More]

Unable to capture SQL Server Schema

Category:DefaultRelease time:2015-10-11Views:130

I am trying to capture SQL Server 2005 demo schema (AdventureWorks). It does shows the numbers of Tables/Indexes captured..but finishes with this error.. Error ocurred during capture: Exhausted Resultset Column not found. Skipping index detail on ind[More]

NoClassDefFoundError: com/sap/sql/log/OpenSQLException

Category:DefaultRelease time:2015-10-11Views:130

I have developed a Java project. It consists of 2 SQLJ files and then a Java class. When I try to execute the Java program, I get a Java error: java.lang.NoClassDefFoundError: com/sap/sql/log/OpenSQLException      at com.mbb.crrs.business.report.dao.[More]

AND / OR  truth table in SQL

Category:DefaultRelease time:2015-10-11Views:130

why null is introduced in the AND and OR truth table?when will a statement/part of a statement return a null value?? |AND               |True          |False             |NULL??        | |True               |True          |False             |Null    [More]

If statement in sql

Category:DefaultRelease time:2015-10-11Views:130

regarding the following sql: SELECT a.lname, a.fname, a.user_id, c.address, c.city, c.zip, c.addresstypeid, d.descriptor as state FROM users a, address c, maintstatetype d WHERE a.user_id = c.user_id(+) AND c.statetypeid = d.statetypeid(+) AND c.addr[More]

If statement in reqular sql statement

Category:DefaultRelease time:2015-10-11Views:130

Hi everyone; I am trying to alter some trigger if I am in a certain environment. here what I am trying to do in pseudo-code if ((select dbenv from environments) = 'DEV') THEN ALTER TRIGGER XXX disable; end if; this sql script will be run from sqlplus[More]

IF statement in SQL*Plus - how to do it

Category:DefaultRelease time:2015-10-11Views:130

Hi, In SQL*Plus script, I would like to keep conditional checking (IF statement) and proceed. For example, whatever is done in PL/SQL block below, want to do the same in SQL*Plus script, I know partly it can be done using VARIABLE keyword, conditiona[More]

Partner Application written in other language than PL/SQL and Java

Category:DefaultRelease time:2015-10-11Views:130

I have an application written in another language than PL/SQL or Java. I want to integrate this application as an Partner apps where I use the same user repository as Portal. Can I integrate the application by calling a stored PL/SQL-procedure based[More]

NULL and dynamic SQL

Category:DefaultRelease time:2015-10-11Views:130

If table testrh2 has the following columns and data col1 --> NULL col2 --> 2 and table testrh has the following columsn and data col1 --> NULL How could I write a dynamic SQL statement to join on the nulls? I've written the following block as a s[More]

How do I connect to SQL Server with Muse?

Category:DefaultRelease time:2015-10-11Views:130

I want to query items from database and load it back to front end. Is there a way Muse can connect to sql server database?You cannot connect to databases via Muse at the moment. Please refer: http://forums.adobe.com/message/5090145#5090145 Cheers, Vi[More]

Hot
Hi All, i have an external hard drive now my time machne has overheated and failed this works fine with time machine Is it possible for me to backup my windows pc to this external hard drive??? thanks in advance JasonI guess the external drive is han [More]
Hi, I have a Digital Signature plugin written for Acrobat X and it handles both signature creation and verification. When a PDF with digital signatures is opened in a browser (specifically IE8, IE9) acrobat X runs in browser mode and it calls our dig [More]
I cannot sign in FaceTime , it keeps on saying an error occurred during activation, please sign in againHey! I too was facing the same problem but have found a solution for it. The problem is most likely because of the 'Date & Time' setting. Change t [More]
Hi I developed a plugin, which changes exif information of the image (using a third party program). Is there a way to inform Lightroom programatically to refresh image metadata? Right now I have to right-click on the folder and choose "Synchronize Fo [More]
I am a storage admin trying to get a better understanding of RMAN. My question is for backuplevel.1 does RMAN require free space to write archive information or archive logs to to complete this backup? My DBA says "no" after I requested that he [More]
1. Performance. When stabilizing any footage I always ask myself: "What the heck is he doing there?!" not even a single core is really working hard. It looks like an idle background process  The GPU is idle as well, as the analysis pass is only [More]
Hi, Scenario : Slap and Ship Outbound scenario using SAP AII 5.1 Issue: Currently I am setting Up RFC Connections for the Auto-ID Test Tool. When I test the NOTIFY_TAGS_OUT RFC Destination, I am getting the below error HTTP/1.0 500 SRT: Unexpected fa [More]
When I click on the Photos tab on Facebook, I get a pop-up error message, telling me to try again. == URL of affected sites == http://facebook.comTroubleshooting extensions and themes * https://support.mozilla.com/en-US/kb/Troubleshooting%20extension [More]
I registered my Skype account through Facebook and i've been told my username is "facebook:myusername" and "myfacebookbookpassword" howerver they don';t seem to be working when i try to login via typing the the credentials and on my co [More]
Hi, I'm a beginner in JSP/Tomcat. I would like to include as much java code as possible inside java classes and just call java methods from my JSP pages. For instance in project PCliente I've created the Java class /u5/web/jakarta-tomcat-4.1.27/webap [More]