Finish the transaction

12.36

Display the foreign account information correctly, but the information about the HKD lost. The Tomcat report that the error occur in the SQL statement. Again, in the line 167:getRate() and line 199:doBuy(), 268:doGet(). Invalid column 'Buy'

Solution.1 change 'Buy' --> 'buyRate'

12.58

Display correctly with the foreign dollar and the hkd with the correct buying rate(in the console environment.)

15.38

Something may need to change in the transaction record table as the datetime attribute of the column traqns_date is in type:datetime

16.15

Some error occur as the return boolean value is incorrectly represented.

17:15

find a big problem when updating the database and accessing it complicated steps

• use the Update command

• update TABLE set COLUMN where CONDITIONS

17.17

Update the record in term of string to money n the hkd account/

• 'update usd set amount=Convert(money,'10.00') where u_id = 83'

• 'update TABLE set COLUMN=convert(money,'String')where u_id = 83;'

17.40 – unsuccessful ..

17.30

Use the type. Timestamp to get the current time in the Tomcat Server.

Static long | System.currentTimeMillis(); object

void Timestamp.setTime(long time)

// import a class,

'import java.lang.*;'

17.42

Use the CAST method – QSL92 standard.

17.47

Forget to change the executeUpdate with the query Update.

// rs = stmt.executeQuery(strSQL);

// affectRow = stmt.executeUpdate(strSQL);

// rs : ResultSet and affectRow : integer

18.05

// Step 5: Update the account

'String strAmount = String.valueOf(dblAmount);'

'strSQL = "update "+strTable+" set amount=Convert(money,'"+strAmount +"') where u_id='"+strUserID+"'";'

'stmt.executeUpdate(strSQL);'

18:40

Finished: bugs: when reload, the transaction continous and non-stop.

22.20

Can do the buy/sell functionally

Major re-factoring : can handling the different currency in one function , good for assignment 4.

01.20

Successfully used to buy and sell (with user u_id 83 and userName leungkac)

02.50
'Timestamp currentTime = new Timestamp( System.currentTimeMillis() );

query = "INSERT INTO transcation (update_time) VALUES ( ' " +

currentTime +

" ' )" ;

Timestamp currentTime = new Timestamp( System.currentTimeMillis() );

query = "INSERT INTO transcation (update_time) VALUES ( ' " +

currentTime +

" ' )" ;'

from Shing .

3.00

Finish the transaction.

Finsih terminattion function and edit user''s function

1. modify adminpreEdit.jsp function, administrator can view user's perosnal information in edit page and edit currency in the same page.
after submit, adminEdit.jsp will check if the input is negative value of currency, warning will display after update other infomation.
For usd , aud etc, if administrator update those uncreated account, function will build a new account for the correspondng currency.

2. Finish termination function
(user name in UserInfo will not be deleted, just insert a new row to "terminate" table)

Finally, it seems that the datdabase is not stable, always cannot connect to the database.
For the indirect access function, i still cannot get any idea to modify it.
There is a problem that, if the page refresh every second, user is difficult to view and to click the button. For example, if i refresh per second for administartor page, mostly, administrator cannot scroll down to view all user's information.

The layout of the transaction: Upper::Get result from the HTML with JavaScript file. Lower::Update the account and Display the latest information in the account. 30.03.05 12.36 

11.00
Review yesterday works: everything seems ok and modify the duplication of he userID.

13.10
Undergoing the buy/selling section

14.55
Insert the money into the by typing:
INSERT INTO usd values('83',0)

17.30
Cannot login the Database again

17.55
Create a Servlet- ExchangeServlet
Someone suggested we should follow the SQL command form the NetBeans' format

20.15
I can access the database and do some calculation

22.30
Modify something in the exchnage.html, remove all the cookie related material.
Keep the layout, modify the design a bit.

01.55
Link the Servlet and the HTML correctly.

02.30
Created a table, CurrRate, which is used to store the currency rate, used for calculation.

03.55
Sleep

Finish Dispaly function of Adminidtartor and User

1. Finish login function by using servlet

2.Finish display userinfo, currency in Administrator and normal User

3. Edit userinfo function for normal user and adminstrator is finished

4.Trying termination

5. Trying edit currency

6.Modfiy the interface of administrator

7. Except edit page of normal user, i have used jsp page link to jsp page which is easy to pass parameter. For example:
Today i have used ./xxx.jsp?uID=(userid), to pass the u_id, so that although administrator reg the page, i still can check which user info administrator want to take a look

I discover that if i use jsp, i don't need to open tomcat, the web page can still be accessed. However, it doesn't work in Servlet.

Finish Registeration With chekcing in both UserInfo and admin table

07:50

Solve the problem:

resultSet rs = executeQuery(strSQL1);

rs.next(); /// very important!

String here = rs.getString;

15.00

can resolve the problem as it seems that the control the resultSet, the rs method.

I found that the return of rs are not the null value but a string, namely

com.microsoft.jdbc.base.BaseResultSet@49cf9f

18.30

check user name L used

"SELECT count(*) AS count FROM member_info WHERE login_id = '" + name +"'";

ResultSet rs = ......

