I am developing a C# Web API for .NET Core 6.0.18 I am using Oracle.ManagedDataAccess.Core 3.21.110
The error occurs on an IIS 10 server, which is configured as follows:
Windows Server 2016 - Dynamic
Supporting: .NET Framework: 4.5.2; 4.6.X; 4.7.X, 4.8
ASP 3.0
ASP.NET 4.0
C#
ASP.NET Ajax
ASP.NET Core IIS Module
3.1 Runtime
With the following note:
To use newer or older versions of ASP.NET Core,
publish your application as self-contained <-- I publish my app as "self-contained"
I publish with VS 2022 as follows:
I am getting the following error when attempted to open a connection to an Oracle 11g DB with a time zone of UTC -7 (Phoenix, AZ):
"ORA-00604: error occurred at recursive SQL level 1 ORA-01882: time zone region not found".
I'm using a Program.cs for startup configuration. The following two json configuration files are in my VS 2022 project too:
- launchSettings.json
- appsettings.json.
I have seen other similar issues:
Code & JSON for IIS Express (IIS Express on my PC works fine on my machine with the Oracle Connection. It fails on the IIS Server)
Some other Stack Overflow answers indicate that the TZ environment variable needs to be set to UTC or I'm guessing some time zone setting. However, none of the solutions mention how to do this using one of the two JSON files created for this app by VS 2022's C# Web API .NET Core template and for an "IIS 10 Server". The answers only are for IIS Express and only partial chunks of JSON vs an entire JSON file and the file's name.
I am new to using this tech and I'm learning as I'm writing the app. Please don't assume that I have in depth knowledge related to this tech.
I have tried this code solutions where:
- the time zone is filled in after the
conn.Openoccurs. I got the same ORA error.
Note: ConnStr is a property for the class that contains the GetTransactions method and is set and retrieved from the appsettings.json file. I'm NOT getting an ORA error related to the connection string, which is shown in the VS 2022 publish dialog with some info redacted. The connection string works fine and I get data when running with the VS 2022 IDE on my localhost server and IIS Express.
public class Transaction
{
public int TransId { get; set; }
public string TransDesc { get; set; }
public Transaction()
{
this.TransId = 0;
this.TransDesc = string.Empty;
}
public Transaction(int TransId, string TransDesc)
{
this.TransId = TransId;
this.TransDesc = TransDesc;
}
}
public class UserTransactions
{
public Status Status { get; set; }
public List<Transaction> Transactions { get; set;}
public UserTransactions()
{
Status = new Status();
Transactions = new List<Transaction>();
}
}
public class Status
{
public static int NotFound = 1;
public static int Failed = 4;
public static int Success = 0;
public Status()
{
ReturnCode = Success;
Msg = string.Empty;
}
public Status(int returnCode, string msg)
{
ReturnCode = returnCode;
Msg = msg;
}
public int ReturnCode { get; set; }
public string Msg { get; set; }
}
public UserTransactions GetTransactions(string id)
{
UserTransactions userTransactions = new UserTransactions();
List<Transaction> transactions = userTransactions.Transactions;
using (OracleConnection conn = new OracleConnection(ConnStr))
{
string sql = "SELECT t.trans_id, t.trans_desc "
+ "FROM GROUP_TRANSACTIONS gt, Transactions t, "
+ "EMPLOYEE_GROUPS eg "
+ "WHERE gt.trans_id = t.trans_id AND "
+ "gt.GROUP_ID = eg.GROUP_ID AND "
+ "eg.id = :id";
OracleCommand oracleCommand = new OracleCommand(sql,conn);
oracleCommand.CommandType = CommandType.Text;
OracleParameter prmId = new OracleParameter();
prmId.OracleDbType = OracleDbType.Varchar2;
prmId.Value = id;
oracleCommand.Parameters.Add(prmId);
try
{
conn.Open();
OracleGlobalization info = conn.GetSessionInfo();
info.TimeZone = "America/Phoenix";
conn.SetSessionInfo(info);
OracleDataReader dataReader = oracleCommand.ExecuteReader();
while (dataReader.Read())
{
transactions.Add(new Transaction(dataReader.GetInt32(0),
dataReader.GetString(1)));
}
if (transactions.Count == 0)
{
userTransactions.Status.ReturnCode = Status.NotFound;
userTransactions.Status.Msg = $"No data was found for id {id}";
}
}
catch (Exception ex)
{
log.Error(ex.Message);
userTransactions.Status.Msg = $"DB Error conntact PTA Mobile support. DB Error message: {ex.Message}";
userTransactions.Status.ReturnCode = Status.Failed;
}
}
return userTransactions;
}
}
}
- Other Stack Overflow answers were specifying a setting in JSON format, but was for a Docker container vs an IIS server
Has anyone had this problem for a "configuration" like I've shown above and resolve it?
Please note, I cannot change to another IIS Server or database. Those resources are what I must use.
The app works when I run with VS 2022 on my PC, with IIS Express and a localhost, but fails when it is published to the IIS server and I invoke it with a URL - i.e. my Web API endpoint is similar to this:
https://my-app-host/api/Users/5555
where 5555 is the id that get passed to my GetTransactions method shown above. The catch (Exception ex) code gets executed and I receive the ORA error from the ex.Message.
Any help or suggestions would be greatly appreciated!
By the way, I could not figure out how to incorporate the environment variable for the IIS 10 Server into my JSON files. (The other Stack Overflow answers are for the IIS Express localhost server that runs with the VS 2022 IDE or a Docker Container. If anyone knows how to provide the TZ environment variable for the IIS 10 server with a JSON example and the filename that it should go in, I will try that.)
