Subject Re: complex indexes vs index key fields From Mervyn Bick <invalid@invalid.invalid> Date Wed, 29 Jul 2020 16:24:44 +0200 Newsgroups dbase.getting-started Attachment(s) load_mysql_energydata.prg On 2020-07-28 14:00, Mervyn Bick wrote: > Firebird does not accept multiple rows in a single INSERT but I've > modified my loading program so that Gaetano can try it.  This program > has NOT been tested as I don't have MySql available. There was a typo which has been fixed.  I've also added code to create the table if it doesn't exist.  If the table exists the try...catch...endtry will prevent an error.   The revised version is attached. > The program takes less than a second to generate the 8 INSERT statements > requires to deal with the 7577 records.  Executing them should not take > too many seconds.  :-) The program took 4.90 seconds to create the table and then load the 7576 test records I have. I have created an ODBC connection named energydata  which has the username and password hard-coded which accounts for the brief connection string.  This will, of course, need to be changed. We've gone off-topic here so let's start a new thread in the ADO or the sql-servers newsgroup. Mervyn. clear s = seconds() d = new adodatabase() d.connectionString = "Provider=MSDASQL.1;Persist Security Info=True;Data Source=energydata" d.active = true try   cStr = "CREATE TABLE energydata ( "   cStr += " siteID CHAR(6)  NOT NULL,"   cStr += "  eTimestamp TIMESTAMP(6) NOT NULL,"   cStr += "  egen FLOAT(10,4) DEFAULT NULL,"   cStr += "  econs FLOAT(10,4) DEFAULT NULL,"   cStr += "  ac FLOAT(10,4) DEFAULT NULL,"   cStr += "  pv_econs FLOAT(10,4) DEFAULT NULL,"   cStr += "  pv_eexp FLOAT(10,4) DEFAULT NULL,"   cStr += "  g_eimport FLOAT(10,4) DEFAULT NULL,"   cStr += "  PRIMARY KEY USING BTREE (siteID, eTimestamp))"   d.ExecuteSQL(cStr) catch(exception e)   ?e.message, 'Table exists' endtry _app.allowYeildOnMsg = true f = new file() f.open('d:\examples\ado\energydata2.csv') cRead = f.gets(1000) //header n = 0 do while not f.eof()    n1 = 1    cStr = "insert into energydata (siteId,eTimestamp,egen,econs,ac,pv_econs,"    cStr += "pv_eexp,g_eimport) values "    do while n1 < 1000 and not f.eof()      cRead = f.gets(10000)      cStr += "("+parse_cRead(cRead)+"),"      _app.executeMessages()      if n%500 = 0         ?n      endif      n++      n1++        enddo    cStr = substr(cStr,1,len(cStr)-1) //get rid of last comma    ?'insert'    d.ExecuteSQL(cStr) enddo d.ExecuteSQL('commit') f.close() d.active = false ? n,' Records added ' ?'seconds',(seconds()-s) function parse_cRead(cRead)    //Excludes site field from the INSERT. Wraps siteID and eTimestamp in single quotes    //Adds NULL in empty fields in the .csv file.      local cRet    cRet = "'"+substr(cRead,1,at(',',cRead,1)-1)+"','"    cTs = substr(cRead,at(',',cRead,2)+1,(at(',',cRead,3)-1)-at(',',cRead,2))  +"',"    cRet += convert_ts(cTs)    cRet += substr(cRead,at(',',cRead,3)+1)    do while ",,"$cRet       cRet = stuff(cRet,at(',,',cRet),2,",NULL,")       if substr(cRet,len(cRet),1) = ','          cRet += 'NULL'       endif      enddo      return cRet    function convert_ts(cTs)    //Reformats the timestamp as YYYY-MM-DD HH:MM:SS    local cRet,cY,cH,cM,cS    if not 'A'$cTs and not 'P'$cTs        cRet = cTs    else       cY = substr(cTs,1,9)       cY = stuff(cy,7,0,'-')       cY = stuff(cy,5,0,'-')       if 'P'$cTs and val(substr(cTs,10,2)) = 12         cH = '00'       elseif 'P'$cTs and val(substr(cTs,10,2)) < 12         cH = val(substr(cTs,10,2))+12       else         cH = substr(cTs,10,2)       endif               cM = substr(cTs,13,2)       cS = ":00',"         cRet = cY+cH+':'+cM+cS    endif    return cRet