Ofbiz Trail Guide

@ofbizUrl tag = your ticket to hell

2007-12-06T12:10:00.000-06:00

You are doing development locally and everything looks good, so you plop your code on the dev server and everything goes to shit. Request parameters are all empty when they should be full. You double check all the svn diffs and you didn't miss any code.

Could you be using a mixture of http and https urls? Could be, if you are using the @ofbizUrl tag to build your URLs. Try doing your URLs without this tag. Things might magically start working for you.

Using JDBC to take advantage of Database technology

2007-11-02T10:08:00.000-06:00

abandoned the entity engine and Jumped into JDBC to do this.

Joins two views in a left outer join, meaning that it gets values from the first view even if there is no matching value in the second view.
Uses the COALESCE function of postgres to fill in default values (zero in this case) when values in the second view (the right hand view) are missing

SELECT query1.DIVID,TotalSub,COALESCE(TotalPassed,0) AS TotalPassed,COALESCE(100*(TotalPassed/TotalSub),0) as PERCENT
FROM
(SELECT DIVID, COUNT(t1.PARTY_ID) AS TotalSub
FROM (public.USERS t1 INNER JOIN public.TABLE2 sub ON t1.PARTY_ID = sub.PARTY_ID)
INNER JOIN public.TABLE3 t3 ON sub.PRODUCT_ID = t3.T_ID
WHERE (sub.from_date + (days_to_complete||' days')::INTERVAL > '2007-08-01' AND sub.product_id = 'testCourseOne')
GROUP BY t1.div_num) AS query1
LEFT OUTER JOIN
(SELECT DIVID, COUNT(t1.PARTY_ID) AS TotalPassed
FROM (public.USERS t1 INNER JOIN public.TABLE2 sub ON t1.PARTY_ID = sub.PARTY_ID)
INNER JOIN public.TABLE3 t3 ON sub.PRODUCT_ID = t3.T_ID
WHERE (sub.from_date + (days_to_complete||' days')::INTERVAL > '2007-08-01'
AND sub.product_id = 'testCourseOne' AND sub.completed_date IS NOT NULL)
GROUP BY t1.div_num) AS query2
ON query1.DIVID = query2.DIVID

adjust allowed login attempts

2007-08-23T12:41:00.000-06:00

How do I adjust the number of allowed login attempts in ofbiz?

Edit the value for "max.failed.logins" in the file security.properties

If a user is already locked out, you can unlock them in webtools-->find-->[enter user login] --> Lookup Party--> find disabled account -->edit

How do I turn this feature off altogether?

Setting max.failed.logins to zero should disable this feature.

Mixing AND and OR conditions in entityConditionList

2007-08-22T12:32:00.001-06:00

How does one make a query using findByCondition that has both OR and AND clauses?

hint: do a search on .OR in your ofbiz code. This will lead you to
the file lookupBulkAddProducts.bsh

with the following code:


conditionList.add(new EntityExpr("productTypeId", EntityOperator.NOT_EQUAL, "AGGREGATED"));
 than isVirtual != "Y".
// we consider those products to be non-virtual and hence addable to the order in bulk
orConditionList.add(new EntityExpr("isVirtual", EntityOperator.NOT_EQUAL, "Y"));
orConditionList.add(new EntityExpr("isVirtual", EntityOperator.EQUALS, "N"));
orConditionList.add(new EntityExpr("isVirtual", EntityOperator.EQUALS, null));

orConditions = new EntityConditionList(orConditionList, EntityOperator.OR);
conditions = new EntityConditionList(conditionList, EntityOperator.AND);

mainConditionList.add(orConditions);
mainConditionList.add(conditions);
mainConditions = new EntityConditionList(mainConditionList, EntityOperator.AND);

productList = delegator.findByCondition("Product", mainConditions, UtilMisc.toList("productId", "brandName", "internalName"), UtilMisc.toList("productId"));

pretty self-explanatory. Group the OR statements in their own entityConditionList, the AND statements in their own, and combine the two in a master EntityConditionList (using the EntityOperator.AND)
so, the above would result in something like WHERE (a OR b OR c) AND d

how to iterate maps in FTL

2007-08-04T06:18:00.001-06:00