re.next();

if (result.getInt("count") == 0)

{

LHO user name

}

else

{

H

}

_ (06:43 PM) :

Y{ ...

count {..

(06:43 PM) :

count(*);

select * from ....

rs.last() <---- result set pointer h@ record

int numOfRow = re.getRow() <---- get a pointer b@

if ( numOfRow == 0)

{LH}

else

{H}

try {

// Step 2: load the SQL server 2000 JDBC driver

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

// Step 3: estalish the connection to the database

con = DriverManager.getConnection(url, dbLoginId, dbPwd);

System.out.println("6");

// disable auto commit;

System.out.println("7");

//Step 4: Create a statement

stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

//Step 4.1: after execute ceateStatement

System.out.println("8");

// First, find if the userID has already exits

strSQL3 = "select * from UserInfo where user_name='"+strUserName+"'";

System.out.println(strUserName);

System.out.println("9");

//boolean isExist = false;

System.out.println("9.1");

strSQL4 = "SELECT u_id FROM UserInfo WHERE user_name='"+strUserName+"'";

rs = stmt.executeQuery(strSQL3);

System.out.println("9.1.1");

System.out.println(rs.toString());

rs.next();

System.out.println(rs.toString());

//String tempStore = null;

System.out.println("9.1.2");

// go to the last record

rs.last();

// If the record exist, get hterow number, ie, record

int getNumberOfRow = rs.getRow();

if (getNumberOfRow != 0){

System.out.println("Same User Name has been registered. ");

System.out.println("9.3");

boolReg = false;

}

20.30

Finish the registration. Now i am working on the buy/sell sections.

Jakarta Tomcat - QueryRunner

http://jakarta.apache.org/commons/dbutils/apidocs/org/apache/commons/dbutils/QueryRunner.html

290305

00.19

Now we are undergoing the phase II, well, cathy work on admin page and I working on the buying/selling section

the administrator page divided into two pages: display normal user information and display terminated user informations.

01.10

response.sendRedirect("/iad/mainServlet");

01.42

Remember, in the registration page, we should check the user_name should not the saem as the administrators.

11.30

Successfully update the registration processes

Registeration done by verfiying the Admin bale and UserInfo table

07:50

Solve the problem:

resultSet rs = executeQuery(strSQL1);

rs.next(); /// very important!

String here = rs.getString;

15.00

can resolve the problem as it seems that the control the resultSet, the rs method.

I found that the return of rs are not the null value but a string, namely

com.microsoft.jdbc.base.BaseResultSet@49cf9f

18.30

check user name L used

"SELECT count(*) AS count FROM member_info WHERE login_id = '" + name +"'";

ResultSet rs = ......

re.next();

if (result.getInt("count") == 0)

{

LHO user name

}

else

{

H

}

_ (06:43 PM) :

Y{ ...

count {..

(06:43 PM) :

count(*);

select * from ....

rs.last() <---- result set pointer h@ record

int numOfRow = re.getRow() <---- get a pointer b@

if ( numOfRow == 0)

{LH}

else

{H}

try {

// Step 2: load the SQL server 2000 JDBC driver

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

// Step 3: estalish the connection to the database

con = DriverManager.getConnection(url, dbLoginId, dbPwd);

System.out.println("6");

// disable auto commit;

System.out.println("7");

//Step 4: Create a statement

stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

//Step 4.1: after execute ceateStatement

System.out.println("8");

// First, find if the userID has already exits

strSQL3 = "select * from UserInfo where user_name='"+strUserName+"'";

System.out.println(strUserName);

System.out.println("9");

//boolean isExist = false;

System.out.println("9.1");

strSQL4 = "SELECT u_id FROM UserInfo WHERE user_name='"+strUserName+"'";

rs = stmt.executeQuery(strSQL3);

System.out.println("9.1.1");

System.out.println(rs.toString());

rs.next();

System.out.println(rs.toString());

//String tempStore = null;

System.out.println("9.1.2");

// go to the last record

rs.last();

// If the record exist, get hterow number, ie, record

int getNumberOfRow = rs.getRow();

if (getNumberOfRow != 0){

System.out.println("Same User Name has been registered. ");

System.out.println("9.3");

boolReg = false;

}

20.30

Finish the registration. Now i am working on the buy/sell sections.

Jakarta Tomcat - QueryRunner

http://jakarta.apache.org/commons/dbutils/apidocs/org/apache/commons/dbutils/QueryRunner.html

290305

00.19

Now we are undergoing the phase II, well, cathy work on admin page and I working on the buying/selling section

the administrator page divided into two pages: display normal user information and display terminated user informations.

01.10

response.sendRedirect("/iad/mainServlet");

01.42

Remember, in the registration page, we should check the user_name should not the saem as the administrators.

11.30

Successfully update the registration processes

Mofidy login, reg function and build up "admin" database

29-03-2005
Have modified login function (which can identify administrator, normal member and terminated member)

Created “admin” table which store u_id(char 20) and password(char 20) and a_id is primary key which will increment automatically

01.38am
Modify registration, check if the name duplicate with administrator

2.46am
Doing display of userinfo in administrator page
?just finish display no of user
?trying display name of member (the no of row of table depends on user no)

Registeration - Database connection II

27/03/05

Still cannot Execute the SQL comamnd

2005/3/27 上午 02:49:50 org.apache.coyote.http11.Http11Protocol init

資訊: Initializing Coyote HTTP/1.1 on http-8080

Starting service Tomcat-Standalone

Apache Tomcat/4.1.31

2005/3/27 上午 02:49:51 org.apache.struts.util.PropertyMessageResources

資訊: Initializing, config='org.apache.struts.util.LocalStrings', returnNull=tru

e

2005/3/27 上午 02:49:51 org.apache.struts.util.PropertyMessageResources

資訊: Initializing, config='org.apache.struts.action.ActionResources', returnNul

l=true

2005/3/27 上午 02:49:52 org.apache.struts.util.PropertyMessageResources

資訊: Initializing, config='org.apache.webapp.admin.ApplicationResources', retur

nNull=true

2005/3/27 上午 02:49:54 org.apache.coyote.http11.Http11Protocol start

資訊: Starting Coyote HTTP/1.1 on http-8080

2005/3/27 上午 02:49:54 org.apache.jk.common.ChannelSocket init

資訊: JK2: ajp13 listening on /0.0.0.0:8009

2005/3/27 上午 02:49:54 org.apache.jk.server.JkMain start

資訊: Jk running ID=0 time=0/31 config=C:\Tomcat 4.1\conf\jk2.properties

Step 4: Create a statement

step 5: execute a quer

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]No ResultSet s

et was produced.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source

)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown So

urce)

at com.microsoft.jdbc.base.BaseStatement.executeQuery(Unknown Source)

at RegForm.inputRecord(RegForm.java:68)

at RegForm.doGet(RegForm.java:130)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl

icationFilterChain.java:200)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF

ilterChain.java:146)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV

alve.java:209)

