public void GetDistinctList(string tableName, TPCField field)
{
string query = "SELECT DISTINCT " + field.name + " FROM " + tableName + " ORDER BY " + field.name;
cursor.Format(query, 0);
if (ShowSQL)
{
LogDebug = query;
}
cursor.Run();
}
public void MakeTableColumns(TPCField[] fields)
{
table.Columns.Clear();
foreach (TPCField field in fields)
table.Columns.Add(field.name, getType(field.type));
}
public void GetTable(string tableName, TPCField[] allFields, int offsetFields, int noFields, TPCIndexField[] orderFields, int offsetOrderFields, int noOrderFields)
{
string query = "SELECT";
#if do_it_with_mssql
if (Limit.Length > 0)
{
query += " " + Limit;
}
else
{
query += " TOP 1000";
}
#endif
string comma = " ";
fields = new TPCField[noFields];
for (int i = 0; i < noFields; i++)
{
fields[i] = allFields[offsetFields + i];
query += comma + fields[i].name;
comma = ", ";
}
dataTableGrid = new DataTableGrid(tableName);
dataTableGrid.MakeTableColumns(fields);
query += comma + UsId + ", " + TmStamp + " FROM " + tableName;
if (Lookup.Length > 0)
{
query += " " + Lookup;
}
#if do_it_with_oracle
if (Lookup.Length == 0)
{
query += " WHERE ROWNUM <= 1000";
}
#endif
if (noOrderFields > 0)
{
string orderBy = " ORDER BY";
comma = " ";
for (int i = 0; i < noOrderFields; i++)
{
int no = orderFields[offsetOrderFields + i].index;
TPCField field = fields[no];
orderBy += comma + field.name;
comma = ", ";
}
query += orderBy;
}
#if do_it_with_lite3
if (Limit.Length > 0)
{
query += " " + Limit;
}
else
{
query += " LIMIT 1000";
}
#endif
cursor.Format(query, 0);
if (ShowSQL)
{
LogDebug = query;
}
cursor.Run();
}
int maxLength(TPCField field)
{
switch (field.type)
{
case DBHandler.PC_CHAR:
case DBHandler.PC_USERSTAMP:
case DBHandler.PC_DATE:
case DBHandler.PC_DATETIME:
case DBHandler.PC_TIME:
case DBHandler.PC_TIMESTAMP:
return field.length;
case DBHandler.PC_BOOLEAN:
return 1;
case DBHandler.PC_BYTE:
return 3;
case DBHandler.PC_SHORT:
return 5;
case DBHandler.PC_DOUBLE:
return 20;
case DBHandler.PC_SEQUENCE:
case DBHandler.PC_INT:
case DBHandler.PC_LONG:
return 10;
}
return 0;
}
string sqlReady(TPCField field, string data, bool isInsert = false, string tableName = "")
{
data = data.Trim();
if (data.Length == 0 && field.isNull == 1)
return "NULL";
switch (field.type)
{
case DBHandler.PC_BOOLEAN:
case DBHandler.PC_BYTE:
case DBHandler.PC_SHORT:
case DBHandler.PC_DOUBLE:
case DBHandler.PC_INT:
case DBHandler.PC_LONG:
return data;
case DBHandler.PC_SEQUENCE:
if (isInsert == true)
return tableName + "Seq.NextVal";
return data;
case DBHandler.PC_CHAR:
case DBHandler.PC_USERSTAMP:
return "'" + escape(data) + "'";
case DBHandler.PC_DATE:
return "to_date('" + data + "','YYYYMMDD')";
case DBHandler.PC_DATETIME:
return "to_date('" + data + "','YYYYMMDDHH24MISS')";
case DBHandler.PC_TIME:
return "to_date('" + data + "','HH24MISS')";
case DBHandler.PC_TIMESTAMP:
return "to_date('" + data + "','YYYYMMDDHH24MISS')";
}
return "";
}
public void GetTable(string tableName, TPCField[] allFields, int offsetFields, int noFields, TPCIndexField[] orderFields, int offsetOrderFields, int noOrderFields)
{
string query = "SELECT";
string comma = " ";
fields = new TPCField[noFields];
for (int i=0; i<noFields; i++)
{
fields[i] = allFields[offsetFields + i];
query += comma + fields[i].name;
comma = ", ";
}
dataTableGrid = new DataTableGrid(tableName);
dataTableGrid.MakeTableColumns(fields);
query += comma + UsId + ", " + TmStamp + " FROM " + tableName;
if (Lookup.Length > 0)
query += " " + Lookup;
else
query += " WHERE ROWNUM <= 1000";
if (noOrderFields > 0)
{
string orderBy = " ORDER BY";
comma = " ";
for (int i = 0; i < noOrderFields; i++)
{
int no = orderFields[offsetOrderFields + i].index;
TPCField field = fields[no];
orderBy += comma + field.name;
comma = ", ";
}
query += orderBy;
}
cursor.Format(query, 0);
if (ShowSQL)
LogDebug = query;
cursor.Run();
}
public void GetDistinctList(string tableName, TPCField field)
{
string query = "SELECT DISTINCT " + field.name + " FROM " + tableName + " ORDER BY " + field.name;
cursor.Format(query, 0);
if (ShowSQL)
LogDebug = query;
cursor.Run();
}
