Dynamic sql create,import, – Technical Dialogue

We’re getting excel file from buyer
We don’t know the column names of excel file

We now have create desk runtime on the bases of excel column identify.
Import excel into MySQL

We now have use in choose command . dynamic binding columns.

Hello @packs,

At which step of this state of affairs do you’ve got issues?
What did you strive that didn’t work?
Are you able to share a brief model of the code that fails?

We’re getting excel file from buyer .
We don’t know what number of columns are there in excel file .( studying excel column )
We now have to retailer the excel file information into desk ( dynamic column ought to be created from excel column all are textual content discipline )
We now have to indicate the desk information to consumer ( want dynamic construction for scan )

Hello, Prakash,

I like to recommend you look into the Excelize bundle to deal with processing the Excel file: excelize bundle – github.com/xuri/excelize/v2 – Go Packages.

You must have the ability to generate the CREATE TABLE command fairly simply right here (I haven’t examined this, but it surely ought to be a great start line to generate the DDL to create the desk. Written for Microsoft SQL Server, but it surely ought to be simple to tweak it to work with different RDBMS’s):

kind textColumn struct {
    identify string
    size int
}

func createCreateTableSQLForTextColumnTable(tableName string, textColumns []textColumn) string {
    sb := strings.Builder{}
    sb.WriteString("CREATE TABLE ")
    sb.WriteString(tableName)
    sb.WriteString(" (n")
    for i, textCol := vary textColumns {
        sb.WriteByte('t')
        sb.WriteString(textcol.identify)
        sb.WriteString(" VARCHAR(")
        sb.WriteString(strings.Atoi(textCol.size))
        sb.WriteString(",n")
    }
    sb.WriteString(");")
    return sb.String()
}

Why do you want a dynamic construction? As a result of the columns are all strings, can’t you simply name (*sql.Rows).Scan/(*sql.Row).Scan and scan right into a []string slice?

func scanTextColumnTable(scanner interface{ Scan(...interface{}) error }, fields []string) error {
    vs := make([]interface{}, len(fields))
    for i := vary fields {
        vs[i] = &fields[i]
    }
    return scanner.Scan(vs...)
}

func scanTextColumnTable(scanner interface{ Scan(…interface{}) error, fields []string) error {
vs := make([]interface{}, len(fields))
for i := vary fields {
vs[i] = &fields[i]
}
return scanner.Scan(vs…)
}

this operate isn’t clear .
how I’ll get discipline names

`outcome [][]string
_Columnname = []string{}
_Columnwidth = []int{}

	allrows, err := tx.Queryx(appquery.Select_ccsall)
		_Columnname, err = allrows.Columns()

		size := len(_Columnname)

		pointers := make([]interface{}, size)
		container := make([]string, size)
		for i, _ := vary pointers {
			pointers[i] = &container[i]
		}

		defer allrows.Shut()
		for allrows.Subsequent() {
			/*
				pointers := make([]interface{}, size)
				container := make([]string, size)

				for i := vary pointers {
					pointers[i] = &container[i]
				}

			*/
			allrows.Scan(pointers...)
			outcome = append(outcome, container)
		}

`

this technique is giving output in array format . we want json format

`func queryToJson(db *sql.DB, question string, args …interface{}) ([]byte, error) {
// an array of JSON objects
// the map secret’s the sector identify
var objects []map[string]interface{}

rows, err := db.Question(question, args...)
if err != nil {
	return nil, err
}
for rows.Subsequent() {
	// determine what columns had been returned
	// the column names would be the JSON object discipline keys
	columns, err := rows.ColumnTypes()
	if err != nil {
		return nil, err
	}

	// Scan wants an array of tips that could the values it's setting
	// This creates the thing and units the values appropriately
	values := make([]interface{}, len(columns))
	object := map[string]interface{}{}
	for i, column := vary columns {
		object[column.Name()] = mirror.New(column.ScanType()).Interface()
		values[i] = object[column.Name()]
	}

	err = rows.Scan(values...)
	if err != nil {
		return nil, err
	}

	objects = append(objects, object)
}

// indent as a result of I wish to learn the output
return json.MarshalIndent(objects, "", "t")

}
`

this code working for me , I would like column in similar order as within the database desk.

You would:

1. Use your database’s INFORMATION_SCHEMA (or no matter else your explicit RDBMS makes use of to explain its schema).
2. Or you may retailer the data your self (you may create a desk to maintain observe of your dynamic tables’ column names).
3. Or you may use the (*sql.Rows).Columns operate to get the column names.

I assume you imply that you really want the output to appear like {"columnName0": "columnValue0", "columnName1": "columnValue1"}. In that case, you may mix the contents of _Columnname and container right into a map earlier than you come it:

m := make(map[string]string, len(_Columnname))
for i, ok := vary _Columnname {
    m[k] = container[i]
}
return m  // For those who cross this to json.Marshal, you're going to get a JSON object.

That is tough! In Go, maps are unordered and JSON objects are unordered per the JSON specification, so that you’ll have to search out some third social gathering JSON library to deal with it. Perhaps somebody right here could have a suggestion.

Have you learnt any library for ordering column in database desk ( discipline order )

Anyone is having any choices. My buyer isn’t accepting the alphabetic order . He need information ought to visualized in accordance with the tables within the discipline.

for this growth we’re able to pay.

I looked for “golang ordered json” and located this bundle that looks as if it’ll do what you need: GitHub – virtuald/go-ordered-json: A fork of the golang encoding/json bundle to assist ordered JSON decoding/encoding

That being stated, why does the shopper care about what the JSON appears like? The net utility (or no matter consumer interface) ought to have the ability to present the info in any order whatever the JSON format.

This matter was mechanically closed 90 days after the final reply. New replies are now not allowed.