Saturday, December 28, 2019

Excel connect with MYSQL database through ODBC

1. MYSQL ODBC driver download

MySQL :: Download Connector/ODBC

WIN 32 or 64 bit

2. From win Start, using "search program and files" to find out ODBC.

3. Add Mysql into User DSN

4. Add ODBC into Excel.

※ Once Mysql table was updated, all push the refresh button, excel sheet will be refreshed at the same time. 

Sunday, November 3, 2019

How to show multiple records with the same primary key in ExtJs Store. ------ Ext store primary key field unavailable

■ Keyword
Extjs Ext Javescript
proxy idProperty
■ Issue
Data.items Data.items
111 A111 111 A111
111 A222        
111 A333    
222 B222 222 B222
■ Solution
  Ext.define(modelName, {  
  extend : "",  
  fields : ["num",  
  proxy: {  
    type: 'ajax',  
    reader: {  
          type: 'json',  
          idProperty: 'undefined'}}  
  store.add(data.items); store.loadRawData(data.items);
idProperty will decide which field will be primary key.
Default is field:"ID".

Monday, October 21, 2019

MySQL5で「Unknown column … in ‘on clause’」

■Error MySQL
Unknown column ... in 'on clause'

Error SQL statement

  • 「FROM a,b」 mutiple table to join
  • 「JOIN」include join statement
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
Unknown column ‘t1.i1’ in ‘on clause’ 


1. SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
2. SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

Saturday, October 19, 2019

Select logic with unconverted value

As the unconverted value is in VBAP table, below fetching logic failed.

CONVERSION_EXIT_VHUPI_INPUT         "300148811" -> "0300148811"
CONVERSION_EXIT_VHUPI_OUTPUT    "0300148811" -> "300148811"

Sunday, July 21, 2019

Mysql UUID

How to created UUID in mysql?

GUID: Globally Unique Identifier
UUID: Universally Unique Identifier

・Select UUID() as UUID.
・Insert Tbl (id, name) values (UUID(), 'test')
・Select replace(UUID(),"-","") as UUID
 ・INSERT INTO t_inventive_principle (`id`,`code_num`,`name`) VALUES (REPLACE(UUID(),"-",""),1,'分割原理');