instead of <#list> ... </#list>

you can try
<#assign mList=courseMap.keySet()/>

<#list mList as key>
...
</#list>

and to address members of the map:

<#list mList as key>

${courseMap[key]}

</#list>

FTL date formats

2007-08-04T06:17:00.000-06:00

Here is a page of information on how to format dates in FTL:

Postgresql Timestamp + interval

2007-08-02T11:46:00.000-06:00

Jumping into SQL with EntityWhereString, I was trying to do some date arithmetic. I had a timestamp and an integer number of days. I wanted to add the number of days to the timestamp and work with the resulting timestamp.

the Postgresql syntax for this is
SELECT timestamp '2001-09-28 01:00' + interval '23 hours' from myReportsso I got this far:select tx_stamp + interval '23 days' from myReportsand that worked but when I tried using my integer column "days_to_complete" for the interval, the syntax wasn't clear.
It turns out I needed to concatenate cast my days_to_complete into a string:days_to_complete||' days'and then cast the resulting string into an INTERVAL:select tx_stamp + (days_to_complete||' days')::INTERVAL from myReportsand that worked.

ofbiz to Excel

2007-07-20T14:42:00.000-06:00

Export results from ofbiz to Excel:

There is an example of how to do this in the Facilities application.
the example is at https://localhost:8443/facility/control/ViewFacilityInventoryByProduct?facilityId=WebStoreWarehouse

Excel can easily open a well formed XML document and preserve the color and style of the original report.
To output in XML: In the view-map tag in controller.xml change "type=screen" to "type=screenxml" and you will get xml output!

All your FTL code must be in valid XML. That means that <link>

<link rel='stylesheet' href='/assets/css/style.css' type='text/css'>

is not valid but

<link rel='stylesheet' href='/assets/css/style.css' type='text/css' />

is valid.
Other tags to check:

area
input
img
param
is not valid. Use &160;instead

then, change the attribute content-type="text/xml" to content-type="application/ms-excel"
and users will be prompted to download a file instead of viewing it in the browser. if you change the link and the view-map to control/myPage.xls instead of control/myPage, then windows will offer to open the file with MS Excel.
Note:
Using a https to do this in Internet Explorer may return the message that "Internet explorer cannot download [yourReport] from localhost". If that is the case, you can turn off https for this request. If that is not an option, you must add a header to the response somehow. The view-map tag does not support other headers besides content-type, so this header gets set in the beanshell file with lines like:
fileName = "courseSetCompletionReportsExcel.xls";

// the following instruction prompts the user with option to open report in Excel (instead of in the browser):

response.setHeader( "Content-disposition", "attachment; filename=\"" + fileName + "\"");

//the following seems to have no effect, probably because my headers are set this way somewhere else:
response.setHeader("Cache-Control", "cache");

// next two lines fixed the refusal to download issue:
response.setHeader("Cache-Control", "must-revalidate");
response.setHeader( "Pragma", "public" );

turn off ServerHitBin complaints

2007-07-17T18:14:00.001-06:00

When ofbiz falls down a flight of stairs and then writes a novel about it, one of the themes is often ServerHitBin. Message associated with the ServerHitBin error:

If you turn of persistance of visits you should also turn off persistence of hits.

I don't know how to configure persistance of either one, but I'd sure like to. So, how?

I see that this message comes from the ServerHitBin.saveHit method, as a result of checking UtilProperties.propertyValueEqualsIgnoreCase(serverstats ... something)
which, after some grep practice, turns out to be in framework/webapp/config/serverstats.properties
where you can adjust how carefully the webapp tracks visits and hits. I notice some of these options were set to the mysterious troolean value of ftrue.

Now that I turned all the stats collection off, I get no more ServerHitBin errors

EntityWhereString

2007-07-16T10:46:00.000-06:00

EntityWhereString simply allows you to add custom SQL syntax to the where clause for an entity operation. It extends EntityCondition so it can be used in the same way.

List pList = new ArrayList();
EntityWhereString conditions = new EntityWhereString("char_length(last_Name)>8");
EntityConditionList assocExprList = new EntityConditionList(UtilMisc.toList(conditions),EntityOperator.AND);

