Backend to an existing MySQL DB?

robinsz

Hi,

I;d like a quick "yes. no, possibly" answer to my problem ...

Im looking at using some tool (possibly AwareIM, possibly someting else) to build a back-office application to an existing web app that runs off a MySQL db.

We're pretty happy with the user front end we have (php/ajax/jquery etc)

What im trying to build is a fully featured back office app to connect to the existign tables, allow lookup of users, details, management of the inventory, billing etc.

I;d appreciate any inptu on how do-able that is with AwareIM, and the best approach. My fumbling with awareIM so far has failed to produce a simple table view of the users yet, but im ever hopeful 🙂

aware_support

If you want a quick answer then it's "yes" - what else can it be? 🙂

You should be able to easily connect to the existing database tables - this is described in the User Guide. Don't know why you couldn't produce a list of existing users.

robinsz

Probably me just doing someting wrong ... it dicovered the tabel fields, i wrote the fool-proof FIND rule to find all the users, and nothing came out .. I'll try again.

It looks the right sort of tool for what we want to do .. we already have a mid-sized application (20K lines of code) that provides the front end service for web users, our back end is .. hmmm ... not good. Its MySQL based, as usual.

We have another product we sell,built using a simalr tool called "4th Dimension" that provides a great back end, but no web front end. Building a web front end to the 4D app is almost impossible, as 4D uses its own internal DB that we dont want to integrate into out front end app.

Our ideal solution is to write a "work alike" of the 4D application, using AwareIM (or similar) hooked up to ur existing MySQL schema. We have a large enough installed base that we dont want to make too many changes to the schema, but we do want to build a great back end tool, so we can do what is necessary, but we'd prefer to kee schema changes to a minimum.

aware_support

It it discovered the tables OK, then it should work. Maybe the problem is in your query...

Try using the built-in Standard/Search Objects functionality and select the external object from the list - this should display all the data.

robinsz

I just started again with a blank app, added the new object, named the db connection, it found the fields OK, click "primary key" next to the one that is the primary key ...

ran the app, standard, search objects, all objects (select the new object type)

produces a nice form with the right column headers, but no items found 🙁

robinsz

robinsz wrote
I just started again with a blank app, added the new object, named the db connection, it found the fields OK, click "primary key" next to the one that is the primary key ...

ran the app, standard, search objects, all objects (select the new object type)

produces a nice form with the right column headers, but no items found 🙁

interstingly, hitting 'export' produces a nice csv of the full dataset for that table, so it clearly can see them .. just nto displaying them in the form

aware_support

Strange. Does the table say that "no data has been found" or it says that n records have been found but doesn't display them?

Can you attach the output of the Aware IM Server and Tomcat?

What is the structure of this table?

robinsz

aware_support wrote
Strange. Does the table say that "no data has been found" or it says that n records have been found but doesn't display them?

Can you attach the output of the Aware IM Server and Tomcat?

What is the structure of this table?

It says "no data available"

Exception thrown com.bas.connectionserver.server.d Internal error. Error reading business object Table 'dev_live.USERS' doesn't exist

<....>
Web server returned error: Internal error. Error reading business object Table 'dev_live.USERS' doesn't exist

Which is of course perfectly true .. it doesnt exist, the table is 'users' not 'USERS' .. somehow Aware has turned it into upper case .. any clues?

aware_support

Case shouldn't matter - MySQL is not case sensitive. Where is "dev_live" coming from? Then again, how come export in CSV works?

robinsz

dev_live is the external db, holding the users table

MySQL IS case snsitive ..

mysql> select * from USERS;
ERROR 1146 (42S02): Table 'dev_live.USERS' doesn't exist

trust me, 'users' does exist

robinsz

MySQL is case sensitive on table and column names, it is not case sensitive in equality tests eg

select * from users where login='brian';

is the same as

select * from users where login='BRIAN'

both return the same row

aware_support

No, this is not true. I just ran a small test in MySQL 5.0 using mysql utility:

select * from nomination;

and

select * from NOMINATION;

produce the same results. Try it yourself.

Which MySQL version are you using?

robinsz

MySQL 5.0.5

I the test fails on my version ...

I have also just tried it on 3 production servers, 2 running MySQL 5.0, another running MySQL 4, all gave the same results. I've been using MySQL since about .. oh, 1997 or so .. its always been case sensitive AFAIK

You aren't by any chance testing on Windows? I've never tried MySQL on windows, it might be different there ...

verison info from my laptop for perusal

mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Connection id: 343
Current database: dev_live
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.51a-24+lenny1 (Debian)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 9 hours 28 min 36 sec

Threads: 1 Questions: 3009 Slow queries: 0 Opens: 936 Flush tables: 1 Open tables: 64 Queries per second avg: 0.088

aware_support

I AM testing it on Windows. If case sensitivity is explained by the difference in OS, then it's a real bummer. Are there any MySQL settings for this?

I can't remember whether Aware IM changes the case of the tables and database entered when you define the MySQL connection (it shouldn't, in theory). But just in case - have you entered table names in exactly the same case they are in the db?

robinsz

Yes, they are entered exactly as per they shoudl be, lower case

In theory you can set a variable in the mysql conf to force lower case table names ..

however, that would first mean converting all my existing tables to lower case, (some are mixed case, like userRates etc) ... but it wont help us in production, because 90% of the servers we run on we do not have root access to the database, the seing is global, and there is absolutely no way we'll get global config changes on a production server, ever.

Somewhere, AwareIM is uppercasing that table name 🙁

robinsz

aware_support wrote
I AM testing it on Windows. If case sensitivity is explained by the difference in OS, then it's a real bummer. Are there any MySQL settings for this?

I found this:

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

Like I said,it explains it, but it wont help us really, because we'll not be able to get that setting changed on production servers.

aware_support

We'll have a look whether Aware IM upper-cases the table name.

However, the question remains - you said that export works fine, which means that it shouldn't be a problem because exactly the same query is run on the server. If the query works for export it should work for the UI in theory. Where do you get this error message and when?

Maybe it's a different problem related to displaying data in the grid. Have you tried other tables?

robinsz

I have tried other tables, same problem

It does seem to be related soley to the grid, the CSV export worked fine. Why, I have no idea, im not familair with the Aware internals.

aware_support

You mentioned the error message - when are you getting it and where do you see it? On the screen, in Tomcat output, in the Aware IM Server output?

robinsz

The "no data available" is in the grid on the search results screen

presumably you should be able to get the same result hooking it up to any MySQLserver on a linux system?

the "table USERS does not exist" error is inthe tomcat log, acutally, if I paste the full error for you you get the full exception path:

(this paste is for the table addresses, whch AwareIM has chnaged to ADDRESSES

WebServlet URL got request with params:

Exception thrown com.bas.connectionserver.server.d Internal error. Error reading business object Table 'dev_live.ADDRESSES' doesn't exist

com.bas.connectionserver.server.d: Internal error. Error reading business object Table 'dev_live.ADDRESSES' doesn't exist

at com.bas.connectionserver.server.ConnectionFactory.sendMessage(Unknown Source)

at com.bas.webapp.common.WebAppUtils.sendMessageToExecutionEngine(Unknown Source)

at com.bas.webapp.thin.a.c.h.a(Unknown Source)

at com.bas.webapp.thin.a.c.h.a(Unknown Source)

at com.bas.webapp.thin.a.c.h.a(Unknown Source)

at com.bas.webapp.thin.a.b.a(Unknown Source)

at com.bas.webapp.thin.a.b.a(Unknown Source)

at com.bas.webapp.thin.servlets.WebInterfaceURLServlet.doPost(Unknown Source)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188-)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108-)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)

at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)

at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528-)

at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)

at java.lang.Thread.run(Unknown Source)

Web server returned error: Internal error. Error reading business object Table 'dev_live.ADDRESSES' doesn't exist