How to configure OFBiz database using MS Sql Server 2008 R2

[Rafael Rondon]

Hello,

I need help to configure and can create me OFBiz database in MS Sql Server
2008 R2. I download the jdbc v2 and v3, with none working. Copy it to
"D:\ofbiz\ofiz-trunk-20110705\framework\entity\lib\ jdbc".

Also edit the file
"D:\ofbiz\ofbiz-trunk-20110705\framework\entity\lib\jdbc\entityengine.xml"
with the following configuration:

    entity-eca-reader="main" entity-group-reader="main" <delegator
distributed-cache-clear-enabled="false"> entity-model-reader="main"
name="default"
        group-name="org.ofbiz" <group-map datasource-name="localmssql"/>
        group-name="org.ofbiz.olap" <group-map
datasource-name="localmssqlolap"/>
        group-name="org.ofbiz.tenant" <group-map
datasource-name="localmssqltenant"/>
...
    <Datasource name = "localmssql"
            helper-class = "org.ofbiz.entity.datasource.GenericHelperDAO"
            schema-name = "dbo"
            field-type-name = "mssql"
            check-on-start = "true"
            add-missing-on-start = "true"
            join-style = "ansi"
            alias-view-columns = "false"
            use-fk-INITIALLY-deferred = "false">
        reader-name="seed"/> <read-data
        reader-name="seed-initial"/> <read-data
        reader-name="demo"/> <read-data
        reader-name="ext"/> <read-data
        <Inline-jdbc
                jdbc-driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

                jdbc-uri = "jdbc: sqlserver: / / localhost: 1433;
databaseName = ofbiz; SelectMethod = cursor;"
                jdbc-username = "ofbiz"
                jdbc-password = "ofbiz"
                isolation-level = "ReadCommitted"
                pool-minsize = "2"
                pool-maxsize = "250"
                time-between-eviction-runs-millis = "600000" />
        <! - <jndi-jdbc Jndi-server-name="default"
jndi-name="comp/env/jdbc/xa/localmssql" isolation-level="ReadCommitted"/> ->
<! - Orion Style JNDI name ->
    </ Datasource>

Please tell me I'm doing wrong or I'm missing, the connection to the
database is local and is the Express version, and tried putting in the
connection URL:
• Localhost
• Localhost: 1433
• Localhost: 1791
• The name of my PC, "\" and SQLExpress Instance.

No way you can connect. I have found no support for this connection.


Regards,


Rafael

[Jeroen van der Wal]

We got it working in SQL Server 2008 R2

- add sqljdbc4.jar to /framework/entity/lib/jdbc folder
- create a user ofbiz with password ofbiz (as in this example)
- make sure you create the database on the sql server and grant the
user ofbiz to create tables.

Here's a snippet of entityengine.xml in /framework/entity/config (it
looked like you copied it to the wrong folder):

    <datasource name="localmssql"
            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
            schema-name="dbo"
            field-type-name="mssql"
            check-on-start="true"
            add-missing-on-start="true"
            join-style="ansi"
            alias-view-columns="false"
            use-indices-unique="false"
            use-fk-initially-deferred="false">
        <read-data reader-name="seed"/>
        <read-data reader-name="seed-initial"/>
        <read-data reader-name="demo"/>
        <read-data reader-name="ext"/>
        <inline-jdbc
                jdbc-driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"

jdbc-uri="jdbc:sqlserver://ams-s-sql08.ecp.loc:1433;databaseName=ofbiz;SelectMethod=cursor;"
                jdbc-username="ofbiz"
                jdbc-password="ofbiz"
                isolation-level="ReadCommitted"
                pool-minsize="2"
                pool-maxsize="250"
                time-between-eviction-runs-millis="600000"/>
        <!-- <jndi-jdbc jndi-server-name="default"
jndi-name="comp/env/jdbc/xa/localmssql"
isolation-level="ReadCommitted"/> --> <!-- Orion Style JNDI name -->
    </datasource>

Hope this helps.

--
Jeroen van der Wal
Stromboli b.v.
[phone]