pList = delegator.findByCondition("Person",assocExprList,null,null);

you must use database names and not entity engine names. that is, if the column name in the database is "First_Name" then you can't use FirstName etc.
you can use database specific functions like char_length(), substr(), and the ever useful

EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');

Custom functions could be used here as well.

Consider that this would allow SQL-injection if your application did not adequately scrub parameters coming from the client. This, I suppose, is the reason for this disclaimer in the source code:

Encapsulates SQL expressions used for where clause snippets.
NOTE: This is UNSAFE and BREAKS the idea behind the Entity Engine where
you avoid directly specifying SQL. So, KEEP IT MINIMAL and preferably replace
it when the feature you are getting at is implemented in a more automatic way for you.

conditions with EntityWhereString can be strung together with other normal sorts of EntityConditions in the EntityConditionList:

EntityConditionList assocExprList = new EntityConditionList(UtilMisc.toList(conditions,exp1),EntityOperator.AND);

where "conditions" is a EntityWhereString and exp1 is an EntityExpr

Does EntityWhereString work on entity-views??
Hell Yes! Even though the entity-view does not exist as far as the database is concerned, ofbiz passes the SQL where string back to the underlying table. Sweet.

access huge entity-views in entity maintenance

2007-07-13T09:44:00.000-06:00

My co-worker showed me this.

Some of my views are too huge for the entity maintenance screen and there is no "find" link to go straight to a subset of one of these views (There is only a link to "all"). If I click "all", I get an "out of memory" error in OFBIZ. Even if I didn't get that error, I don't want to wait 15 minutes for my view to show up.

A way around this is to right-click on the "all" link and select "copy link location" and paste the results into the address bar of the browser. Then, delete the text after your entity name and press enter. Then you will see a find screen with no records for your entity view and be able to look stuff up without calling up the entire view.

Increasing ViewSize in EntityEngine

2007-07-09T14:30:00.000-06:00

10 records at a time when viewing an entity-view? Why and how can I change this?

Of course, you can change it in the address bar. Just change the settings for VIEW_SIZE and VIEW_INDEX to navigate around your entity:

https://localhost:8443/webtools/control/FindGeneric
?entityName=CombinedView&find=true&VIEW_SIZE=100&VIEW_INDEX=7660

or, to change the default behavior, I did a search on VIEW_SIZE in *.xml in the ofbiz workspace and I found nothing. A search for VIEW_SIZE in *.* led me to findGeneric.bsh, where I found the code:

String viewSizeString = (String)request.getParameter("VIEW_SIZE");
if (viewSizeString == null || viewSizeString.length() == 0) {
viewSizeString = "10";
}

and changed the "10" to "50" and now my view entities give me a nice fat 50 rows per page. Of course, this will be blown away next time I update ofbiz, so it may be easier to just edit the address bar.

SQL-injection and OFBIZ

2007-07-08T19:08:00.000-06:00

One of the great things about the OFBIZ entity engine is that it is well nigh impossible to make SQL-injections against it.

None of the web-application security scans I've done against OFBIZ applications have ever yielded a SQL injection vulnerability and it isn't because of careful parameter scrubbing. Looking at the code, I can't really see how any SQL injection could work. I'm not ready to declare that OFBIZ is completely free of SQL injection problems, but it is beyond me to find any.

Of course, one gives this protection up when switching to JDBC and the limitations of the entity engine may just induce one to do that. Take security into consideration when making that decision.

GenericPK

2007-07-05T19:57:00.000-06:00

A google search for ofbiz GenericPK will return the code for this class, but no explanation of what it is for. All the code examples in the OFBIZ codebase seem to use it in conjuction with GenericValue so that one may use a GenericValue retrieved from the EntityEngine to populate a an arbitrary or Generic PK for use with an operation like FindByPrimaryKey or RemoveByPrimaryKey. In other words, you got some data from somewhere and you would like to run a "PrimaryKey" operation. So first convert the data to a GenericPK and then use that for your operation.

Can I Has Cursor?

2007-07-05T19:31:00.000-06:00

