SQL: invalid identifier

Hi Sathish,

           I want to insert a new record everytime user logs in

Please post output of

desc SIWEB_USERS

making sure you're using the same schema, table and login

Hi Charles,

                DESC command didnt work :(

                  but heres a snapshot of table.

>>DESC command didnt work :(

That's not a good sign. Did you use SQL Plus?

If the schema is o2, try

desc o2.siweb_users;

No, I am on DbVisualizer.

Already tried with Schema name... still the same error

13:05:53  [DESC - 0 row(s), 0.000 secs]  [Error Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

whats the solution to this charles?

just a simple INSERT is creating so much of havoc :(

You might try it in full

describe o2.siweb_users;

Anyway, i would use *two* Statements (PreparedStatements) not one

>>just a simple INSERT is creating so much of havoc :(

If you switched the queries/inserts around, you might find it's the SELECT that gives the problem. Anyway, if you're set on using just one Statement, i'd close it and the ResultSet before doing the second one

full "describe " didnt work either... :(

So instead of using 1 statement, you want me to user 2 preparedStatements? Will it make any difference?

>> If you switched the queries/inserts around, you might find it's the SELECT that gives the problem.

Didnt get you on this one Charles

>>Didnt get you on this one Charles

I was suggesting you switch the two queries around into reverse order - try it

Some progress...

        I shifted the queries... and this time a different error:

 13:39:39  [INSERT - 0 row(s), 0.000 secs]  [Error Code: 1, SQL State: 23000]  ORA-00001: unique constraint (VSLAND.SYS_C0031967) violated
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

Is it because, I tried to update the ID field which is a primary Key?

Hi Charles,

            so I dropped the primary Key on ID, and it staretd working.

           The thing I didnt get, what difference it made when we switched Select and Insert statements???

>>Is it because, I tried to update the ID field which is a primary Key?

Yes. You already have that key in the table

But a new problem Charles,

        because I moved Select query after Insert, I am not able to get access_type. This is the method code now. I need to insert the access_type with query.

public String getUserAccessType(String userId) {
		
		String accessType = null;
		
		String updateQry = "INSERT INTO SIWEB_USERS (ID, LAST_LOGIN, ACCESS_TYPE) VALUES ('" + userId.toLowerCase() + "', SYSDATE, '" + accessType + "')";
		
		String qry = "SELECT ACCESS_TYPE FROM SIWEB_USERS WHERE lower(ID) = lower('" + userId.toLowerCase() + "') ";
		 
	//	String updateQry_2 = "update SIWEB_USERS set LAST_LOGIN_TIME_2 = LAST_LOGIN_TIME WHERE lower(ID) = lower('" + userId.toLowerCase() + "') ";
//		String updateQry = "update SIWEB_USERS set LAST_LOGIN_TIME = sysdate WHERE lower(ID) = lower('" + userId.toLowerCase() + "') ";
	
		
		System.out.println("qry:" + updateQry);
		
		try {
			 db = new DbConn();
			 conn = db.getConnection(url, user, password);
				
			stmt = conn.createStatement();
			rs = stmt.executeQuery(qry);
						
			if (rs.next()) {				
				accessType = rs.getString(1);				
			}
			
         
			
           rs1  = stmt.executeQuery(updateQry); 
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
		    	db.closeResources(stmt, conn);
							
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return accessType;
	}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:

Yes that was really an experiment. I would use 2 statements

I used 2 statements now, but still gettting a null..

 Where am I going wrong charles?

public String getUserAccessType(String userId) {
		
		String accessType = null;
		
		
		String qry = "SELECT ACCESS_TYPE FROM SIWEB_USERS WHERE lower(ID) = lower('" + userId.toLowerCase() + "') ";
		
	
		String updateQry = "INSERT INTO SIWEB_USERS (ID, LAST_LOGIN, ACCESS_TYPE) VALUES ('" + userId.toLowerCase() + "', SYSDATE, '" + accessType + "')";	
	
		try {
			 db = new DbConn();
			 conn = db.getConnection(url, user, password);
				
			stmt = conn.createStatement();
			rs = stmt.executeQuery(qry);
						
			if (rs.next()) {				
				accessType = rs.getString(1);				
			}
	  		
			
		   stmt1 = conn.createStatement();	
           rs1  = stmt1.executeQuery(updateQry); 
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
		    	db.closeResources(stmt, conn);
				stmt1.close();			
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return accessType;
	}

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:

>>I used 2 statements now, but still gettting a null..

If you mean null is returned, the ID is probably not in the table

you should be doing an UPDATE not an INSERT

update SIWEB_USERS set LAST_LOGIN=SYSDATE where ID=?

Hi Mick,

               But update will update the same record. Right?

              Requirement is to create a new record everytime user logs in.

I am sorry Mick, I didnt get you on this one.

Why a separate table? This table is made for this purpose :(

because you will be duplicating the user details every time they log in.
That table is designed to hold user details eg. why do you need to store CREATE_DATE everytiem they login
(And why is the column call LAST_LOGIN?)

Actually, CREATE_DATE and all other stuff can be dropped, All I need is, USER_ID and LAST_LOGIN.

Then thats effectively the same as a separate table :)
I'd rename the LAST_LOGIN field as well.

you can get rid of your select statement, its not needed.

Hmmm Ok Mick.

Will do that :-)

Thanks :)

You had several database experts help you on this one before and the verdict was to use one table. Your mistake is that you have the wrong key field. The primary key should be the user id but a separate auto-incrementing table id

Hi Charles,

                  The database experts suggested me to use 2 tables instead of one :(

                   And after switching (as suggested by you), it is working fine....

                   Now I put a trigger on LAST_LOGIN, so it updates the other table.

                  Did I miss something?

>>The database experts suggested me to use 2 tables instead of one :(

Well i'd remembered one, but never mind ;-)

The point is that the table definition has to be wrong or you wouldn't have a dup key error

>                   The database experts suggested me to use 2 tables instead of one :(

absolutely its 2
one for the user details, and the other to store times they logged in. The foreign key would be the user id

>                    And after switching (as suggested by you), it is working fine....

you don't need to switch, the select is redundant and should be removed

>>  but heres a snapshot of table.

Just looked more closely at the table. You're trying to use the USER table as the USER_LOGIN table. There, of course, *are* two tables. The latter table is as i defined earlier.

Ohk :-)

@ Mick,

>> you don't need to switch, the select is redundant and should be removed

  But Mick, I need to know users access_Type, based on this I will hide/unhide "Admin" Tab on the page

Personally i would make the two queries have their own method - they're for quite different purposes

Also, your insert should work as is *as long as* it's into, say, a SIWEB_USER_LOGIN table, although it's questionable whether you should be storing access type in there

Hi Charles,

            Heres what I thought. Can you please suggest me if its correct or completely absurd :)

    I am thinking of keeping one table for all the user details, like access_type, fname, lname and everything but no LOGIN_TIME.

  and another table with just two fiels, ID and LOGIN_TIME. Everytime user logs in, insert a new row.

 Then I can do Count(*) on this table for a given date range, and make use of a JOIN to get Fname and Lname from the other table.

>>and another table with just two fiels, ID and LOGIN_TIME.

No -there needs to be at least three

ID
USER_ID
LOGIN_TIME

Hmmm.. why do we need ID on this one Charles?

>   But Mick, I need to know users access_Type, based on this I will hide/unhide "Admin" Tab on the page

but you don't need it to insert the login time, they are unrelated.
You would have code elsewhere to load the user, but this method is only concerned with inserting login time

>     I am thinking of keeping one table for all the user details, like access_type, fname, lname and everything but no LOGIN_TIME.
> and another table with just two fiels, ID and LOGIN_TIME. Everytime user logs in, insert a new row.

Thats what I already suggested :)

> Hmmm.. why do we need ID on this one Charles?

Not necessary