problem reading Oracle table

Smart GWT Technical Q&A
1

I’m using SmartGWT Power 2.5 on RHEL 6.

I’m having an issue using an XML data source descriptor to access an Oracle table; the Oracle error is “ORA-00942: table or view does not exist”, but I can browse to the table through the SmartGWT Admin console, and also verify the server.properties via the console.

Question #1: Could this error be related to using ojdbc14 vs. ojdbc6 (the table has a column which is a timestamp, and I know there are changes in the newer jar related to date objects)? I realize that is an Oracle question but I wonder if the SmartGWT community has encountered the issue specifically.

Question #2: can I verify or generate the table.ds.xml via the Admin console? When I Browse the database I can see the table I need; how do I generate the correct .ds.xml from it?

Question #3: what should I see in the Admin console when I select the Import DataSources tab? What is the implication if I see nothing? Does “import” (at least two different buttons have this label) mean from the DB, or from code, or..?

Question #4: how does the DataSource attribute “tableName” translate the actual name of the table – is it case sensitive? Given a table named LOGGING_EVENTS should the tableName=“LoggingEvents” or =“loggingEvents”, or does it not matter? This relates to my biggest gripe: please give me a datasource.xsd and it will make my work so much easier! Given that so much of SmartGWT hinges on a successful DataSource creation, – give us the tools to verify the XML!

the loggingEvent.ds.xml:

<DataSource 
	ID="loggingEvent"
    schema="PGDEV"
	dbName="Oracle"
	tableName="LoggingEvent"
	dataSourceVersion="1"
	serverType="sql" >
	<fields>
		<field name="TIMESTMP" title="TimeStamp" type="datetime" length="20"></field>
		<field name="FORMATTED_MESSAGE" length="4000" type="text"></field>
		<field name="LOGGER_NAME" length="254" type="text"></field>
		<field name="LEVEL_STRING" length="254" type="text"></field>
		<field name="THREAD_NAME" length="254" type="text"></field>
		<field name="REFERENCE_FLAG" type="number"></field>
		<field name="SOURCE_HOST" length="254" type="text"></field>
		<field name="JOB_ID" length="254" type="text"></field>
		<field name="USER_ID" length="254" type="text"></field>
		<field name="LOG_TYPE" length="254" type="text"></field>
		<field name="RETENTION_TIME" length="254" type="text"></field>
		<field name="EVENT_CAT_ID" length="254" type="text"></field>
		<field name="SITE_ID" length="254" type="text"></field>
		<field name="SATELLITE_ID" length="4" type="text"></field>
		<field name="ZONE_ID" length="254" type="text"></field>
		<field name="SITE_ID" length="254" type="text"></field>
		<field name="ENVIRONMENT" length="254" type="text"></field>
		<field name="ELEMENT_ID" length="254" type="text"></field>
		<field name="CLASS_NAME" length="254" type="text"></field>
		<field primaryKey="true" length="10" name="EVENT_ID" type="sequence"></field>
	</fields>
</DataSource>

server.properties

# The webRoot directory:
webRoot: __AUTODETECT__

gwtModuleName: logviewer
isomorphicPathRootRelative: $gwtModuleName/sc

# -------------- DATABASE --------------------
sql.defaultDatabase: Oracle
sql.Oracle.driver.serverName: 123456
sql.Oracle.driver.portNumber: 1521

# Database SID 
sql.Oracle.driver.databaseName: PGDEV

sql.Oracle.driver.user: ********
sql.Oracle.driver.password: ********

# -------------- LOADING APP AND DATASOURCE DEFINITIONS --------------------
project.datasources: $webRoot/shared/ds
project.apps: $webRoot/shared/app

# -------------- Other settings --------------------
RPCManager.enabledBuiltinMethods: xmlToJS, uploadProgressCheck, exportClientData, downloadClientExport, setAttributes, getAvailableScriptEngines, getLogNames, getLogEntries, saveSharedXML
FilesystemDataSource.enabled: true

the SQL to create the table:

CREATE TABLE "PGSWLOG"."LOGGING_EVENT"
  (
    "TIMESTMP"          NUMBER(20,0) NOT NULL ENABLE,
    "FORMATTED_MESSAGE" VARCHAR2(4000 BYTE) NOT NULL ENABLE,
    "LOGGER_NAME"       VARCHAR2(254 BYTE) NOT NULL ENABLE,
    "LEVEL_STRING"      VARCHAR2(254 BYTE) NOT NULL ENABLE,
    "THREAD_NAME"       VARCHAR2(254 BYTE),
    "REFERENCE_FLAG"    NUMBER(*,0),
    "SOURCE_HOST"       VARCHAR2(50 BYTE),
    "JOB_ID"            VARCHAR2(50 BYTE),
    "USER_ID"           VARCHAR2(18 BYTE),
    "LOG_TYPE"          VARCHAR2(18 BYTE),
    "RETENTION_TIME"    NUMBER(10,0),
    "EVENT_CAT_ID"      NUMBER(10,0),
    "SITE_ID"           VARCHAR2(18 BYTE),
    "SATELLITE_ID"      VARCHAR2(18 BYTE),
    "ZONE_ID"           VARCHAR2(35 BYTE),
    "ENVIRONMENT"       VARCHAR2(18 BYTE),
    "ELEMENT_ID"        VARCHAR2(18 BYTE),
    "CLASS_NAME"        VARCHAR2(250 BYTE),
    "EVENT_ID"          NUMBER(10,0),
    PRIMARY KEY ("EVENT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SWLOG" ENABLE
  )

the error:

[ERROR] [logviewer] - 19:41:44.481:XRP8:WARN:RPCManager:ORA-00942: table or view does not exist
, response: {operationId: "loggingEvent_fetch",
clientContext: Obj,
context: Obj,
transactionNum: 0,
httpResponseCode: 200,
httpResponseText: "//isc_RPCResponseStart-->[{queueStatus:-..."[162],
xmlHttpRequest: [object XMLHttpRequest],
transport: "xmlHttpRequest",
status: -1,
clientOnly: undef,
httpHeaders: Obj,
isStructured: true,
callbackArgs: null,
results: Obj,
queueStatus: -1,
isDSResponse: true,
invalidateCache: false,
data: "ORA-00942: table or view does not exist
",
startRow: 0,
endRow: 0,
totalRows: 0}

Thanks-
Susan

2

Hi Susan
If you open the Visual Builder tool, there’s an option to create .ds.xml files directly from database tables using a wizard interface.
See the “DataSources” section at the bottom right. Click “New…” and select the option to create a dataSource from “existing table or view”. There’s a graphical interface allowing you to (for example) set up which columns to include, set field titles etc.
The generated ds.xml file will be saved in the appropriate directory, and can be further hand-modified if necessary.

3

A quick follow up since our previous post didn’t address all the points you posted:

#1: This is very likely unrelated - the bug is probably just that the table name is incorrect in your dataSource

#2: We pointed you to the VisualBuilder / DataSource Wizard as a way to generate ds.xml files from a database table.
However another point: The dataSource created in this way is static. A different approach you should be aware of is to create your own .ds.xml file by hand (simple enough to do), and set the “autoDeriveSchema” attribute to true.
This means you don’t have to explicitly list out each field name - the server will dynamically generate the dataSource object based on the fields in the database table (which has the advantage that if you change the DB table you don’t have to rebuild your .ds.xml file)
The SmartGWT QuickStart Guide has a section which discusses this - see “DataSource Generation”.

#3: The Admin Console should show you a list of database configurations in the databases tab, and a list of configured datasources in the “Import DataSource” tab. If this list is empty it implies that the system is finding any dataSource configuration files. Any dataSource files you have should be stored under the war/ds/ directory of your project, should have a specified ID, and should be named the same as the DataSource ID with the suffix “.ds.xml”. Once again - the Quickstart guide has a good section on DataSource descriptor files in the DataBinding chapter.
In terms of the UI components – the top list shows the list of dataSources, and the Refresh button below it should allow you to refresh that list. The bottom section is a preview of records in the dataSource - so it is populated via a fetch against the dataSource. The Refresh button there refreshes that list of records (so re-fetches, and displays any changes). The “Import” button will regenerate the SQL database table and can be used to import xml-formatted test data into that database table.

#4: The tableName should match the database table name - so if your table is “LOGGING_EVENTS” in your database, that string should be the tableName in the associated dataSource.