There is something like a cursor in ofbiz. It is known as an EntityListIterator. When you do findAll or findByAnd() on an entity, it returns a list into memory and if the result set is too large, you will have performance problems. That is, if you consider death to be a performance problem. Instead of a List, you can get the equivalent of a cursor that you can loop through and retrieve your data. EntityListIterators only work "byCondition". That is, you have to write the command differently.

personList = delegator.findByAnd("Person", utilMisc.toMap("lastName","Smith"));

would become

EntityExpr entityExpr = new EntityExpr("lastName",EntityOperator.EQUALS "Smith");
personELI = delegator.findListIteratorByCondition("Person",entityExpr);

how to deal with blank rows on multi-forms

2007-05-14T13:00:00.000-06:00

If you use simple methods to create multi-row form, you might notice that if there is an error in one row, any blank rows in the form will cause validation errors. You might think that the "if-not-empty" tag in the SimpleMethods.xml definition would cause the form to ignore blank rows, but it doesn't. The reason is that by default, a multi-form is treated as one big transaction. When one of the rows fails validation, they all do.

The solution is to use the global-transaction="false" attribute in the event tag in controller.xml . This tells the event called that you want the form to be processed as individual lines instead of as one big form.

Test your services

2007-05-09T12:56:00.000-06:00

A great feature in webtools lets you fire off your services individually and give them parameters of your own choosing.

go to webtools --> schedule job --> fill in the name of your service (as defined in one of the xml files in the serviceDef directory of your application)

the application will then ask you for the parameters that are defined as attributes in the service definition. The job I scheduled is defined in groups.xml and calls three services that all have attributes defined. When I run this job thru the webtools "schedule job" tool, it asks me for all the parameters for all three services.

blank src="" causes double request processing

2007-05-02T11:00:00.000-06:00

Twice in the past month, I've been beplexed by http requests being processed twice or more, once with expected parameters, and then again with no parameters. This caused all sorts of unexpected behavior. Request processing even seemed to be triggered by trying to "view source" in Firefox.

I visited my doctor to see if I needed my medication adjusted again, but all other aspects of reality seemed to be reasonable, so I looked for a solution by lobotimizing my application and adding pieces back in again until I noticed the behavior. It came back after adding a chunk of code back into an ftl page. It turns out that this behavior was caused by some image tags with blank src attributes. If I have IMG SRC="" or even IMG SRC="#" the requests get called twice.

turning down the volume on logging messages

2007-02-14T14:04:00.000-06:00

I had a overly chatty console log. Messages I was interested in would zip by before I could read them because of some job poller logging thousands of INFO statements.

To shut him up, I edited debug.properties (ofbiz/framework/base/config/debug.properties)
and replaced the ALL with WARN in the following lines:

# Set root logger priority and appender
log4j.rootLogger=WARN, stdout, file, css

# Special OFBiz log4j configuration - set all top levels to true and set root to ALL
log4j.logger.NoModule=WARN
log4j.logger.org.ofbiz=WARN

ahhhhh. peace and quiet.

looping through context object

2007-02-09T11:24:00.000-06:00

Set z = context.entrySet();
Iterator it = z.iterator();
while (it.hasNext()) {
Map.Entry pairs = (Map.Entry)it.next();
Debug.log(pairs.getKey() + " = " + pairs.getValue());
}

context and globalContext

2007-01-08T11:28:00.000-06:00

What is the difference between context and global context?

This applies to Screen Widgets and context and global context are alive while a screen is being built.

This ofbiz developer put it well:

Each piece of a screen has its own context, and there is also a global context
available to all scopes. This is only available during the screen
rendering process. Once the screen is rendered, there is no more
context.

In general the only way to keep variables across
multiple requests is to use the session.

bypass authentication in local ofbiz development environment

2006-12-18T18:26:00.000-06:00

I must spend 1 hour per day logging into my local ofbiz setup. I never want to be asked again.

To get around this, I edited LoginWorker.java and hard-coded the username and password.

Hard code the credentials in both the login() method and the checkLogin() method to totally bypass the login screen.

hard-code only in the login method and the login screen will still appear, but will accept blank credentials, which might be what you want when testing for application flow.