On Mon, Jul 11, 2011 at 10:35 PM, Rafael Rondon <[hidden email]> wrote:

>
> Hello,
>
> I need help to configure and can create me OFBiz database in MS Sql Server
> 2008 R2. I download the jdbc v2 and v3, with none working. Copy it to
> "D:\ofbiz\ofiz-trunk-20110705\framework\entity\lib\ jdbc".
>
> Also edit the file
> "D:\ofbiz\ofbiz-trunk-20110705\framework\entity\lib\jdbc\entityengine.xml"
> with the following configuration:
>
>    entity-eca-reader="main" entity-group-reader="main" <delegator
> distributed-cache-clear-enabled="false"> entity-model-reader="main"
> name="default"
>        group-name="org.ofbiz" <group-map datasource-name="localmssql"/>
>        group-name="org.ofbiz.olap" <group-map
> datasource-name="localmssqlolap"/>
>        group-name="org.ofbiz.tenant" <group-map
> datasource-name="localmssqltenant"/>
> ...
>    <Datasource name = "localmssql"
>            helper-class = "org.ofbiz.entity.datasource.GenericHelperDAO"
>            schema-name = "dbo"
>            field-type-name = "mssql"
>            check-on-start = "true"
>            add-missing-on-start = "true"
>            join-style = "ansi"
>            alias-view-columns = "false"
>            use-fk-INITIALLY-deferred = "false">
>        reader-name="seed"/> <read-data
>        reader-name="seed-initial"/> <read-data
>        reader-name="demo"/> <read-data
>        reader-name="ext"/> <read-data
>        <Inline-jdbc
>                jdbc-driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
>
>                jdbc-uri = "jdbc: sqlserver: / / localhost: 1433;
> databaseName = ofbiz; SelectMethod = cursor;"
>                jdbc-username = "ofbiz"
>                jdbc-password = "ofbiz"
>                isolation-level = "ReadCommitted"
>                pool-minsize = "2"
>                pool-maxsize = "250"
>                time-between-eviction-runs-millis = "600000" />
>        <! - <jndi-jdbc Jndi-server-name="default"
> jndi-name="comp/env/jdbc/xa/localmssql" isolation-level="ReadCommitted"/> ->
> <! - Orion Style JNDI name ->
>    </ Datasource>
>
> Please tell me I'm doing wrong or I'm missing, the connection to the
> database is local and is the Express version, and tried putting in the
> connection URL:
> • Localhost
> • Localhost: 1433
> • Localhost: 1791
> • The name of my PC, "\" and SQLExpress Instance.
>
> No way you can connect. I have found no support for this connection.
>
>
> Regards,
>
>
> Rafael

[Jeroen van der Wal]

Here's a link to the JDBC V4 driver:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11774
--
Jeroen van der Wal
Stromboli b.v.
[phone]



On Mon, Jul 11, 2011 at 10:55 PM, Jeroen van der Wal
<[hidden email]> wrote:

