wood burning stoves 2.0*
The moose likes JDBC and the fly likes creating virtual tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "creating virtual tables" Watch "creating virtual tables" New topic
Author

creating virtual tables

Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
I've a trouble here in jdbc.. i'm using jsp with oracle..
now.. i want to create a table
for that i'm using
create table <table name>

now what i want to do is..
i've a html file.. in that.. there is a text box..
when i click the button in that file.. the string i entered ..
will be carried to the jsp file..

String name = request.getParameter("name");

now..
i want to create a table named.. the String name..
for that what query i've to use..

io've given like this
String query = "CREATE TABLE " + "'name"'"
but showing errors..


help me.. please..
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

this is not the same as a virtual table, this is just a... table.

In future it helps if you reduce your description to just the facts of the problem, and include the error given by the server. Telling us it is 'showing errors' is not very helpful.

I'm guessing you should not be including the single quotes around the table name, it is not required.

Dave
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Actully this is my program


<html>
<head>
<title>Database creation
</title>
</head>
<%@ page language="java" session="true" import="java.sql.*" import="java.lang.*" %>
<body>
<%
String name = request.getParameter("n");
String age = request.getParameter("age");
out.println(name);
out.println(age);

try
{
try
{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{out.println(e);
}
String dbURL = "jdbc dbc:create";
Connection dbCon;
dbCon = DriverManager.getConnection(dbURL);


StringBuffer tab = new StringBuffer();
tab.append("CREATE TABLE ");
tab.append(name);
PreparedStatement ps = dbCon.prepareStatement(tab.toString());
out.println(tab.toString());
ps.executeUpdate();
out.println("tables created");




StringBuffer tab1 = new StringBuffer();
tab1.append("alter table");
tab1.append(name);
tab1.append(" add (eno number(2) , ename varchar2(20) , region varchar2(20)); ");

PreparedStatement ps1 = dbCon.prepareStatement(tab1.toString());
out.println(tab1.toString());
ps1.executeUpdate();

out.println("fields created");

dbCon.close();
}

catch(SQLException e)
{out.println("what a problem ??? " +e);
}
%>

<br>

</body>
</html>



when i run the program..

the error i can see is..

aravind 20 CREATE TABLE aravind tables created what a problem ??? java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement



please reply me.. what is the error in this..

if it is a wrong program..
please help me.. in creating a table... with which.. i want to give the name..through html page..

i'm using.. jsp.. with oracle..
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

it looks like "alter table" needs a space after it, otherwise you get "alter tabeltablename" rather than "alter table tablename"

Dave
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
no Mr.David..
this is showing the same error.. when i'm giving like this..
i cannot fix that problem..

is that code is correct
if yes..
please tell me.. why it is showing errors
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
mr.David...
After giving the space.. the error generated is


java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

Aravind,
I'm not sure if this is what is causing the error, but you typically don't include a semicolon in the JDBC SQL string. Try removing that.

If that doesn't work, copy the output of
out.println(tab1.toString());
and run it against the database command line. That often gives more meaningful messages about what is wrong.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Jeanne..
i've copied
out.println(tab1.toString()); to the oracle. database editor..
this line is working correctly..

actully sir the table is creating every time..
but the problem. is that.. the fields inside the table is not creating..


once again i'm copying the code and the error message..

please check it out and tell me. sir..


code is

<html>
<head>
<title>Database creation
</title>
</head>
<%@ page language="java" session="true" import="java.sql.*" import="java.lang.*" %>
<body>
<%
String name = request.getParameter("n");
String age = request.getParameter("age");
out.println(name);
out.println(age);

try
{
try
{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{out.println(e);
}
String dbURL = "jdbc dbc:create";
Connection dbCon;
dbCon = DriverManager.getConnection(dbURL);


StringBuffer tab = new StringBuffer();
tab.append("CREATE TABLE ");
tab.append(name);
PreparedStatement ps = dbCon.prepareStatement(tab.toString());
out.println(tab.toString());
ps.executeUpdate();
out.println("tables created");


StringBuffer tab1 = new StringBuffer();
tab1.append("alter table ");
tab1.append(name);
tab1.append(" add column(eno number(2) not null);");

PreparedStatement ps1 = dbCon.prepareStatement(tab1.toString());
out.println(tab1.toString());

ResultSet rs = ps1.executeQuery();

out.println("fields created");
rs.close();
dbCon.close();
}

catch(SQLException e)
{out.println("what a problem ??? " +e);
}
%>

<br>

</body>
</html>


error message is...

e CREATE TABLE e tables created what a problem ??? java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

As Jeanne said, remove the semi-colon from the 'add column' line. (and Jeanne is a girl )
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

tab1.append(" add column(eno number(2) not null)");
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
its showing the same error again..
syntax error in field definition..!!
what can i do now ..

please help me sir
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Can you try this please?
tab1.append(" add eno number(2) not null");

I believe the correct 'add' doesn't include the 'column' keyword. The bracks should be optional too.
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
sir i got the thing...

what i have added is

tab1.append(add eno number);
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: creating virtual tables