MySQL with BC4J need to knows

If you want to use MySQL with BC4J and you are only familiar with Oracle databases then you should know the following. Read for all MySQL details my previous blog over MySQL too. If you know more tricks how you can use mysql with bc4j let me know.
If you want use bind variables then you should use ? and not named bind variables like :1 or :customerId. For example in viewobject impl

public void selectById(BigDecimal id, boolean executeQuery)
{
  setWhereClause("ID = ?");
  setWhereClauseParams(new Object[]{id});
  if (executeQuery) {
    executeQuery();
  }
}

MySQL does not know sysdate you have to use Now()
select A.text from A where A.date_from > now()

In Oracle SQL you can outer join a table bij adding (+) at the right place in the where clause. In MySQL you have to use Left join or Right Join. For example
select A.text
, B.text
, C.text
from A left join B on B.ID = A.B_ID
, C
where A.C_ID = C.ID

If you have a date as column type in MySQL table then BC4J generates the entity attribute as string. You have to change this to timestap.

If you have a blob as table data type then BC4J uses a string as java type. You can change this to BLOBdomain but this doesn't work. You have to program a workaround here some which you can use to store a blob and how to retrieve.

    public BigDecimal putBlobData (File file) throws FileNotFoundException,
                                                    IOException {
        BigDecimal blobId = null;
        PreparedStatement stmt = applicationModule.getDBTransaction().createPreparedStatement("SELECT last_insert_id()",1);
        try {
          Connection conn = stmt.getConnection();
          stmt.close();
          stmt = conn.prepareStatement("insert into Blobdata(BLOBDATA,CRE_USER_CODE,CRE_DT) values (?,?,?)");
          InputStream isFile = new FileInputStream(file);
          stmt.setBinaryStream(1,isFile, (int)(file.length()));
          stmt.setString(      2,"mhs");
          stmt.setDate(        3,new Date(new java.util.Date().getTime()));
          int count = stmt.executeUpdate();
          conn.commit();
          stmt.close();
          isFile.close();
          stmt = conn.prepareStatement("SELECT last_insert_id()",1);
          stmt.execute();
          ResultSet rs = stmt.getResultSet();
          if ( rs != null ){
            rs.first();
            blobId = rs.getBigDecimal(1);
          }
          stmt.close();
       
        } catch ( SQLException e) {
            e.printStackTrace();
        } finally {
        }
        return blobId;
    }

To retrieve a blob use this

    public static InputStream getBlobInputStream(BigDecimal id, MhsServiceImpl service){
   
        String sqlQuery = "SELECT blobdata FROM blobdata WHERE id = ?";
        byte[] bytes = null;  
        String description = "";
        ResultSet rs = null;
        Blob blob = null;
        InputStream is = null;

        PreparedStatement stmt = service.getDBTransaction().createPreparedStatement("SELECT last_insert_id()",1);
        try {
          Connection conn = stmt.getConnection();
          stmt = conn.prepareStatement(sqlQuery);
          stmt.setBigDecimal(1,id);
          rs = stmt.executeQuery();
          ResultSetMetaData md = rs.getMetaData();
          while (rs.next()) {
              blob = rs.getBlob("blobdata");
              is = blob.getBinaryStream();

          }
         
        } catch(SQLException e){
           e.printStackTrace();
        }
        return is;

    }