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 try to run Petstore 1.3.2 on JBoss 3.2.3. I used a patch for Petstore 1.3.2 to run on JBoss 3.0.1 RC1. I tested, this patch works on 3.0.1 well. Using database cloudscape, provided by j2eesdk 1.3.1. But it doesnt work in 3.2.3. link to the [More]
I just bought a power adapter and it is not working, says "not charging".   The same for the mobile charger.   Anything I need to do in the setting?The device is locked to the carrier it was purchased through unless you specifically purchased it [More]
Since updating to iOS7 on my iPhone 4s when writing text using a QWERTY keyboard with Chinese Simplified Pinyin, characters no longer appear, only the letters which I've typed. Anyone else with this problem and found a solution? It troubled me since [More]
my ipad mini was connected to VGA projector but no image was seenHi theflyingduke, Welcome to the Support Communities! Your post doesn't indicate what model projector you are using, but the link below will provide information and troubleshooting step [More]
Hi there, I am new to ABAP WebDynpro so please excuse me when the question is very simple. I haven't found an entry concerning the Communication between two different ABAP Components that could answer my question, so I am hoping that you can help me. [More]
On XP Computers when I am logger on as adminstrator there is no problem.  When my students are logged on as users, some of the Infinity Blocks are missing.  For example on Ch 2.1 Sec 2.2 lab, the ITOGGLE Block is a question mark.  I can search and fi [More]
Hi Guruu2019s Please help me in resolving the issue given below. I am able to get ad hoc cost estimation results in accounting screen, but not able to create a Quotation from Ad hoc cost estimation of cProjects. I am here with providing necessary ste [More]
Hi, I'm trying to manually deploy a project (form, servlet, java class, jsp) but I put the project into ROOT folder. I think I'm wrong because when the form executes post method on the servlet Tomcat cannot find it and gives me Error HTTP 404(resourc [More]
I am experiencing an issue where dragging photos in a manually ordered album does not allow me to change the order of the photos. See video below. Infuriating. 3.0.1 http://www.youtube.com/watch?v=gQUcSNxgfdACheck in Windows Explorer to see that the [More]
Hi Sir, How to use Oracle8i database to store chinese character? Is there any setting on database side? I'm using Oracle8i 8.1.5 currently. Please advise. Thanks.it's not the field type, it's the character encoding you need to change. Generally, you [More]