• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

creating virtual tables

 
Aravind Prasad
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Aravind Prasad
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Jeanne said, remove the semi-colon from the 'add column' line. (and Jeanne is a girl )
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tab1.append(" add column(eno number(2) not null)");
 
Aravind Prasad
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
its showing the same error again..
syntax error in field definition..!!
what can i do now ..

please help me sir
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 265
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sir i got the thing...

what i have added is

tab1.append(add eno number);
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic