Ron Gines
|
| Posted: 05/15/2002, 6:59 PM |
|
I've been testing CC for one of my clients and have to say that I am extremely impressed. In a matter of a handful of hours I have managed to run through the tutorials and build my own 'site' to connect to a test copy of my one of my client's production databases.
The client database is a copy of one that is running connected to their plant floors across the country. The tables are generally updated from other systems that are running on the plant floors. As a result, the Primary Keys are not autoincremented and I also have no capabilities to change the structure of this database.
Everything works fine with the exception of the Insert functions. I've scanned through the available forums for similar issues and have figured out that CC assumes auto-incremented PKs or that a PK is pre-generated through an event.
What I believe I need to do is be able to bring up a blank record form without CC first trying to access the database. Even when during an insert, before displaying the record form, CC is trying to hit the database with PK values of NULL as follows:
SQL - select * from QA_KEY_CHKPT_ASNMT where QA_KEY_CHKPT_C=Null and PP_N=Null
I am hoping/assuming that there is an easy way around this that I am missing. If I can get this to work, then I'm sure that I can get my client to purchase several copies of CC ... if not ... then it is back to the likes of Net.Data, hand written ASP and/or WebFocus (ughhh!).
Thanks in advance!
|
|
|
 |
Nicole
|
| Posted: 05/16/2002, 2:58 AM |
|
Ron,
of course there's workaround for your issue that requires (on my opinion) little part of manual coding.
In CC add key fields to the form and mark them as 'Key'. This let you preserve default update and delete functionality. Generate the code and once you're sure that form design won't change create Custom Action event.
(BTW, once it is created changes made on the form, e.g. change field type won't be applied to generated code, you should do it manually in event code or re-obtain code and them customize it again)
Obtain code for it. What you should do is modify insert query in order to include key fields and their proper value to sql statement.
And some explanation about NULL key values. CC Record form is based on following sql query:
select * from table_name where primary_key= any_value
It is the same both for insert and update mode except "any_value" value.
take a look at generated code and you'll see that in case primary key value is passed through url this value is used to retrieve record, while no value is passed Where clause is: primary_key = Null
in order to select empty recordset.
|
|
|
|
Tony White
|
| Posted: 05/17/2002, 6:18 AM |
|
Assume a Grid/Record form in JSP.
In the Event/Open for the Record form insert the following code where KEY represents PK:
if ("KEY=Null".equals(sWhere)) {
sWhere = "KEY is Null";
}
DB2 doesn't like the clause "KEY=Null", but rather likes "KEY is Null". That takes care of the display problem.
Now, for the Insert portion of the problem, I use a UUID algorithm to generate a key, and then copy the insert code from the generated page to the Event/Insert of the Record form and modify slightly. My final code is:
Added -> String tmpkey = getUUID("00:14:34:56:78:90");
Added -> int key = tmpkey.hashCode();
sSQL = "insert into OUTLINE (" +
Added -> "key," +
"STATUS," +
"DESCRIPTION," +
"PARENT," +
"NODEID)" +
" values (" +
Added -> key + "," +
toSQL(fldSTATUS, Text_TYPE) + "," +
toSQL(fldDESCRIPTION, Text_TYPE) + "," +
toSQL(fldPARENT, Number_TYPE) + "," +
toSQL(fldNODEID, Text_TYPE) +
")";
I regenerate and everything works fine! Hope this helps!
|
|
|
|
Ron Gines
|
| Posted: 05/17/2002, 8:04 AM |
|
>DB2 doesn't like the clause "KEY=Null", but rather likes "KEY is Null".
>That takes care of the display problem.
Thanks for the observation. I completely missed for forest due to all af the tree <g>. Changing the syntax fixed the display problem.
>In the Event/Open for the Record form insert the following code
>where KEY represents PK:
>if ("KEY=Null".equals(sWhere)) {
>sWhere = "KEY is Null";
>}
For others on the forum who may be reading this, the syntax under ASP is ...
if sWhere = "KEY=Null" then sWhere = "KEY is Null"
|
|
|
|
|