> We got it working in SQL Server 2008 R2
>
> - add sqljdbc4.jar to /framework/entity/lib/jdbc folder
> - create a user ofbiz with password ofbiz (as in this example)
> - make sure you create the database on the sql server and grant the
> user ofbiz to create tables.
>
> Here's a snippet of entityengine.xml in /framework/entity/config (it
> looked like you copied it to the wrong folder):
>
>    <datasource name="localmssql"
>            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
>            schema-name="dbo"
>            field-type-name="mssql"
>            check-on-start="true"
>            add-missing-on-start="true"
>            join-style="ansi"
>            alias-view-columns="false"
>            use-indices-unique="false"
>            use-fk-initially-deferred="false">
>        <read-data reader-name="seed"/>
>        <read-data reader-name="seed-initial"/>
>        <read-data reader-name="demo"/>
>        <read-data reader-name="ext"/>
>        <inline-jdbc
>                jdbc-driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
>
> jdbc-uri="jdbc:sqlserver://ams-s-sql08.ecp.loc:1433;databaseName=ofbiz;SelectMethod=cursor;"
>                jdbc-username="ofbiz"
>                jdbc-password="ofbiz"
>                isolation-level="ReadCommitted"
>                pool-minsize="2"
>                pool-maxsize="250"
>                time-between-eviction-runs-millis="600000"/>
>        <!-- <jndi-jdbc jndi-server-name="default"
> jndi-name="comp/env/jdbc/xa/localmssql"
> isolation-level="ReadCommitted"/> --> <!-- Orion Style JNDI name -->
>    </datasource>
>
> Hope this helps.
>
> --
> Jeroen van der Wal
> Stromboli b.v.
> [phone]
>
>
> On Mon, Jul 11, 2011 at 10:35 PM, Rafael Rondon <[hidden email]> wrote:
>>
>> Hello,
>>
>> I need help to configure and can create me OFBiz database in MS Sql Server
>> 2008 R2. I download the jdbc v2 and v3, with none working. Copy it to
>> "D:\ofbiz\ofiz-trunk-20110705\framework\entity\lib\ jdbc".
>>
>> Also edit the file
>> "D:\ofbiz\ofbiz-trunk-20110705\framework\entity\lib\jdbc\entityengine.xml"
>> with the following configuration:
>>
>>    entity-eca-reader="main" entity-group-reader="main" <delegator
>> distributed-cache-clear-enabled="false"> entity-model-reader="main"
>> name="default"
>>        group-name="org.ofbiz" <group-map datasource-name="localmssql"/>
>>        group-name="org.ofbiz.olap" <group-map
>> datasource-name="localmssqlolap"/>
>>        group-name="org.ofbiz.tenant" <group-map
>> datasource-name="localmssqltenant"/>
>> ...
>>    <Datasource name = "localmssql"
>>            helper-class = "org.ofbiz.entity.datasource.GenericHelperDAO"
>>            schema-name = "dbo"
>>            field-type-name = "mssql"
>>            check-on-start = "true"
>>            add-missing-on-start = "true"
>>            join-style = "ansi"
>>            alias-view-columns = "false"
>>            use-fk-INITIALLY-deferred = "false">
>>        reader-name="seed"/> <read-data
>>        reader-name="seed-initial"/> <read-data
>>        reader-name="demo"/> <read-data
>>        reader-name="ext"/> <read-data
>>        <Inline-jdbc
>>                jdbc-driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
>>
>>                jdbc-uri = "jdbc: sqlserver: / / localhost: 1433;
>> databaseName = ofbiz; SelectMethod = cursor;"
>>                jdbc-username = "ofbiz"
>>                jdbc-password = "ofbiz"
>>                isolation-level = "ReadCommitted"
>>                pool-minsize = "2"
>>                pool-maxsize = "250"
>>                time-between-eviction-runs-millis = "600000" />
>>        <! - <jndi-jdbc Jndi-server-name="default"
>> jndi-name="comp/env/jdbc/xa/localmssql" isolation-level="ReadCommitted"/> ->
>> <! - Orion Style JNDI name ->
>>    </ Datasource>
>>
>> Please tell me I'm doing wrong or I'm missing, the connection to the
>> database is local and is the Express version, and tried putting in the
>> connection URL:
>> • Localhost
>> • Localhost: 1433
>> • Localhost: 1791
>> • The name of my PC, "\" and SQLExpress Instance.
>>
>> No way you can connect. I have found no support for this connection.
>>
>>
>> Regards,
>>
>>
>> Rafael
>

[Rafael Rondon]

Thank you very much Jeroen for your help, but after doing what you recommended just told me that it could not connect to the database. Then I realized that the problem is that I need to configure remote connections in SQL Server 2008.

I thought I'd post the solution if it happens to another, I ran the "SQL Server Configuration Manager," and that "SQL Server Network Protocols for SQLEXPRESS "-->" configuration" -> "Property TPCP / IP". And put port you want to use (see image attached).

Sure, when I run startofbiz.bat already, does not fail, and gets to create the database tables in the database. But when I try to run in the browser gives me other errors (see screen attached). The same version of OFBiz if run with Derby DB works fine.

