We need some help configuring PXWEB with a SQL Server database

[Matilde Lopez]

Good afternoon everyone,

On this occasion we turn to the group to see if they can help us. We need help configuring PXWEB with a SQL Server database

We want to configure PxWeb with a SQL Server database. We made the configurations in the corresponding files and have had no luck.

We create the database and when we enter the PxWeb website, we see that there is the link to enter the database, but when we click on the link, we do not see the content of the tables that exist in the database.
I leave you 2 images with what happens to us.

Could someone provide us with specific configuration files for a SQL Server connection hosted on the same server as the web application? This would help us greatly in resolving this issue and moving forward with the integration smoothly.

Thank you very much in advance and we look forward to your response.

Greetings,

[Matilde Lopez]

I attach images of the situation we have.

Thank you very much in advance and we look forward to your response.

Greetings,

[Stefan Jul Gunnersen]

Hello,

I only have limited experience with PxWeb and that is primarily with Oracle (and a bit PostGres), but it should be as simple as having a working model in the database and use a .NET friendly connection string.

However, you should be able to see some more info in the log for the PxWeb site itself. It should be located in a subdirectory of the site (cannot remember which folder, but search for "log" or something like that.

Also, I believe the logging can be adjusted to output more debug info.

I hope this helps you. Feel free to write back and I am sure me or somebody else from the group will provide assistance.

Kind regards,

Stefan, IT developer at Statistics Denmark

[Matilde Lopez]

Hello,

Thanks for your reply.

I will check the log and try to do the same with Postgres.

Kind regards,

Matilde

[Stefan Jul Gunnersen]

Actually, as far as I know, PostGres is not supported, but I have had some luck implementing it with some changes to the source code. It might be available someday, but it depends on a lot of different things. I would definitely recommend you to get PxWeb to run with the SQL Server first.

Kind regards,
Stefan

[Matilde Lopez]

Good afternoon Stefan and everyone,

We have been viewing the files and modifying as you indicated but we still cannot see the data that is loaded in the database. The SQLServer database looks fine but gives an error when reading the data.

Could you give us an example of the SQDb.config file to see if we have any errors that we are not being able to notice?

On the other hand, we try to configure the database with Postgres and it also gives us an error.

We need to get this working by setting up a SQL database. It doesn't matter if it's SQLServer or Postgres or MySQL.

That's why we insist on asking for help. Could you provide us with an example of a database that you have configured with one of the models that we mentioned in the previous paragraph?

I take this opportunity to attach the SQLServer and Postgres logs.

Thank you very much in advance and we hope you can continue helping us.

Kind regards,

[Stefan Jul Gunnersen]

Dear all,

I have examined our own Oracle enabled PxWeb instance and replaced sensitive info. 

Below is an adjusted version of our sqldb.config where I have replaced the connection string for Oracle with a default one for SQL Server. Note that USER and PASSWORD should be replaced by the value of DefaultUser and DefaultPassword.

Also note that this is for a previous version of PxWeb (the 2021 edition, I believe), not the newest which I have yet to experiment with.

SqlDb.confg:

<?xml version="1.0" encoding="iso-8859-1"?>
<SqlDbConfig version="2008" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="SqlDbConfig.xsd">
<Database id="DST" metaModel="2.3">
<Descriptions>
<Description lang="da">Danmarks Statistik</Description>
<Description lang="en">Statistics Denmark</Description>
</Descriptions>

<Connection databaseType="SQL" dataProvider="Sql" useTemporaryTables="false" metatablesSchema="YourDatabaseSchema" >
<ConnectionString>Server=yourServername\yourInstanceName;Database=yourDataBase;User Id=USER;Password=PASSWORD;</ConnectionString>
                           <DefaultUser>YourUser</DefaultUser>
                           <DefaultPassword>YourPassword</DefaultPassword>
</Connection>

<Languages>
<Language main="true" name="Danish" code="da" metaSuffix="" />
<Language main="false" name="English" code="en" metaSuffix="ENG" />
</Languages>
<Codes />

<Keywords>
</Keywords>
<Tables>
<Table modelName="Attribute" tableName="ATTRIBUTE" alias="ATT"></Table>
<Table modelName="AttributeLang2" tableName="ATTRIBUTE_ENG" alias="AT2"></Table>
<Table modelName="Contents" tableName="CONTENTS" alias="CNT"></Table>
<Table modelName="ContentsLang2" tableName="CONTENTS_ENG" alias="CN2"></Table>
<Table modelName="ContentsTime" tableName="CONTENTSTIME" alias="CTM"></Table>
<Table modelName="DataStorage" tableName="DATASTORAGE" alias="DST"></Table>
<Table modelName="Footnote" tableName="FOOTNOTE" alias="FNT"></Table>
<Table modelName="FootnoteLang2" tableName="FOOTNOTE_ENG" alias="FN2"></Table>
<Table modelName="FootnoteContents" tableName="FOOTNOTECONTENTS" alias="FCO"></Table>
<Table modelName="FootnoteContTime" tableName="FOOTNOTECONTTIME" alias="FCT"></Table>
<Table modelName="FootnoteContValue" tableName="FOOTNOTECONTVALUE" alias="FCA"></Table>
<Table modelName="FootnoteContVbl" tableName="FOOTNOTECONTVBL" alias="FCB"></Table>
<Table modelName="FootnoteGrouping" tableName="FOOTNOTEGROUPING" alias="FCO"></Table>
<Table modelName="FootnoteMainTable" tableName="FOOTNOTEMAINTABLE" alias="FMT"></Table>
<Table modelName="FootnoteMaintTime" tableName="FOOTNOTEMAINTTIME" alias="FNM"></Table>
<Table modelName="FootnoteMaintValue" tableName="FOOTNOTEMAINTVALUE" alias="FMV"></Table>
<Table modelName="FootnoteMenuSel" tableName="FOOTNOTEMENUSEL" alias="FMS"></Table>
<Table modelName="FootnoteSubTable" tableName="FOOTNOTESUBTABLE" alias="FST"></Table>
<Table modelName="FootnoteValue" tableName="FOOTNOTEVALUE" alias="FVL"></Table>
<Table modelName="FootnoteValueSetValue" tableName="FOOTNOTEVALUESETVALUE" alias="FVS"></Table>
<Table modelName="FootnoteVariable" tableName="FOOTNOTEVARIABLE" alias="FVB"></Table>
<Table modelName="Grouping" tableName="GROUPING" alias="GRP"></Table>
<Table modelName="GroupingLang2" tableName="GROUPING_ENG" alias="GR2"></Table>
<Table modelName="GroupingLevel" tableName="GROUPINGLEVEL" alias="GRP"></Table>
<Table modelName="GroupingLevelLang2" tableName="GROUPINGLEVEL_ENG" alias="GR2"></Table>
<Table modelName="Link" tableName="LINK" alias="LNK"></Table>
<Table modelName="LinkLang2" tableName="LINK_ENG" alias="LN2"></Table>
<Table modelName="LinkMenuSelection" tableName="LINKMENUSELECTION" alias="LMS"></Table>
<Table modelName="MainTable" tableName="MAINTABLE" alias="MTA"></Table>
<Table modelName="MainTableLang2" tableName="MAINTABLE_ENG" alias="MT2"></Table>
<Table modelName="MainTablePerson" tableName="MAINTABLEPERSON" alias="MTP"></Table>
<Table modelName="MainTableVariableHierarchy" tableName="MAINTABLEVARIABLEHIERARCHY" alias="MTP"></Table>
<Table modelName="MenuSelection" tableName="MENUSELECTION" alias="MSL"></Table>
<Table modelName="MenuSelectionLang2" tableName="MENUSELECTION_ENG" alias="MS2"></Table>
<Table modelName="MetabaseInfo" tableName="METABASEINFO" alias="MBI"></Table>
<Table modelName="MetaAdm" tableName="METAADM" alias="MAD"></Table>
<Table modelName="Organization" tableName="ORGANIZATION" alias="ORG"></Table>
<Table modelName="OrganizationLang2" tableName="ORGANIZATION_ENG" alias="OR2"></Table>
<Table modelName="Person" tableName="PERSON" alias="PRS"></Table>
<Table modelName="SecondaryLanguage" tableName="SECONDARYLANGUAGE" alias="SLA"></Table>
<Table modelName="SpecialCharacter" tableName="SPECIALCHARACTER" alias="SPC"></Table>
<Table modelName="SpecialCharacterLang2" tableName="SPECIALCHARACTER_ENG" alias="SP2"></Table>
<Table modelName="SubTable" tableName="SUBTABLE" alias="STB"></Table>
<Table modelName="SubTableLang2" tableName="SUBTABLE_ENG" alias="ST2"></Table>
<Table modelName="SubTableVariable" tableName="SUBTABLEVARIABLE" alias="STV"></Table>
<Table modelName="TextCatalog" tableName="TEXTCATALOG" alias="TXC"></Table>
<Table modelName="TextCatalogLang2" tableName="TEXTCATALOG_ENG" alias="TX2"></Table>
<Table modelName="TimeScale" tableName="TIMESCALE" alias="TSC"></Table>
<Table modelName="TimeScaleLang2" tableName="TIMESCALE_ENG" alias="TS2"></Table>
<Table modelName="Value" tableName="VALUE" alias="VAL"></Table>
<Table modelName="ValueLang2" tableName="VALUE_ENG" alias="VA2"></Table>
<Table modelName="ValueGroup" tableName="VALUEGROUP" alias="VPL"></Table>
<Table modelName="ValueGroupLang2" tableName="VALUEGROUP_ENG" alias="VL2"></Table>
<Table modelName="ValuePool" tableName="VALUEPOOL" alias="VPL"></Table>
<Table modelName="ValuePoolLang2" tableName="VALUEPOOL_ENG" alias="VP2"></Table>
<Table modelName="ValueSet" tableName="VALUESET" alias="VST"></Table>
<Table modelName="ValueSetLang2" tableName="VALUESET_ENG" alias="VS2"></Table>
<Table modelName="ValueSetGrouping" tableName="VALUESETGROUPING" alias="VBL"></Table>
<Table modelName="Variable" tableName="VARIABLE" alias="VBL"></Table>
<Table modelName="VariableLang2" tableName="VARIABLE_ENG" alias="VB2"></Table>
<Table modelName="VSValue" tableName="VSVALUE" alias="VVL"></Table>
<Table modelName="VSValueLang2" tableName="VSVALUE_ENG" alias="VV2"></Table>
</Tables>
</Database>
</SqlDbConfig>

As regards the log I believe you should try to select the database and then see what the log notes about any occurring errors. Also, you should modify the log4net section for logging level in the Web.config and set it to DEBUG:

<logger additivity="false" name="PCAxis.Web.Controls.PxDefaultLogger">
<level value="DEBUG" />
<appender-ref ref="visitorStatisticsAppender" />
</logger>
<logger name="api-usage">
<level value="DEBUG" />
<appender-ref ref="ApiAppender" />
</logger>
<root>
<level value="DEBUG" />
<appender-ref ref="RollingLogFileAppender" />
</root>

This should effect the logging.

Kind regards,

Stefan