Code for checkLogin() method:
if (userLogin == null) {
// added to circumvent login:
username = "admin";
password = "ofbiz";
....

Code for login() method:
// comment out existing username and password definition:
// String username = request.getParameter("USERNAME");
// String password = request.getParameter("PASSWORD");
String username = "admin";
String password = "ofbiz";

rebuild ofbiz and enjoy the extra seconds!

pg_dump problem solved

2006-12-08T11:37:00.000-06:00

In setting up ofbiz and postgres on windows, I found that PGAdmin totally barfed when trying to run the SQL file that I had created from a pg_dump command.

It returned errors like "ERROR: syntax error at or near "\" at character 387817 "

This was the result of the default behaviour of pg_dump using COPY commands, which PGAdmin apparently can't stomach. The command line statement,
psql -U postgres -f pgdump.sql reported no errors but FAILED to load a significant portion of the data. I find the limited error reporting very relaxing!

I found there is a command line option to pg_dump to dump the data using INSERT statments instead:
-d, --inserts dump data as INSERT, rather than COPY, commands

so, I did

pg_dump -d -U postgres > pgdump34.sql

from the Windows cmd line and got SQL statements that where recognizable to me and to PGAdmin III

ofbiz and eclipse

2006-12-07T21:55:00.000-06:00

There are four basic things you might like to do in eclipse: manage svn tasks, Run Ant builds, start and stop ofbiz, and debug java files. All of these are within your reach, but the number of options might send you back to typing "startofbiz" at the command line. Ofbiz has a wiki entry about using eclipse and that is a good starting point, even though it doesn't address all the painful hassles you may encounter.

It is really nice to have svn commands right there in your eclipse file navigator. Here is how to install subclipse. Visit http://subclipse.tigris.org/s to get the location of the update site and then open help --> software updates-->find and install --> search for new features --> new remote site --> enter name and http address and follow steps.

which version? I have eclipse 3.2 and I installed subclipse 1.1.x and had no problems
how to connect to an existing project repository? This couldn't be simpler. Right click in navigation panel. Select import from context menu. Choose "Checkout projects from svn and enter the svn url in the space provided

ANT. Really all you need to do with ANT is load your project's build.xml file into eclipse's excellent ANT tool and run your ANT target from there.

open Ant view
browse to the build.xml file for ofbiz
click the green arrow to run the default target
double-click run-install or run install seed to set up the default passwords and other initialization stuff
browse to the build.xml file for your project
click the green arrow to run the default target for your project

starting and stopping the whole damn ofbiz server from within eclipse. Follow the directions in the wiki and note that:

all the classpath settings in your project's build path that allow you to EDIT your project in eclipse need to now show up in the project's run path to allow you to RUN your project in eclipse. Classpath errors can either mean missing libraries or they can mean conflicting libraries.
Your search - ofbiz blooooody entityresolver2 - did not match any documents. Errors like "java.lang.NoClassDefFoundError: org/xml/sax/ext/EntityResolver2" could mean eclipse is finding a jar file with a package that is the same as the one that the missing class is really in. It is finding the wrong jar file FIRST. I got around my issue by changing the order of library imports in the classpath window.
I had to copy RemoteDispatcherImpl_Stub.class from \ofbiz\framework\service\build\classes\org\ofbiz\service\rmi TO \ofbiz\bin\org\ofbiz\service\rmi

debugging then comes fairly naturally after getting it running inside eclipse. You can only debug java files. I would love to know a way to debug minilang, bsh and ftl.

running ofbiz with postgresql on windows

2006-12-06T15:45:00.000-06:00

These are the steps I took to get ofbiz talking to a postgresql database running on Windows:

Download + install postgres for Windows
update /data/pg_hba.conf to include the following lines.
local all all trust
host all all 127.0.0.1 255.0.0.0 trust
Download appropriate driver.
fix build.xml to add your driver's jar file to the classpath or replace existing postgres driver with correct one for the version you just installed.
Install postgres, making note of username and password. The installation will allow you to name a postgres user. I named mine "postgres". The installation will give you a PGAdmin executable which is useful for doing operations which you aren't allowed to do as admin user.
initialize database with ofbiz tables, preferably from a pgdump.sql file, alternatively by using the xml import tool.
change entityengine.xml to reflect your use of a local windows postgres

link to entityengine.xml example
url looks like: jdbc:postgresql://127.0.0.1:5432/postgres

Read this for instructions on configuring pg_hba.conf

"host all all ... trust" means no password is checked at all for connects for the hosts indicated, so if you are getting "password failed" in your log files, it isn't reading pg_hba.conf correctly.

For most changes to the .conf files, you can tell postgresql to read them again with the command pg_ctl reload -D D:\psql\data. Certain settings in the .conf files require a postgresql restart which is best done from PGAdmin III. Stopping the service and trying to start it again can turn into a major hassle because this amounts to starting postgresql with administrative privileges. When I did it this way, I couldn't restart the service except by rebooting.

You will also need to run a ant run-install-seed to put default passwords (admin,ofbiz)

getting your data

2006-11-29T16:19:00.000-06:00

People learning ofbiz will probably first be looking for equivalents to the SQL select statements or hibernate calls they are used to. The GenericDelegator object does this. Reading this post requires your understanding that calls to delegator aren't really like select statements at all because these methods return a List of objects and not a cursor. and they aren't like hibernate calls either, because they don't return a List of the specific object type, but a List of GenericValue objects.

select * from table : you are looking for delegator.findAll()
List peopleList = delegator.findAll("PicnicPeople"); will get every record in the PicnicPeople entity. It will come to you as a List of GenericValue objects.

select * from table where... : When you want to limit the records returned, delegator.findByAnd() is what you want. findByAnd takes a Map (or List) of the field names and values you wish to add to the where clause. This is kind of nice because you don't have to worry about constructing Where... AND.... AND.... clauses. There is a utility called UtilMisc.toMap() to construct this map that you will see used a lot.
Map fieldMap = UtilMisc.toMap("name","Fred");
List peopleNamedFred = delegator.findByAnd("PicnicPeople", fieldMap);

select * from table1, table2 where table1.pk = table2.fk : outer joins can be accomplished by delegator.getRelated()
if you already have a peopleList populated from the previous example,
aPerson = (GenericValue)peopleList.get(1);
List peopleDateList = delegator.getRelated("PicnicDates",aPerson);
would get back a List containing all the picnic dates that aPerson.
to get the entire list of dates for an entire List of people, you would do a loop through peopleList and perform delegator.getRelated(PicnicDates, (GenericValue)peopleList.get(x)) for each entry in the list. There are other ways to do joins: dynamic views and entity views, but I'm just talking about delegator today.

select * from table1 where name LIKE 'Amy%' wild card queries can be used with delegator.findByLike() as in :
delegator.findByLike(PicnicPeople", UtilMisc.toMap("name","Amy%"));

Which version of ofbiz am I running?

2006-11-29T09:44:00.000-06:00

You'd expect the version number to be in the Readme file, but it isn't.
One way to check the exact revision is to look in a file in the .svn directory called entries. Each entry will show the revision number (467725) and a date for that revision.

How to issue a query from simple methods?

2006-11-28T17:27:00.000-06:00

I am tasked with what seems like a simple form: 20 checkboxes based on a database lookup that updates a different database table.

So, first step is to see if I can create a simple method that gets me a list of maps that represents all of the rows in my table. Gotta stop saying "table". Tables are called entities in OFBIZ. The entity is Pmu and contains the fields PmuId, ecmId, and label.

Simple question. How do I do a query " Select * from Pmu " ?
the closest thing to a sql query is something called entity-condition:
<simple-method name="getPmus" description="get pmu list">
<entity-condition name="Pmu">
</entity-condition>
</simple-method>

so, how do I do something like " Select * from Pmu where ecmId = 5 " ?

<simple-method name="getPmus" description="get pmu list">
<entity-condition name="Pmu">
<condition-expr name="ecmId" env-name="parameters.ecmId" operator="equals">
</condition-expr>
</entity-condition>

where in the heck does the parameters object come from?
well, in this documentation of minilang, it states that by default, the incoming context will be mapped to the "parameters" variable. Nice. So ecmId will come in with the context and everything will be fine.

After I add the above code to simple-methods.xml, I will have available to me a List object containing a bunch of objects of type Pmu. In my next post I will try and make use of those Pmu objects in my form widget!