MySQL: Inequality Queries

SELECT * FROM ssubject WHERE status NOT IN ('W','X');

SELECT * FROM ssession WHERE sem<=2;

To get Cummulative Credit Points from Sem 1 and Sem 2:

SELECT SUM(crptearn) FROM ssession WHERE sem<=2
AND studentid=10;

MySQL: Marks Input Design (Used in IComis)

To implement this User Interface:




Scenario:

1. Form loads the Program Combo Box with all the
available Programs
2. User selects a program and it loads the Session
Combo Box
3. User clicks on the Load Subjects Button, and it
loads the Subjects Combo Box.
4. User clicks on Load Students Button, and it
loads the Students Combo Box.


This loads the Program Combo Box:

SELECT name FROM program ORDER BY name;

This loads the Session Combo Box:

SELECT DISTINCT session FROM ssession;

This loads the Subjects Combo Box when the
Load Subjects Button is pressed:

SELECT DISTINCT code FROM ssubject
WHERE sessionid IN
(SELECT sessionid FROM ssession WHERE session='JAN 2009'
AND studentid IN (SELECT studentid
FROM student WHERE program='DICT'));


This loads the Students Combo Box when the
Load Students Button is pressed:

SELECT studentid,name FROM student
WHERE studentid IN
(SELECT studentid FROM ssession
WHERE session='JAN 2009' AND sessionid IN
(SELECT sessionid FROM ssubject
WHERE code='CSC1b')) AND program='DICT'
ORDER BY name;


Sample Java Code:

cbxStudent.removeAllItems();
try {
//---init cbxStudent---
statement = connection.createStatement();
String sSQL =
"SELECT studentid,name FROM student "
+ "WHERE studentid IN "
+ "(SELECT studentid FROM ssession "
+ "WHERE session='"
+ cbxSession.getSelectedItem().toString().trim()
+ "' AND sessionid IN "
+ "(SELECT sessionid FROM ssubject "
+ "WHERE code='"
+ cbxSubject.getSelectedItem().toString().trim()
+ "')) AND program='"
+ cbxProgramName.getSelectedItem().toString().trim() +"'"
+ " ORDER BY name ";

rsStudent = statement.executeQuery(sSQL);


while (rsStudent.next()) {
cbxStudent.addItem(rsStudent.getString("name"));
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "btnLoadStudents: " + e.toString());
//System.exit(1);
} finally {
try {
statement.close();
} catch (Exception e) {
}
}

MySQL: Drilling-down type nested queries (Not Used )

Scenario:

1. List all students in doing DICT for JAN 2009:
2. User selects Darren, then list all subjects taken by Darren.
3. User selects CSC1b subject for coursemarks input.

Below are MySQL Query Browser queries for the Scenario
above. Using nested queries provides the solution.






Text Version:

SELECT * FROM student WHERE program='DICT'
AND studentid IN
(SELECT studentid FROM ssession WHERE session='JAN 2009');


SELECT * FROM ssubject
WHERE sessionid IN
(SELECT sessionid FROM ssession WHERE session='JAN 2009'
AND studentid IN (SELECT studentid
FROM student WHERE program='DICT' and name='Darren'));



SELECT * FROM ssubject
WHERE sessionid IN
(SELECT sessionid FROM ssession WHERE session='JAN 2009'
AND studentid IN (SELECT studentid
FROM student WHERE program='DICT' and name='Darren'))
AND code='CSC1b';

MySQL: Decimal, Nested Query, ResultSet Pointer Manipulation

FLOAT:
FLOAT(4,2) will give 1.1 for data input 1.1


DECIMAL:
DECIMAL(4,2) will give 1.10 for data input 1.1
4 means 4 digits exclusing decimal point.
eg. 12.34 is four digits.
2 means how many decimal places.

So use decimal.

To get decimal from a ResultSet:
double f = resultSet.getDouble("credithour");


assuming credithour is a DECIMAL(10,2)
you will get 12.34

To input a decimal:
double value = Double.parseDouble((String)table.getValueAt(rowl,col));
Then, use the value in an SQL statement:

INSERT INTO table(name, marks) VALUES('James',value);

Example of a Nested Query:
select * from psubject
where pid in ( select pid from program where name='DICT' )
AND sem=1;

ResultSet Pointer:
After every update or query, the ResultSet points to the location
before the first row. You need to do this before getting anything
out again:

if(rsPsubject.next()) populateSubjectListForm();

Moving ResultSet to updated row (after an update):
After doing any updates, your ResultSet points to location
before the firt row. If you wish to go back to the previous
row, extract the primary key first before doing the update,
the do the update, then use a simple loop to get back to
the previous row:

//---get the primary key of the current row---
int currentStudentID = rsStudent.getInt("studentid");



//---Do your update here:



requeryStudent();

//---go back to row before the update---
rsStudent.next();
while(!(rsStudent.getInt("studentid")==currentStudentID)) rsStudent.next();

Reverse RAT Links

http://paulmcpd.blogspot.com/2009/02/how-to-write-remote-access-tools-in.html

http://paulmcpd.blogspot.com/2009/02/execute-external-program-from-java.html

http://paulmcpd.blogspot.com/2009/02/command-execution.html

http://paulmcpd.blogspot.com/2009/02/process-class-in-java.html

Robots:

http://paulmcpd.blogspot.com/2008/03/java-remote-desktopcapture.html

http://www.developer.com/java/other/article.php/2212401

Reverse RAT

Web Browser Source Code:

public FrWebBrowser() {
initComponents();
try{
tpOutput.setPage("http://www.google.com.my");
}catch(Exception e){

}
}

Modify the constructor as shown above. You should have a
JTextPane called tpOutput.


Desktop Capture Source Code:



private void btnCaptureActionPerformed(java.awt.event.ActionEvent evt) {
String userHome = System.getProperty("user.home") + "\\";
captureScreen(userHome + "desktop.jpg");
}

public void captureScreen(String fileName){
Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
try{
BufferedImage image = new Robot().createScreenCapture(new Rectangle(screenSize));
ImageIO.write(image, "jpg", new File(fileName));
//ImageIO.write(image, "png", new File(fileName));
}catch(Exception e){
JOptionPane.showMessageDialog(null, "captureScreen: " + e.toString());
}
}



FTP Source code :
Modify the sendFile ( ) method to upload desktop.jpg:

private void sendFile() {
String ftpHost = "*************"; //get this from me
String ftpUserName = "student";
String ftpPassword = "********"; //get this from me
String ftpRemoteDirectory = "public_html";
String userHome = System.getProperty("user.home") + "\\";
String fileToTransmit = userHome + "desktop.jpg";

FTPClient ftp = new FTPClient();
try{
ftp.connect(ftpHost);
ftp.login(ftpUserName, ftpPassword);
ftp.changeWorkingDirectory(ftpRemoteDirectory);

File f = new File(fileToTransmit);
ftp.setFileType(FTP.BINARY_FILE_TYPE);

ftp.storeFile(f.getName(),new FileInputStream(f));

ftp.disconnect();

}catch(Exception e){
JOptionPane.showMessageDialog(null, e.toString());
}

}



For FTP, you should have a button event handler that calls
sendFile( ) above.


Now, go back to your Web Browser form and modify the constructor:

public FrWebBrowser() {
initComponents();
try{
tpOutput.setPage("********"); //get this from me
}catch(Exception e){

}
}

To test the program, one person will run the FTP and click Capture Desktop
followed by Send File.

Another person will run Web Browser to see the first person's desktop.
You can also test it alone, just click Capture Desktop, Send File, then Open the Web
Browser.

Java Screencam

http://wuetender-junger-mann.de/wordpress/?p=585 wrote:

A Screen Camera in Java

July 12, 2008, 23:48

Today I wrote a little class that allows to repeatedly capture the screen content and write the result to a flash file. It created a flash video with one frame per second (this is a bit crude) with one frame per screen shot taken. It is based on JavaSWF2 a nice wrapper around the SWF file format. To run you need to get the JavaSWF2 jarfile. Then you go:

camera = new ScreenCamera(new File("test.swf"));
doStuff();
camera.takeScreenShot();
doMoreStuff();
camera.takeScreenShot();
camera.close();

It might be useful for testing and creating tutorials.

http://www.anotherbigidea.com/javaswf/

JavaSWF2 is a set of Java packages that enable the parsing, manipulation and generation of the Macromedia Flash(TM) file format known as SWF ("swiff").

JavaSWF2 is available as Java source code under the JavaSWF2-BSD License - which is a BSD License that is compatible with the GPL.