at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex

t.invokeNext(StandardPipeline.java:596)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav

a:433)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV

alve.java:144)

at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex

t.invokeNext(StandardPipeline.java:596)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav

a:433)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)

at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:

2358)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j

ava:133)

at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex

t.invokeNext(StandardPipeline.java:596)

at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatche

rValve.java:118)

at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex

t.invokeNext(StandardPipeline.java:594)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j

ava:116)

at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex

t.invokeNext(StandardPipeline.java:594)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav

a:433)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal

ve.java:127)

at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex

t.invokeNext(StandardPipeline.java:596)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav

a:433)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)

at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:15

2)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java

:799)

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce

ssConnection(Http11Protocol.java:705)

at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java

:577)

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP

ool.java:683)

at java.lang.Thread.run(Thread.java:534)

Error when executing inputRecord!

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]No ResultSet set was produced.

I decide to update the hk dollar account in order to try to update the database

12.00

I realize the problem may not just related to the SQL command only, so i ask Shin.

12.30

After sending my problem to Shin, he sends me a copy of basic Servlet codes, just run it, compile it, little changes and re-compiled. Done.

Problem: still un-found, but may be related to the complicated commands that i have typed in. The command rs.close(); db.close(); and object.close(); etc are not added in Shin's coding.

import java.io.*;

import javax.servlet.*;

import javax.servlet.http.*;

import java.sql.*;

public class HelloWorld extends HttpServlet

{

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException

{

PrintWriter out = response.getWriter();

out.println("Hello World 2");

String result;

try

{

out.println("0");

String url = "jdbc:microsoft:sqlserver://w2ksa.cs.cityu.edu.hk:1433;databaseName=iad08_db;";

out.println("1");

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

out.println("2");

String dbLoginId = "iad08"; // database login ID

String dbPwd = "iad08abc"; // database password?

out.println("3");

Connection con = DriverManager.getConnection(url, dbLoginId, dbPwd);

out.println("4");

Statement stmt = con.createStatement();

String strSQL = "SELECT * FROM UserInfo";

ResultSet rs = stmt.executeQuery(strSQL);

} catch(Exception expt)

{

out.println("fail !!!!");

out.println(expt);

}

}

}

13.45

I can modified my code and input the database, from the web-browser, to the database. But the auto-increment of that test table not suitable test the real cases.

14.25

Successful on updating the test table by ignoring the command connnection.close() and statement.close().

14:35

I think I should keep the connection.close() there.

30. After a tea-break, successes!

31. Remember to match the type, cf, char, varchar match string and int, tinyint match int, float,decimal match double, float

20.17

How to close the session.

21.07

http://dev2dev.bea.com.cn/bbs/thread.jspa?forumID=1&threadID=3484&tstart=30

21.21

modify the HTML file – registration.html with JavaScript,

line 28 - > document.registerate

21.53

Exception handling under the ResultSet

Something added in line 135

//stmt.executeQuery(strSQL3);

//System.out.println("9.1");

rs = stmt.executeQuery(strSQL3);

System.out.println("9.1.1");

try{

strUid = rs.getString("u_id");

System.out.println(strUid);

}catch(SQLException sqlex){

System.out.println("Error in the SQLQuery");

throw sqlex;

}

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSet.html