private static void Main(string[] args)
{
var connString = "Host=127.0.0.1;Server = alx_test; User ID = informix; password = 462753;";
var conn = new IfxConnection(connString);
conn.Open();
var createDbCommand = conn.CreateCommand();
var selectDbCommand = conn.CreateCommand();
var createTableCommand = conn.CreateCommand();
createDbCommand.CommandText = "CREATE DATABASE IF NOT EXISTS 'alxr64_PeopleRegistry_Demo';"
;
selectDbCommand.CommandText = "DATABASE 'alxr64_PeopleRegistry_Demo';";
createTableCommand.CommandText = @"CREATE TABLE IF NOT EXISTS person (
id SERIAL not null,
FirstName VARCHAR(100),
LastName VARCHAR(100),
SurName VARCHAR(100),
BirthDate DATE);";
createDbCommand.ExecuteNonQuery();
selectDbCommand.ExecuteNonQuery();
createTableCommand.ExecuteReader();
}
protected override IDataReader PrepareSQLDatasetInternal(String sql, bool schemaOnly)
{
IfxCommand command;
IfxDataReader reader;
try
{
if (!Connected)
{
Connect();
}
command = _connection.CreateCommand();
command.CommandTimeout = CommandTimeout;
command.CommandText = sql;
if (schemaOnly)
{
reader = command.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
}
else
{
reader = command.ExecuteReader();
}
}
catch (Exception e)
{
throw new QueryBuilderException(ErrorCode.ErrorExecutingQuery,
e.Message + "\n\n" + Helpers.Localizer.GetString("strQuery", Constants.strQuery) + "\n" + sql);
}
return(reader);
}
/// <summary>
/// Create a table
/// </summary>
/// <returns>bool</returns>
public bool CreateTable()
{
try
{
_command = _connection.CreateCommand();
_command.CommandText = "create table transactiontable(id integer, name char(25));";
_command.ExecuteNonQuery();
}
catch (Exception excep)
{
WriteLine(String.Format("The table cannot be created: {0}", excep.Message));
return(false);
}
return(true);
}
public async Task <Person> GetById(int id)
{
var req = _conn.CreateCommand();
req.CommandText = $"SELECT * FROM person WHERE id={id};";
await _conn.OpenAsync();
var res = await req.ExecuteReaderAsync();
var p = FromDb(res);
_conn.Close();
return(p.First());
}
public void GetPosition(out int x, out int y)
{
x = 0; y = 0;
string strConn = ConfigurationSettings.AppSettings.Get("MadsConnect");
IfxConnection conn = new IfxConnection(strConn);
try
{
conn.Open();
}
catch
{
return;
}
using (IfxCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select vh_gps_lat,vh_gps_long from vehicle where vh_nbr=" + this.VehicleID;
IfxDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
x = Int32.Parse(rdr["vh_gps_long"].ToString());
y = Int32.Parse(rdr["vh_gps_lat"].ToString());
}
}
conn.Close();
}
public static IEnumerable <Table> GetTables(IfxConnection cn)
{
var tables = new List <Table>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = @"
select trim(st.tabname), st.tabtype
from 'informix'.systables st
where st.tabid >= 100
and (st.tabtype = 'T' or st.tabtype = 'V')";
using (var reader = command.ExecuteReader()) {
for (; reader.Read();)
{
string tabName = reader[0] as string;
string tabType = reader[1] as string;
tables.Add(new Table(tabName, null, (tabType == "T") ? TableType.Table : TableType.View));
}
}
return(tables);
}
public Location(XmlNode locationNode)
{
theVehicle = new Vehicle(locationNode.SelectSingleNode("/location_request/vehicle").InnerXml);
//string sqlConnString = "Host=192.168.1.120;Service=6032;Server=mads_se;User ID=net_book;password=Mickey;Database=/usr/taxi/mads";
string sqlConnString = ConfigurationSettings.AppSettings.Get("MadsOBC");
IfxConnection conn = new IfxConnection(sqlConnString);
conn.Open();
using (IfxCommand ct = conn.CreateCommand())
{
string sqlQuery = "select vh_gps_long,vh_gps_lat from vehicle where vh_nbr=" + theVehicle.VehNbr.ToString();
ct.CommandText = sqlQuery;
IfxDataReader dr = ct.ExecuteReader();
if (dr.Read())
{
theVehicle.X = Convert.ToInt32(dr["vh_gps_long"]);
theVehicle.Y = Convert.ToInt32(dr["vh_gps_lat"]);
}
else
{
theVehicle.X = 0;
theVehicle.Y = 0;
}
}
conn.Close();
}
private bool UpdateMISHotlist(IMISHotlistUpdate update, string encryptedIdentifier)
{
Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
try
{
using (IfxConnection connection = EstablishConnection())
{
IfxCommand command = connection.CreateCommand();
if (update.Change == "Add")
{
command.CommandText = $"INSERT INTO Hotlist VALUES ('{encryptedIdentifier}') ";
}
else if (update.Change == "Delete")
{
command.CommandText = $"DELETE FROM Hotlist WHERE ac_nr = '{encryptedIdentifier}'";
}
}
}
catch (Exception ex)
{
Log.LogException(ex);
Log.LogTrace(ex.Message + ". Check error log for more details.");
return(false);
}
Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
return(true);
}
private static void DeleteRecords(IfxConnection cn, string tableName)
{
using (var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("delete from {0}", tableName);
cmd.ExecuteNonQuery();
}
}
private static void DeleteRecords(IfxConnection cn, string tableName)
{
using (var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("delete from {0}", tableName);
cmd.ExecuteNonQuery();
}
}
public DbCommand CreateCommand()
{
try
{
DbCommand cmd = null;
cmd = Conn.CreateCommand();
return(cmd);
}
catch (Exception)
{
throw;
}
}
private void InsertNewUpdates(IEnumerable <IMISValidationListUpdate> addedItems, IfxConnection conn)
{
Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
try
{
IfxCommand command = conn.CreateCommand();
var insertItems = "()";
command.CommandText = $"INSERT INTO VL VALUES {insertItems}";
}
catch (Exception ex)
{
Log.LogTrace(ex.Message + ". Check error log for more details.");
Log.LogException(ex);
}
Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
}
static List <uspGetListOfSequenceNrGaps_Result> GetDuplicateTransactions(IfxConnection connection)
{
Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
var missing = new List <uspGetListOfSequenceNrGaps_Result>();
using (var dataContext = new DatabaseSyncDataContext())
{
IfxCommand command = connection.CreateCommand();
Log.LogInfoMessage($"Querying duplicate transactions for month : {CurrentMonth.ToString("yyyy-MM-dd 00:00:00")}");
command.CommandText =
$"select count(*),ln_id, tx_seq_nr " +
$"from informix.p_trans " +
$"where dt_concluded >= TO_DATE('{CurrentMonth.ToString("yyyy-MM-dd 00:00:00")}', '%Y-%m-%d %H:%M:%S') " +
$"and dt_concluded < TO_DATE('{FollowingMonth.ToString("yyyy-MM-dd 00:00:00")}','%Y-%m-%d %H:%M:%S') " +
$"Group by ln_id,tx_seq_nr ";
Log.LogInfoMessage(command.CommandText);
IfxDataReader dataReader = command.ExecuteReader();
List <string> s = new List <string>();
while (dataReader.Read())
{
if (dataReader[0].ToString().Trim() == "1")
{
continue;
}
missing.Add(new uspGetListOfSequenceNrGaps_Result {
Lane = dataReader[1].ToString().Trim(), Sequencenr = int.Parse(dataReader[2].ToString().Trim())
});
}
}
Log.LogInfoMessage($"Duplicate Transactions found : {missing.Count}");
Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
return(missing);
}
private void RemoveDeletedUpdates(IEnumerable <IMISValidationListUpdate> deletedItems, IfxConnection conn)
{
Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
try
{
IfxCommand command = conn.CreateCommand();
string deleteList = "()";
command.CommandText = $"DELETE VL WHERE IDVL IN {deleteList}";
}
catch (Exception ex)
{
Log.LogTrace(ex.Message + ". Check error log for more details.");
Log.LogException(ex);
}
Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
}
private void UpdateMISValidationList(IEnumerable <IMISValidationListUpdate> updatedItems, IfxConnection conn)
{
Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
try
{
foreach (var update in updatedItems)
{
IfxCommand command = conn.CreateCommand();
command.CommandText = $"UPDATE VL SET Balance = Balance WHERE ac_nr = ac_nr ";
}
}
catch (Exception ex)
{
Log.LogTrace(ex.Message + ". Check error log for more details.");
Log.LogException(ex);
}
Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
}
public static IEnumerable <Column> GetColumns(Table table, IfxConnection cn)
{
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = @"
select trim(sc.colname), sc.coltype, sc.collength
from 'informix'.systables st, 'informix'.syscolumns sc
where st.tabname = ?
and (st.tabtype = 'T' or st.tabtype = 'V')
and st.tabid >= 100
and st.tabid = sc.tabid";
command.Parameters.Add("st.tabname", table.ActualName);
var informixColumnInfos = new List <InformixColumnInfo>();
using (var reader = command.ExecuteReader()) {
for (; reader.Read();)
{
string colName = reader[0] as string;
int colType = Convert.ToInt32(reader[1]);
int colLength = Convert.ToInt32(reader[2]);
var informixColumnInfo = InformixColumnInfoCreator.CreateColumnInfo(
colName,
colType,
colLength);
informixColumnInfos.Add(informixColumnInfo);
}
}
var results =
from c in informixColumnInfos
select new Column(c.Name, table, c.IsAutoincrement, c.DbType, c.Capacity);
return(results);
}
public static IEnumerable<Column> GetColumns(Table table, IfxConnection cn)
{
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = @"
select trim(sc.colname), sc.coltype, sc.collength
from 'informix'.systables st, 'informix'.syscolumns sc
where st.tabname = ?
and (st.tabtype = 'T' or st.tabtype = 'V')
and st.tabid >= 100
and st.tabid = sc.tabid";
command.Parameters.Add("st.tabname", table.ActualName);
var informixColumnInfos = new List<InformixColumnInfo>();
using (var reader = command.ExecuteReader()) {
for (; reader.Read(); ) {
string colName = reader[0] as string;
int colType = Convert.ToInt32(reader[1]);
int colLength = Convert.ToInt32(reader[2]);
var informixColumnInfo = InformixColumnInfoCreator.CreateColumnInfo(
colName,
colType,
colLength);
informixColumnInfos.Add(informixColumnInfo);
}
}
var results =
from c in informixColumnInfos
select new Column(c.Name, table, c.IsAutoincrement, c.DbType, c.Capacity);
return results;
}
public static IEnumerable<Table> GetTables(IfxConnection cn)
{
var tables = new List<Table>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = @"
select trim(st.tabname), st.tabtype
from 'informix'.systables st
where st.tabid >= 100
and (st.tabtype = 'T' or st.tabtype = 'V')";
using (var reader = command.ExecuteReader()) {
for (; reader.Read(); ) {
string tabName = reader[0] as string;
string tabType = reader[1] as string;
tables.Add(new Table(tabName, null, (tabType == "T") ? TableType.Table : TableType.View));
}
}
return tables;
}
public static IEnumerable <ForeignKey> GetForeignKeys(Table table, IfxConnection cn)
{
var fkqis = new List <ForeignKeyQueryInfo>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select st.tabid from 'informix'.systables st where st.tabname = ?";
command.Parameters.Add("st.tabname", table.ActualName);
int?tabid = null;
try {
tabid = command.ExecuteScalar() as int?;
}
catch (Exception) {
}
if (tabid.HasValue)
{
command.CommandText = @"
select
si.part1, si.part2, si.part3, si.part4,
si.part5, si.part6, si.part7, si.part8,
si.part9, si.part10, si.part11, si.part12,
si.part13, si.part14, si.part15, si.part16,
trim(st.tabname) as fktabname,
si.tabid as fktabid,
trim(rt.tabname) as pktabname,
rc.tabid as pktabid,
sr.primary as pkconstraintid
from
'informix'.systables st,
'informix'.sysconstraints sc,
'informix'.sysindexes si,
'informix'.sysreferences sr,
'informix'.systables rt,
'informix'.sysconstraints rc
where
st.tabid = ?
and st.tabid = sc.tabid
and sc.constrtype = 'R'
and sc.constrid = sr.constrid
and sc.tabid = si.tabid
and sc.idxname = si.idxname
and rt.tabid = sr.ptabid
and rc.tabid = sr.ptabid
and sr.primary = rc.constrid";
command.Parameters.Clear();
command.Parameters.Add("st.tabid", tabid);
using (var reader = command.ExecuteReader()) {
for (; reader.Read();)
{
var fkqi = new ForeignKeyQueryInfo();
fkqi.FKTabName = reader["fktabname"] as string;
fkqi.FKTabId = Convert.ToInt32(reader["fktabid"]);
fkqi.PKTabName = reader["pktabname"] as string;
fkqi.PKTabId = Convert.ToInt32(reader["pktabid"]);
fkqi.PKConstraintId = Convert.ToInt32(reader["pkconstraintid"]);
for (int i = 0; i < 16; i++)
{
int part = Convert.ToInt32(reader.GetValue(i));
if (part == 0)
{
break;
}
fkqi.FKParts.Add(part);
}
fkqis.Add(fkqi);
}
}
command.CommandText = @"
select
part1, part2, part3, part4,
part5, part6, part7, part8,
part9, part10, part11, part12,
part13, part14, part15, part16
from
'informix'.sysindexes si,
'informix'.sysconstraints sc
where
si.tabid = sc.tabid
and si.idxname = sc.idxname
and sc.constrid = ?";
command.Parameters.Clear();
command.Parameters.Add("sc.constrid", null);
foreach (var fkqi in fkqis)
{
command.Parameters["sc.constrid"].Value = fkqi.PKConstraintId;
using (var reader = command.ExecuteReader()) {
for (; reader.Read();)
{
for (int i = 0; i < 16; i++)
{
int part = Convert.ToInt32(reader.GetValue(i));
if (part == 0)
{
break;
}
fkqi.PKParts.Add(part);
}
}
}
}
command.CommandText = "select trim(sc.colname) from 'informix'.syscolumns sc where sc.tabid = ? and sc.colno = ?";
command.Parameters.Clear();
command.Parameters.Add("sc.tabid", null);
command.Parameters.Add("sc.colno", null);
foreach (var fkqi in fkqis)
{
command.Parameters["sc.tabid"].Value = fkqi.FKTabId;
foreach (int part in fkqi.FKParts)
{
command.Parameters["sc.colno"].Value = part;
string columnName = command.ExecuteScalar() as string;
fkqi.FKColumnNames.Add(columnName);
}
command.Parameters["sc.tabid"].Value = fkqi.PKTabId;
foreach (int part in fkqi.PKParts)
{
command.Parameters["sc.colno"].Value = part;
string columnName = command.ExecuteScalar() as string;
fkqi.PKColumnNames.Add(columnName);
}
}
}
var results =
from fkqi in fkqis
select new ForeignKey(
detailTable: new ObjectName(null, fkqi.FKTabName),
columns: fkqi.FKColumnNames,
masterTable: new ObjectName(null, fkqi.PKTabName),
masterColumns: fkqi.PKColumnNames);
return(results.ToArray());
}
// http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
// http://stackoverflow.com/questions/320045/how-do-i-get-constraint-details-from-the-name-in-informix
public static Key GetPrimaryKey(Table table, IfxConnection cn)
{
var columnNames = new List <string>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select st.tabid from 'informix'.systables st where st.tabname = ?";
command.Parameters.Add("st.tabname", table.ActualName);
int?tabid = null;
try {
tabid = command.ExecuteScalar() as int?;
}
catch (Exception) {
}
if (tabid.HasValue)
{
command.CommandText = @"
select
si.part1, si.part2, si.part3, si.part4,
si.part5, si.part6, si.part7, si.part8,
si.part9, si.part10, si.part11, si.part12,
si.part13, si.part14, si.part15, si.part16
from
'informix'.sysconstraints sc,
'informix'.sysindexes si
where
sc.tabid = ?
and sc.constrtype = 'P'
and si.tabid = sc.tabid
and si.idxname = sc.idxname";
command.Parameters.Clear();
command.Parameters.Add("sc.tabid", tabid);
var parts = new List <int>();
using (var reader = command.ExecuteReader()) {
if (reader.Read())
{
for (int i = 0; i < 16; i++)
{
int part = Convert.ToInt32(reader.GetValue(i));
if (part == 0)
{
break;
}
parts.Add(part);
}
}
}
command.CommandText = "select trim(sc.colname) from 'informix'.syscolumns sc where sc.tabid = ? and sc.colno = ?";
command.Parameters.Clear();
command.Parameters.Add("sc.tabid", tabid);
command.Parameters.Add("sc.colno", null);
foreach (int part in parts)
{
command.Parameters["sc.colno"].Value = part;
string columnName = command.ExecuteScalar() as string;
columnNames.Add(columnName);
}
}
return(new Key(columnNames));
}
private static void LoadRecords(IfxConnection cn, string connectionString, string tableName)
{
DatabaseSchemaHelper databaseSchemaHelper = new DatabaseSchemaHelper(connectionString);
var schemaColumns = databaseSchemaHelper.GetColumns(tableName);
string[] columnNames = (from schemaColumn in schemaColumns select schemaColumn.ActualName).ToArray();
string joinedColumnNames = string.Join(", ", columnNames);
int numColumns = columnNames.Length;
// Load the .unl file (renamed to .txt and added to the project as a resource file).
string fileContents = Resources.Resources.ResourceManager.GetString(tableName);
string[] lines = fileContents.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
string[] placeHolders = new string[numColumns];
for (int i = 0; i < numColumns; i++)
{
placeHolders[i] = "?";
}
string joinedPlaceHolders = string.Join(", ", placeHolders);
using (var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, joinedColumnNames, joinedPlaceHolders);
for (int i = 0; i < numColumns; i++)
{
cmd.Parameters.Add(columnNames[i], null);
}
foreach (var line in lines)
{
string[] values = line.Split('|');
// "value1|value2|value3|" after splitting on '|' yields "value1", "value2", value3, ""
// i.e. there is an empty extra value at the end of the array.
// And, there is usually a blank line at the end of the file.
if (values.Length != (numColumns + 1))
{
break;
}
for (int i = 0; i < numColumns; i++)
{
if (!string.IsNullOrEmpty(values[i]))
{
// This is a temporary hack for the "lead_time" column of the "manufact" table.
// We should lookup the coltype and collength of the column
// using tableName and schemaColumns[i].ActualName. Then, if coltype
// is 14 (INTERVAL) then we need to look at collength to determine the
// detailed type of INTERVAL. Then, create a new instance of IfxTimeSpan
// or IfxMonthSpan and parse the string, values[i]. There may be other column types
// that need to be handled specially. I guess we need to test lots more
// different column types.
//
// SYSCOLUMNS
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_025.htm
//
// Storing Column Length
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_027.htm
//
// Using the Informix System Catalogs
// http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
//
// collength = 836 = 0x344
// length = 3
// first_qualifier = 4
// last_qualifier = 4
//
if (tableName == "manufact" && schemaColumns[i].ActualName == "lead_time")
{
cmd.Parameters[i].Value = new IfxTimeSpan(Convert.ToInt32(values[i]), IfxTimeUnit.Day);
}
else
{
cmd.Parameters[i].Value = values[i];
}
}
else
{
cmd.Parameters[i].Value = DBNull.Value;
}
}
cmd.ExecuteNonQuery();
}
}
}
public static IEnumerable<ForeignKey> GetForeignKeys(Table table, IfxConnection cn)
{
var fkqis = new List<ForeignKeyQueryInfo>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select st.tabid from 'informix'.systables st where st.tabname = ?";
command.Parameters.Add("st.tabname", table.ActualName);
int? tabid = null;
try {
tabid = command.ExecuteScalar() as int?;
}
catch (Exception) {
}
if (tabid.HasValue) {
command.CommandText = @"
select
si.part1, si.part2, si.part3, si.part4,
si.part5, si.part6, si.part7, si.part8,
si.part9, si.part10, si.part11, si.part12,
si.part13, si.part14, si.part15, si.part16,
trim(st.tabname) as fktabname,
si.tabid as fktabid,
trim(rt.tabname) as pktabname,
rc.tabid as pktabid,
sr.primary as pkconstraintid
from
'informix'.systables st,
'informix'.sysconstraints sc,
'informix'.sysindexes si,
'informix'.sysreferences sr,
'informix'.systables rt,
'informix'.sysconstraints rc
where
st.tabid = ?
and st.tabid = sc.tabid
and sc.constrtype = 'R'
and sc.constrid = sr.constrid
and sc.tabid = si.tabid
and sc.idxname = si.idxname
and rt.tabid = sr.ptabid
and rc.tabid = sr.ptabid
and sr.primary = rc.constrid";
command.Parameters.Clear();
command.Parameters.Add("st.tabid", tabid);
using (var reader = command.ExecuteReader()) {
for (; reader.Read(); ) {
var fkqi = new ForeignKeyQueryInfo();
fkqi.FKTabName = reader["fktabname"] as string;
fkqi.FKTabId = Convert.ToInt32(reader["fktabid"]);
fkqi.PKTabName = reader["pktabname"] as string;
fkqi.PKTabId = Convert.ToInt32(reader["pktabid"]);
fkqi.PKConstraintId = Convert.ToInt32(reader["pkconstraintid"]);
for (int i = 0; i < 16; i++) {
int part = Convert.ToInt32(reader.GetValue(i));
if (part == 0) break;
fkqi.FKParts.Add(part);
}
fkqis.Add(fkqi);
}
}
command.CommandText = @"
select
part1, part2, part3, part4,
part5, part6, part7, part8,
part9, part10, part11, part12,
part13, part14, part15, part16
from
'informix'.sysindexes si,
'informix'.sysconstraints sc
where
si.tabid = sc.tabid
and si.idxname = sc.idxname
and sc.constrid = ?";
command.Parameters.Clear();
command.Parameters.Add("sc.constrid", null);
foreach (var fkqi in fkqis) {
command.Parameters["sc.constrid"].Value = fkqi.PKConstraintId;
using (var reader = command.ExecuteReader()) {
for (; reader.Read(); ) {
for (int i = 0; i < 16; i++) {
int part = Convert.ToInt32(reader.GetValue(i));
if (part == 0) break;
fkqi.PKParts.Add(part);
}
}
}
}
command.CommandText = "select trim(sc.colname) from 'informix'.syscolumns sc where sc.tabid = ? and sc.colno = ?";
command.Parameters.Clear();
command.Parameters.Add("sc.tabid", null);
command.Parameters.Add("sc.colno", null);
foreach (var fkqi in fkqis) {
command.Parameters["sc.tabid"].Value = fkqi.FKTabId;
foreach (int part in fkqi.FKParts) {
command.Parameters["sc.colno"].Value = part;
string columnName = command.ExecuteScalar() as string;
fkqi.FKColumnNames.Add(columnName);
}
command.Parameters["sc.tabid"].Value = fkqi.PKTabId;
foreach (int part in fkqi.PKParts) {
command.Parameters["sc.colno"].Value = part;
string columnName = command.ExecuteScalar() as string;
fkqi.PKColumnNames.Add(columnName);
}
}
}
var results =
from fkqi in fkqis
select new ForeignKey(
detailTable: new ObjectName(null, fkqi.FKTabName),
columns: fkqi.FKColumnNames,
masterTable: new ObjectName(null, fkqi.PKTabName),
masterColumns: fkqi.PKColumnNames);
return results.ToArray();
}
private static void LoadRecords(IfxConnection cn, string connectionString, string tableName)
{
DatabaseSchemaHelper databaseSchemaHelper = new DatabaseSchemaHelper(connectionString);
var schemaColumns = databaseSchemaHelper.GetColumns(tableName);
string[] columnNames = (from schemaColumn in schemaColumns select schemaColumn.ActualName).ToArray();
string joinedColumnNames = string.Join(", ", columnNames);
int numColumns = columnNames.Length;
// Load the .unl file (renamed to .txt and added to the project as a resource file).
string fileContents = Resources.Resources.ResourceManager.GetString(tableName);
string[] lines = fileContents.Split(new[] { Environment.NewLine }, StringSplitOptions.None);
string[] placeHolders = new string[numColumns];
for (int i = 0; i < numColumns; i++) {
placeHolders[i] = "?";
}
string joinedPlaceHolders = string.Join(", ", placeHolders);
using (var cmd = cn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, joinedColumnNames, joinedPlaceHolders);
for (int i = 0; i < numColumns; i++) {
cmd.Parameters.Add(columnNames[i], null);
}
foreach (var line in lines) {
string[] values = line.Split('|');
// "value1|value2|value3|" after splitting on '|' yields "value1", "value2", value3, ""
// i.e. there is an empty extra value at the end of the array.
// And, there is usually a blank line at the end of the file.
if (values.Length != (numColumns + 1)) {
break;
}
for (int i = 0; i < numColumns; i++) {
if (!string.IsNullOrEmpty(values[i])) {
// This is a temporary hack for the "lead_time" column of the "manufact" table.
// We should lookup the coltype and collength of the column
// using tableName and schemaColumns[i].ActualName. Then, if coltype
// is 14 (INTERVAL) then we need to look at collength to determine the
// detailed type of INTERVAL. Then, create a new instance of IfxTimeSpan
// or IfxMonthSpan and parse the string, values[i]. There may be other column types
// that need to be handled specially. I guess we need to test lots more
// different column types.
//
// SYSCOLUMNS
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_025.htm
//
// Storing Column Length
// http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_027.htm
//
// Using the Informix System Catalogs
// http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
//
// collength = 836 = 0x344
// length = 3
// first_qualifier = 4
// last_qualifier = 4
//
if (tableName == "manufact" && schemaColumns[i].ActualName == "lead_time") {
cmd.Parameters[i].Value = new IfxTimeSpan(Convert.ToInt32(values[i]), IfxTimeUnit.Day);
}
else {
cmd.Parameters[i].Value = values[i];
}
}
else {
cmd.Parameters[i].Value = DBNull.Value;
}
}
cmd.ExecuteNonQuery();
}
}
}
// http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
// http://stackoverflow.com/questions/320045/how-do-i-get-constraint-details-from-the-name-in-informix
public static Key GetPrimaryKey(Table table, IfxConnection cn)
{
var columnNames = new List<string>();
cn.Open();
var command = cn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select st.tabid from 'informix'.systables st where st.tabname = ?";
command.Parameters.Add("st.tabname", table.ActualName);
int? tabid = null;
try {
tabid = command.ExecuteScalar() as int?;
}
catch (Exception) {
}
if (tabid.HasValue) {
command.CommandText = @"
select
si.part1, si.part2, si.part3, si.part4,
si.part5, si.part6, si.part7, si.part8,
si.part9, si.part10, si.part11, si.part12,
si.part13, si.part14, si.part15, si.part16
from
'informix'.sysconstraints sc,
'informix'.sysindexes si
where
sc.tabid = ?
and sc.constrtype = 'P'
and si.tabid = sc.tabid
and si.idxname = sc.idxname";
command.Parameters.Clear();
command.Parameters.Add("sc.tabid", tabid);
var parts = new List<int>();
using (var reader = command.ExecuteReader()) {
if (reader.Read()) {
for (int i = 0; i < 16; i++) {
int part = Convert.ToInt32(reader.GetValue(i));
if (part == 0) break;
parts.Add(part);
}
}
}
command.CommandText = "select trim(sc.colname) from 'informix'.syscolumns sc where sc.tabid = ? and sc.colno = ?";
command.Parameters.Clear();
command.Parameters.Add("sc.tabid", tabid);
command.Parameters.Add("sc.colno", null);
foreach (int part in parts) {
command.Parameters["sc.colno"].Value = part;
string columnName = command.ExecuteScalar() as string;
columnNames.Add(columnName);
}
}
return new Key(columnNames);
}
static void CheckSequenceGaps()
{
Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
try
{
using (IfxConnection connection = EstablishConnection())
using (var dataContext = new DatabaseSyncDataContext())
{
var missingTransactions = dataContext.GetTransactionSequenceNrGaps(CurrentMonth, FollowingMonth);
foreach (var missingtrans in missingTransactions)
{
try
{
Log.LogTrace($"Requesting {missingtrans.Lane}:{missingtrans.Sequencenr}");
IfxCommand command = connection.CreateCommand();
command.CommandText = $"SELECT * FROM p_trans WHERE ln_id='{missingtrans.Lane}' AND tx_seq_nr='{missingtrans.Sequencenr}'";
IfxDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Log.LogTrace($"Found {missingtrans.Lane}:{missingtrans.Sequencenr}");
var trans = new StagingTransaction();
try
{
int i = 0;
trans.pl_id = dataReader[i].ToString().Trim();
trans.ln_id = dataReader[++i].ToString().Trim();
trans.dt_concluded = ExtractDatetimeValue(dataReader[++i]).Value;
trans.tx_seq_nr = int.TryParse(dataReader[++i].ToString(), out var outInt)
? outInt
: throw new InvalidDataException("Invalid transaction sequence number.");
trans.ts_seq_nr = ExtractIntegerValue(dataReader[++i].ToString());
trans.us_id = dataReader[++i].ToString().Trim();
trans.ent_plz_id = dataReader[++i].ToString().Trim();
trans.ent_lane_id = dataReader[++i].ToString().Trim();
trans.dt_started = ExtractDatetimeValue(dataReader[++i]);
trans.next_inc = ExtractIntegerValue(dataReader[++i].ToString());
trans.prev_inc = ExtractIntegerValue(dataReader[++i].ToString());
trans.ft_id = ExtractIntegerValue(dataReader[++i].ToString());
trans.pg_group = ExtractIntegerValue(dataReader[++i].ToString());
trans.cg_group = ExtractIntegerValue(dataReader[++i].ToString());
trans.vg_group = dataReader[++i].ToString().Trim();
trans.mvc = dataReader[++i].ToString().Trim();
trans.avc = dataReader[++i].ToString().Trim();
trans.svc = dataReader[++i].ToString().Trim();
trans.loc_curr = dataReader[++i].ToString().Trim();
trans.loc_value = dataReader.GetIfxDecimal(++i).ToString();
trans.ten_curr = dataReader[++i].ToString().Trim();
trans.ten_value = dataReader.GetIfxDecimal(++i).ToString();
trans.loc_change = dataReader.GetIfxDecimal(++i).ToString();
trans.variance = dataReader.GetIfxDecimal(++i).ToString();
trans.er_id = ExtractIntegerValue(dataReader[++i].ToString());
trans.pm_id = dataReader[++i].ToString().Trim();
trans.card_nr = dataReader[++i].ToString().Trim();
trans.mask_nr = dataReader[++i].ToString().Trim();
trans.bin_nr = dataReader[++i].ToString().Trim();
trans.serv_code = dataReader[++i].ToString().Trim();
trans.ca_id = dataReader[++i].ToString().Trim();
trans.ct_id = dataReader[++i].ToString().Trim();
trans.it_id = dataReader[++i].ToString().Trim();
trans.sec_card_nr = dataReader[++i].ToString().Trim();
trans.lm_id = dataReader[++i].ToString().Trim();
trans.as_id = dataReader[++i].ToString().Trim();
trans.reg_nr = dataReader[++i].ToString().Trim();
trans.vouch_nr = ExtractIntegerValue(dataReader[++i].ToString());
trans.ac_nr = dataReader[++i].ToString().Trim();
trans.rec_nr = dataReader[++i].ToString().Trim();
trans.tick_nr = dataReader[++i].ToString().Trim();
trans.bp_id = ExtractIntegerValue(dataReader[++i].ToString());
trans.fg_id = ExtractIntegerValue(dataReader[++i].ToString());
trans.dg_id = ExtractIntegerValue(dataReader[++i].ToString());
trans.rd_id = dataReader[++i].ToString().Trim();
trans.rep_indic = dataReader[++i].ToString().Trim();
trans.maint_indic = dataReader[++i].ToString().Trim();
trans.req_indic = ExtractIntegerValue(dataReader[++i].ToString());
trans.iv_prt_indic = dataReader[++i].ToString().Trim();
trans.ts_dt_started = ExtractDatetimeValue(dataReader[++i]);
trans.iv_nr = ExtractIntegerValue(dataReader[++i].ToString());
trans.td_id = ExtractIntegerValue(dataReader[++i].ToString());
trans.avc_seq_nr = ExtractIntegerValue(dataReader[++i].ToString());
trans.update_us_id = dataReader[++i].ToString().Trim();
trans.card_bank = dataReader[++i].ToString().Trim();
trans.card_ac_nr = dataReader[++i].ToString().Trim();
trans.tg_mfg_id = dataReader[++i].ToString().Trim();
trans.tg_post_bal = dataReader.GetIfxDecimal(++i).ToString();
trans.tg_reader = dataReader[++i].ToString().Trim();
trans.tg_us_cat = dataReader[++i].ToString().Trim();
trans.tg_card_type = dataReader[++i].ToString().Trim();
trans.tg_serv_prov_id = dataReader[++i].ToString().Trim();
trans.tg_issuer = dataReader[++i].ToString().Trim();
trans.tg_tx_seq_nr = ExtractIntegerValue(dataReader[++i].ToString());
trans.etc_context_mrk = dataReader[++i].ToString().Trim();
trans.etc_manufac_id = ExtractIntegerValue(dataReader[++i].ToString());
trans.etc_beacon_id = ExtractIntegerValue(dataReader[++i].ToString());
trans.etc_contract_pv = dataReader[++i].ToString().Trim();
trans.avc_dt_concluded = ExtractDatetimeValue(dataReader[++i]);
trans.avc_status = dataReader[++i].ToString().Trim();
trans.anpr_vln = dataReader[++i].ToString().Trim();
trans.anpr_conf = ExtractIntegerValue(dataReader[++i].ToString());
trans.lvc = dataReader[++i].ToString().Trim();
trans.inc_ind = ExtractIntegerValue(dataReader[++i].ToString());
trans.id_vl = dataReader[++i].ToString().Trim();
trans.vl_vln = dataReader[++i].ToString().Trim();
trans.anpr_seq_nr = ExtractIntegerValue(dataReader[++i].ToString());
if (!dataContext.ImportedTransactions.Any(x => x.ln_id == trans.ln_id &&
x.tx_seq_nr == trans.tx_seq_nr &&
x.dt_concluded == trans.dt_concluded))
{
dataContext.ImportedTransactions.Insert(trans);
}
else
{
Log.LogTrace($"Trans already exists {trans.ln_id} : {trans.tx_seq_nr} : {trans.dt_concluded}");
}
}
catch (Exception ex)
{
Log.LogException(ex);
}
}
dataContext.Save();
dataReader.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
}
catch (Exception ex)
{
Log.LogException(ex);
}
Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
}
private static void TableCounts()
{
using (var dataContext = new DatabaseSyncDataContext())
using (IfxConnection connection = EstablishConnection())
{
IfxCommand command = connection.CreateCommand();
command.CommandText =
$"select count(*), ln_id, tx_seq_nr " +
$"from informix.p_trans " +
$"where dt_concluded >= TO_DATE('2018-11-01 00:00:00', '%Y-%m-%d %H:%M:%S') " +
$"and dt_concluded < TO_DATE('2018-12-01 00:00:00','%Y-%m-%d %H:%M:%S') and ln_id='04RS' " +
$"Group by ln_id,tx_seq_nr " +
$"order by tx_seq_nr ";
IfxDataReader dataReader = command.ExecuteReader();
List <string> s = new List <string>();
while (dataReader.Read())
{
var d = $"{dataReader[0].ToString().Trim()} : {dataReader[1].ToString().Trim()}";
s.Add(d);
//if (!dataContext.ImportedTransactions.Any(x => x.ln_id == "03RS" && x.tx_seq_nr.ToString() == d))
// Console.WriteLine(d);
}
File.WriteAllLines("seqNrs.txt", s);
/*IfxCommand command2 = connection.CreateCommand();
* command2.CommandText =
* $"select count(*) from informix.p_trans where dt_concluded >= TO_DATE('2018-11-01 00:00:00', '%Y-%m-%d %H:%M:%S') and dt_concluded<TO_DATE('2018-12-01 00:00:00','%Y-%m-%d %H:%M:%S')";
*
*
*
*
* IfxDataReader dataReader2 = command2.ExecuteReader();
*
* while (dataReader2.Read())
* {
* Console.WriteLine(dataReader2[0].ToString().Trim());
* }
*
* IfxCommand command3 = connection.CreateCommand();
* command3.CommandText =
* $"select count(*) from informix.p_trans where dt_concluded >= TO_DATE('2018-12-01 00:00:00', '%Y-%m-%d %H:%M:%S') and dt_concluded<TO_DATE('2019-01-01 00:00:00','%Y-%m-%d %H:%M:%S')";
*
*
*
*
* IfxDataReader dataReader3 = command3.ExecuteReader();
*
* while (dataReader3.Read())
* {
* Console.WriteLine(dataReader3[0].ToString().Trim());
* }
*
* IfxCommand command4 = connection.CreateCommand();
* command4.CommandText =
* $"select count(*) from informix.p_trans where dt_concluded >= TO_DATE('2019-01-01 00:00:00', '%Y-%m-%d %H:%M:%S') and dt_concluded<TO_DATE('2019-02-01 00:00:00','%Y-%m-%d %H:%M:%S')";
*
*
*
*
* IfxDataReader dataReader4 = command4.ExecuteReader();
*
* while (dataReader4.Read())
* {
* Console.WriteLine(dataReader4[0].ToString().Trim());
* }*/
}
}
