How to Create Delphi 4 Structure to Map Column Names in Xls to Column Names in SQL

how to create Delphi 4 structure to map column names in XLS to column names in SQL

What you want to do is map one string to another. You can use a simple string list for that.

// Declare the variable somewhere, such as at unit scope or as a field
// of your form class
var
  ColumnNameMap: TStrings;

---

// Somewhere else, such as unit initialization or a class constructor,
// initialize the data structure with the column-name mappings.    
ColumnNameMap := TStringList.Create;
ColumnNameMap.Values['Bgm167 GrosGalns DA'] := 'bgm229/ egm229';

---

// In yet a third place in your code, use something like this to map
// the column name in your input to the real column name in your output.
i := ColumnNameMap.IndexOfName(ColumnName);
if i >= 0 then
  RealColumnName := ColumnNameMap.Values[ColumnName]
else
  RealColumnName := ColumnName;

Later versions of Delphi have the generic TDictionary class. Use TDictionary<string, string>. The TStrings solution I outlined above will have problems if any of the column names can have equals signs in them, but you can mitigate that by changing the NameValueSeparator property.

var
  ColumnNameMap: TDictionary<string, string>;

---

ColumnNameMap := TDictionary<string, string>.Create;
ColumnNameMap.Add('Bgm167 GrosGalns DA', 'bgm229/ egm229');

---

if not ColumnNameMap.TryGetValue(ColumnName, RealColumnName) then
  RealColumnName := ColumnName;

Delphi: Store data in somekind of structure

What you need is the so-called "serialization" mechanism.

1. The standard way

1.1 SaveToStream

In Delphi, we usually implement a SaveToStream method, which will save the content of each object in a destination TStream (either a TFileStream or a TMemoryStream).

You'll have to write the serialization by hand.

1.2 DFM-like streaming

See TWriter / TReader classes.

If you define your data in published properties, you are able to serialize them using those standard Delphi classes.

For some methods able to serialize any TCollection to and from JSON content, see this blog article.

2. The RTTI

See for instance this SO question.

In particular, the new enhanced RTTI (available since Delphi 2010) opens new opportunities to serialization.

3. Use records instead of classes

If each item does not store a lot of content (some integer/boolean), it may make sense to use records instead of objects. For speed and memory consumption/fragmentation, it may be worth it.

Here is some wrapper able to serialize any dynamic array, even containing nested records or dynamic arrays.

4. Use a database engine

Perhaps the better approach is not to have your data stuck in a non-evolving binary form, proprietary to your application. If you want to add a property, you'll have to manage it by hand. Or if you want to access your data from other applications, it may be difficult.

There are a lot of database solutions around - instead of using an external database (like MS SQL, FireBird or Oracle), it could be a good idea to embed the database inside your application (much easier to install). Worth mentioning SQLite which has a lot of wrappers, including our version (which will allow you to change to any other database if you want to use MS SQL or Oracle instead).

You have other solutions around - see this SO question - and if you need performance, take a look at our Big Table library.

Undeclared identifier:

In the code I gave you, notice that the value inside the brackets for the Values property was quoted. It's a string. Maybe you meant to have "smrBgm229GallonsGross" in quotes, too, like this:

ColumnNameMap.Values['smrBgm229GallonsGross'] := 'smrBgm167GallonsGrosssDA';

In your code, the compiler complains that it doesn't recognize the identifier smrBgm229GallonsGross. Look at your code. Have you declared such an identifier? If not, then you can't expect the compiler to know what you're asking of it, and the error message makes perfect sense.

(If you were using Perl, the compiler might have known what you wanted. There are certain situations where a so-called "bareword" will be interpreted as a string literal rather than an identifier. But that's Perl, not Delphi.)

So far, I've only been looking at the first line of code that mentions smrBgm229GallonsGross. However, there are five more lines of code, where you look up the index of the name and then assign values to a variable, and those will need a proper declaration of a variable. In my example, I used the made-up variable name ColumnName to represent the name of whatever input column you happen to be processing at the time. I assumed you would be iterating over a list of columns in a loop, so you would do the same thing for each column, taking a value from your Excel spreadsheet and transferring it into a corresponding column in the database, which was represented by the also-made-up variable name RealColumnName.

SQL select join: is it possible to prefix all columns as 'prefix.*'?

I see two possible situations here. First, you want to know if there is a SQL standard for this, that you can use in general regardless of the database. No, there is not. Second, you want to know with regard to a specific dbms product. Then you need to identify it. But I imagine the most likely answer is that you'll get back something like "a.id, b.id" since that's how you'd need to identify the columns in your SQL expression. And the easiest way to find out what the default is, is just to submit such a query and see what you get back. If you want to specify what prefix comes before the dot, you can use "SELECT * FROM a AS my_alias", for instance.

Is it possible to retrieve a column value by name using GoLang database/sql

Yes, it is possible to do this without having to manually match up the column positions. There are some third-party libraries you can use to do this, such as sqlx or gorp. I would recommend sticking with one of these instead of rolling your own.

Named matching does have a slight penalty. Named matching is no different than matching up the column positions yourself. It just does this work for you at runtime - possibly on every query execution. This is true in any other language.

Why at runtime? The query is written as a string. It has to be parsed to determine the position.

If you were to make your own library, how do you do this on your own?

• Rows.Columns to get column names and positions.

• Passing a slice of pointers []interface{} to Rows.Scan to get the values.

• reflect.Value and Value.Addr to get a pointer to the destination value.

• Value.FieldByName to get the Value of a struct field if you want to map to struct fields.

Ok, so lets see how this works.

type Person struct {
    Id int
    Name string
}
rows, err := db.Query("SELECT id, name FROM person;")
if err != nil {
    // handle err
    log.Fatal(err)
}
columnNames, err := rows.Columns() // []string{"id", "name"}
if err != nil {
    // handle err
    log.Fatal(err)
}
people = make([]Person, 0, 2)
for rows.Next() {
    person := Person{}
    // person == Person{0, ""}
    pointers := make([]interface{}, len(columnNames))
    // pointers == `[]interface{}{nil, nil}`
    structVal := reflect.ValueOf(person)
    for i, colName := range columnNames {
        fieldVal := structVal.FieldByName(strings.Title(colName))
        if !fieldVal.IsValid() {
            log.Fatal("field not valid")
        }
        pointers[i] = fieldVal.Addr().Interface()
    }
    // pointers == `[]interface{}{&int, &string}`
    err := rows.Scan(pointers...)
    if err != nil {
        // handle err
        log.Fatal(err)
    }
    // person == Person{1, "John Doe"}
    people = append(people, person)
}

Convert excel column alphabet (e.g. AA) to number (e.g., 25)

Try:

var foo = function(val) {
  var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', i, j, result = 0;

  for (i = 0, j = val.length - 1; i < val.length; i += 1, j -= 1) {
    result += Math.pow(base.length, j) * (base.indexOf(val[i]) + 1);
  }

  return result;
};

console.log(['A', 'AA', 'AB', 'ZZ'].map(foo)); // [1, 27, 28, 702]

---

Related Topics