If you can please help me with this new error, Attach I send the log files of the last run of startofbiz.bat.


Regards,


Rafael

On Mon, Jul 11, 2011 at 5:06 PM, Jeroen van der Wal <[hidden email]> wrote:

Here's a link to the JDBC V4 driver:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11774

--
Jeroen van der Wal
Stromboli b.v.
<a href="tel:%2B31%20655%20874050" value="[phone]">[phone]

On Mon, Jul 11, 2011 at 10:55 PM, Jeroen van der Wal
<[hidden email]> wrote:
> We got it working in SQL Server 2008 R2
>
> - add sqljdbc4.jar to /framework/entity/lib/jdbc folder
> - create a user ofbiz with password ofbiz (as in this example)
> - make sure you create the database on the sql server and grant the
> user ofbiz to create tables.
>
> Here's a snippet of entityengine.xml in /framework/entity/config (it
> looked like you copied it to the wrong folder):
>
>    <datasource name="localmssql"
>            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
>            schema-name="dbo"
>            field-type-name="mssql"
>            check-on-start="true"
>            add-missing-on-start="true"
>            join-style="ansi"
>            alias-view-columns="false"
>            use-indices-unique="false"
>            use-fk-initially-deferred="false">
>        <read-data reader-name="seed"/>
>        <read-data reader-name="seed-initial"/>
>        <read-data reader-name="demo"/>
>        <read-data reader-name="ext"/>
>        <inline-jdbc
>                jdbc-driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
>
> jdbc-uri="jdbc:sqlserver://ams-s-sql08.ecp.loc:1433;databaseName=ofbiz;SelectMethod=cursor;"
>                jdbc-username="ofbiz"
>                jdbc-password="ofbiz"
>                isolation-level="ReadCommitted"
>                pool-minsize="2"
>                pool-maxsize="250"
>                time-between-eviction-runs-millis="600000"/>
>        <!-- <jndi-jdbc jndi-server-name="default"
> jndi-name="comp/env/jdbc/xa/localmssql"
> isolation-level="ReadCommitted"/> --> <!-- Orion Style JNDI name -->
>    </datasource>
>
> Hope this helps.
>
> --
> Jeroen van der Wal
> Stromboli b.v.
> <a href="tel:%2B31%20655%20874050" value="[phone]">[phone]
>
>
> On Mon, Jul 11, 2011 at 10:35 PM, Rafael Rondon <[hidden email]> wrote:
>>
>> Hello,
>>
>> I need help to configure and can create me OFBiz database in MS Sql Server
>> 2008 R2. I download the jdbc v2 and v3, with none working. Copy it to
>> "D:\ofbiz\ofiz-trunk-20110705\framework\entity\lib\ jdbc".
>>
>> Also edit the file
>> "D:\ofbiz\ofbiz-trunk-20110705\framework\entity\lib\jdbc\entityengine.xml"
>> with the following configuration:
>>
>>    entity-eca-reader="main" entity-group-reader="main" <delegator
>> distributed-cache-clear-enabled="false"> entity-model-reader="main"
>> name="default"
>>        group-name="org.ofbiz" <group-map datasource-name="localmssql"/>
>>        group-name="org.ofbiz.olap" <group-map
>> datasource-name="localmssqlolap"/>
>>        group-name="org.ofbiz.tenant" <group-map
>> datasource-name="localmssqltenant"/>
>> ...
>>    <Datasource name = "localmssql"
>>            helper-class = "org.ofbiz.entity.datasource.GenericHelperDAO"
>>            schema-name = "dbo"
>>            field-type-name = "mssql"
>>            check-on-start = "true"
>>            add-missing-on-start = "true"
>>            join-style = "ansi"
>>            alias-view-columns = "false"
>>            use-fk-INITIALLY-deferred = "false">
>>        reader-name="seed"/> <read-data
>>        reader-name="seed-initial"/> <read-data
>>        reader-name="demo"/> <read-data
>>        reader-name="ext"/> <read-data
>>        <Inline-jdbc
>>                jdbc-driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
>>
>>                jdbc-uri = "jdbc: sqlserver: / / localhost: 1433;
>> databaseName = ofbiz; SelectMethod = cursor;"
>>                jdbc-username = "ofbiz"
>>                jdbc-password = "ofbiz"
>>                isolation-level = "ReadCommitted"
>>                pool-minsize = "2"
>>                pool-maxsize = "250"
>>                time-between-eviction-runs-millis = "600000" />
>>        <! - <jndi-jdbc Jndi-server-name="default"
>> jndi-name="comp/env/jdbc/xa/localmssql" isolation-level="ReadCommitted"/> ->
>> <! - Orion Style JNDI name ->
>>    </ Datasource>
>>
>> Please tell me I'm doing wrong or I'm missing, the connection to the
>> database is local and is the Express version, and tried putting in the
>> connection URL:
>> • Localhost
>> • Localhost: 1433
>> • Localhost: 1791
>> • The name of my PC, "\" and SQLExpress Instance.
>>
>> No way you can connect. I have found no support for this connection.
>>
>>
>> Regards,
>>
>>
>> Rafael
>

