Unable to fetch database table from webdynpro application

[Former Member]

Hello,

I have a Webdynpro application trying to fetch data from the database table. I have used the following code to get the connection to the database and execute the sql statement.

InitialContext dbInitContext = new InitialContext();

Properties sysProperties = System.getProperties();

String sysname = sysProperties.getProperty("SAPSYSTEMNAME");

String dbName = "jdbc/" + "SAP" + sysname + "DB";

DataSource dataSource = (DataSource) dbInitContext.lookup(dbName);

Connection conn = dataSource.getConnection();

String SelectStmt = "Select * from INV_CUSTOMER";

PreparedStatement stmt = conn.prepareStatement(SelectStmt);

ResultSet resultSet = stmt.executeQuery();

I logged into the database using user superdba in sqlcli, and created the table "INV_CUSTOMER". I am trying to run this application in my sneak preview java installation which comes with maxdb 7.6. But when I run the app, it always throws up the error:

The SQL statement "SELECT * FROM "INV_CUSTOMER"" contains the semantics error[s]: - 1:15 - the table or view >>INV_CUSTOMER<< does not exist

I am new to this concept, so any help would be appreciated.

Thanks,

Ajay

[Former Member · Accepted solution]

Hi,

Thanks for your replies. I am able to connect to my database table but when I am trying the following code, I get an SQL exception at the "PreparedStatement stmt....." line as shown:

String SelectStmt = "INSERT INTO TMP_COMPANY_CODES(CODE,DESTINATION,ACTIVE) values(1100,'test','dummy') ";

PreparedStatement stmt = conn.prepareStatement(SelectStmt);

stmt.executeQuery();

stmt.executeUpdate();

conn.commit();

stmt.close();

conn.close();

SQL Exception:

The SQL statement "INSERT INTO "TMP_COMPANY_CODES" ("CODE","DESTINATION","ACTIVE") VALUES (1100,'test','dummy')" contains the semantics error[s]: - type check error: new value (element number 2 (CHAR)) is not assignable to column >>DESTINATION<< (CLOB)

- type check error: new value (element number 3 (CHAR)) is not assignable to column >>ACTIVE<< (CLOB)

The database table fields destination and active are of type string, and their JDBC type is shown as CLOB.

How do I resolve this exception?

Thanks,

Ajay

[p_2_5_6_9_6_0 · Accepted solution]

>

> Hi,
> 
> Thanks for your replies. I am able to connect to my database table but when I am trying the following code, I get an SQL exception at the "PreparedStatement stmt....." line as shown:
> 
> String SelectStmt = "INSERT INTO TMP_COMPANY_CODES(CODE,DESTINATION,ACTIVE) values(1100,'test','dummy') ";
> PreparedStatement stmt = conn.prepareStatement(SelectStmt);
> stmt.executeQuery();
> stmt.executeUpdate();
> conn.commit();
> stmt.close();
> conn.close();
> 
> SQL Exception:
> The SQL statement "INSERT INTO "TMP_COMPANY_CODES" ("CODE","DESTINATION","ACTIVE") VALUES (1100,'test','dummy')" contains the semantics error[s]: - type check error: new value (element number 2 (CHAR)) is not assignable to column  >>DESTINATION<< (CLOB)
> - type check error: new value (element number 3 (CHAR)) is not assignable to column  >>ACTIVE<< (CLOB)
> 
> The database table fields destination and active are of type string, and their JDBC type is shown as CLOB.
> 
> How do I resolve this exception?
> 
> Thanks,
> Ajay

Hi,

A few things that I noticed:

1.) Dont use the stmt.executeQuery() command since you are simply inserting data into the table.

2.) Use stmt.execute() or stmt.executeUpdate() as you have done in the program now. That alone should suffice.

3.) The SQL Exception is due to a mismatch in the data types of the columns and the values that you are passing in the isnert command. Particularly check the data types and lengths for the Destination and Active columns in the dictionary table. They should be the builtin type "string" in most cases. Also use a sufficiently long length for your data so that there is enough space for the strings.

Thanks.

p256960.

[Former Member · Accepted solution]

Hi,

Thanks a lot for your suggestions, it works now and I am able to fetch the data from the tables and display them.

Thanks,

Ajay

[siddharth_jain]

Hi,

Make sure that you have Written conn.commit(); after storing data in DB.

Siddharth

[Former Member]

Try this.

String name=wdContext.currentMyDataElement().getName();
String userName = "sa";
String password = "jktsql";
String url ="jdbc:microsoft:sqlserver://(servername)jothi:(port number)1433;databaseName=student";
				String querry="select * from empDetail where name=?";
				Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
				Connection con =DriverManager.getConnection(url, userName, password);
				Statement st=con.createStatement();
				PreparedStatement ps=con.prepareStatement(querry);
				ps.setString(1,name);
				ResultSet rs=ps.executeQuery();
				while(rs.next())
				{
					String strn=rs.getString(1);
					String strp=rs.getString(2);
                                       wdComponentAPI.getMessageManager().reportSuccess(strn);
                                       wdComponentAPI.getMessageManager().reportSuccess(strp);
                                    }

]

Regards,

H.V.Swathi

Edited by: H.V Swathi on Dec 9, 2008 5:08 AM

Edited by: H.V Swathi on Dec 9, 2008 5:09 AM

[Former Member]

Hi,

The SAP System connection is a Open SQL Driver type, so I think that you can't read a table using this connection if it wasn't created from a dictionary.

Try to create this table with a dictionary project instead to create directly on database.

Best regards

[Former Member]

Hi Isaias,

I created a dictionary project with the same structure, and used it in my app. Right now, everytime I run my app, the old data I entered is lost. I need to store data in the table, and reuse those records the next time I run the app. How do I do this?

Another question I have is, I have created Database Tables inside Local Dictionary of my dictionary project. Does this create a table in the actual database?

Forgive me, but I am new to this topic.

Thanks,

Ajay

[p_2_5_6_9_6_0]

>

> Hi Isaias,
> 
> I created a dictionary project with the same structure, and used it in my app. Right now, everytime I run my app, the old data I entered is lost. I need to store data in the table, and reuse those records the next time I run the app. How do I do this?
> Another question I have is, I have created Database Tables inside Local Dictionary of my dictionary project. Does this create a table in the actual database?
> Forgive me, but I am new to this topic.
> 
> Thanks,
> Ajay

Hi,

It seems to me like there is more than one issue here. Let me try and help you out with some details.

1.) If you have created the Dictionary project and the code in your original post is not giving you problems then it's good. However, the problem of the data disappearing from your Java Dictionary seems little bit wierd. You need to ensure that you are not running any "delete" command and ensure that the select statement (of it has and conditions) is correct.

2.) As far as where the tables actually reside: When you create the Dictionary tables in a Dictionary Project- this will actually create a script that will be run on the server that has an installed database (like Oracle, MSSQL Server or MaxDB, UDB). This means that the dictionary tables are just an abstraction of actual database tables.

In order to ensure that you are not deleting the data - debug your Web Dynpro Application or EJB so that you can go through it step by step and see whether all the database statements are correct. Sometimes you may not get an SQL error due to a NullPointer in the dynamic queries as well - so keep a check on that as well.

If you require a more detailed solution - please give us a small piece of code that we can analyse.

Hope that helps.

Thanks.,

p256960.