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(); }