[Rafael Rondon]

Ready, and is running well in SQL Server 2008 R2, I just need to raise test
data, thus giving the error because the database did not contain any records
in its tables.

I ran the following command: ant run-install; and then: startofbiz.bat.

Thanks,


Rafael


On Tue, Jul 12, 2011 at 4:47 PM, Rafael Rondon <[hidden email]> wrote:

> Thank you very much Jeroen for your help, but after doing what you
> recommended just told me that it could not connect to the database. Then I
> realized that the problem is that I need to configure remote connections in
> SQL Server 2008.
>
> I thought I'd post the solution if it happens to another, I ran the "SQL
> Server Configuration Manager," and that "SQL Server Network Protocols for
> SQLEXPRESS "-->" configuration" -> "Property TPCP / IP". And put port you
> want to use (see image attached).
>
> Sure, when I run startofbiz.bat already, does not fail, and gets to create
> the database tables in the database. But when I try to run in the browser gives
> me other errors (see screen attached). The same version of OFBiz if run
> with Derby DB works fine.
>
> If you can please help me with this new error, Attach I send the log files
> of the last run of startofbiz.bat.
>
>
> Regards,
>
>
> Rafael
>
>
>
> On Mon, Jul 11, 2011 at 5:06 PM, Jeroen van der Wal <[hidden email]>wrote:
>
>> Here's a link to the JDBC V4 driver:
>> http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11774
>> --
>> Jeroen van der Wal
>> Stromboli b.v.
>> [phone]
>>
>>
>>
>> On Mon, Jul 11, 2011 at 10:55 PM, Jeroen van der Wal
>> <[hidden email]> wrote:
>> > We got it working in SQL Server 2008 R2
>> >
>> > - add sqljdbc4.jar to /framework/entity/lib/jdbc folder
>> > - create a user ofbiz with password ofbiz (as in this example)
>> > - make sure you create the database on the sql server and grant the
>> > user ofbiz to create tables.
>> >
>> > Here's a snippet of entityengine.xml in /framework/entity/config (it
>> > looked like you copied it to the wrong folder):
>> >
>> >    <datasource name="localmssql"
>> >            helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
>> >            schema-name="dbo"
>> >            field-type-name="mssql"
>> >            check-on-start="true"
>> >            add-missing-on-start="true"
>> >            join-style="ansi"
>> >            alias-view-columns="false"
>> >            use-indices-unique="false"
>> >            use-fk-initially-deferred="false">
>> >        <read-data reader-name="seed"/>
>> >        <read-data reader-name="seed-initial"/>
>> >        <read-data reader-name="demo"/>
>> >        <read-data reader-name="ext"/>
>> >        <inline-jdbc
>> >
>>  jdbc-driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
>> >
>> >
>> jdbc-uri="jdbc:sqlserver://ams-s-sql08.ecp.loc:1433;databaseName=ofbiz;SelectMethod=cursor;"
>> >                jdbc-username="ofbiz"
>> >                jdbc-password="ofbiz"
>> >                isolation-level="ReadCommitted"
>> >                pool-minsize="2"
>> >                pool-maxsize="250"
>> >                time-between-eviction-runs-millis="600000"/>
>> >        <!-- <jndi-jdbc jndi-server-name="default"
>> > jndi-name="comp/env/jdbc/xa/localmssql"
>> > isolation-level="ReadCommitted"/> --> <!-- Orion Style JNDI name -->
>> >    </datasource>
>> >
>> > Hope this helps.
>> >
>> > --
>> > Jeroen van der Wal
>> > Stromboli b.v.
>> > [phone]
>> >
>> >
>> > On Mon, Jul 11, 2011 at 10:35 PM, Rafael Rondon <[hidden email]>
>> wrote:
>> >>
>> >> Hello,
>> >>
>> >> I need help to configure and can create me OFBiz database in MS Sql
>> Server
>> >> 2008 R2. I download the jdbc v2 and v3, with none working. Copy it to
>> >> "D:\ofbiz\ofiz-trunk-20110705\framework\entity\lib\ jdbc".
>> >>
>> >> Also edit the file
>> >>
>> "D:\ofbiz\ofbiz-trunk-20110705\framework\entity\lib\jdbc\entityengine.xml"
>> >> with the following configuration:
>> >>
>> >>    entity-eca-reader="main" entity-group-reader="main" <delegator
>> >> distributed-cache-clear-enabled="false"> entity-model-reader="main"
>> >> name="default"
>> >>        group-name="org.ofbiz" <group-map datasource-name="localmssql"/>
>> >>        group-name="org.ofbiz.olap" <group-map
>> >> datasource-name="localmssqlolap"/>
>> >>        group-name="org.ofbiz.tenant" <group-map
>> >> datasource-name="localmssqltenant"/>
>> >> ...
>> >>    <Datasource name = "localmssql"
>> >>            helper-class =
>> "org.ofbiz.entity.datasource.GenericHelperDAO"
>> >>            schema-name = "dbo"
>> >>            field-type-name = "mssql"
>> >>            check-on-start = "true"
>> >>            add-missing-on-start = "true"
>> >>            join-style = "ansi"
>> >>            alias-view-columns = "false"
>> >>            use-fk-INITIALLY-deferred = "false">
>> >>        reader-name="seed"/> <read-data
>> >>        reader-name="seed-initial"/> <read-data
>> >>        reader-name="demo"/> <read-data
>> >>        reader-name="ext"/> <read-data
>> >>        <Inline-jdbc
>> >>                jdbc-driver =
>> "com.microsoft.sqlserver.jdbc.SQLServerDriver"
>> >>
>> >>                jdbc-uri = "jdbc: sqlserver: / / localhost: 1433;
>> >> databaseName = ofbiz; SelectMethod = cursor;"
>> >>                jdbc-username = "ofbiz"
>> >>                jdbc-password = "ofbiz"
>> >>                isolation-level = "ReadCommitted"
>> >>                pool-minsize = "2"
>> >>                pool-maxsize = "250"
>> >>                time-between-eviction-runs-millis = "600000" />
>> >>        <! - <jndi-jdbc Jndi-server-name="default"
>> >> jndi-name="comp/env/jdbc/xa/localmssql"
>> isolation-level="ReadCommitted"/> ->
>> >> <! - Orion Style JNDI name ->
>> >>    </ Datasource>
>> >>
>> >> Please tell me I'm doing wrong or I'm missing, the connection to the
>> >> database is local and is the Express version, and tried putting in the
>> >> connection URL:
>> >> • Localhost
>> >> • Localhost: 1433
>> >> • Localhost: 1791
>> >> • The name of my PC, "\" and SQLExpress Instance.
>> >>
>> >> No way you can connect. I have found no support for this connection.
>> >>
>> >>
>> >> Regards,
>> >>
>> >>
>> >> Rafael
>> >
>>
>
>
>
>

--
Rafael Rondón Montás
Software Developer
Dominican Republic
Cel:. 809-862-2839
email: [hidden email]