PROCEDURE MakeNewTableLine lParameters mtable, mFile * * Procedure to create the commands to create new tables * select 0 use (mtable) alias dbf_file * * mIndex = '' * IF .T. * * Create the Index command * NOTE: Only create the non K_ keys * The K_ Keys are created later. * for i = 1 to tagcount() do case case left(key(i), 2) = 'K_' loop case left(key(i), 8) = 'ALLTRIM(' * Can't have alltrim mkey = strtran(substr(key(i), 9), ')', '') case left(key(i), 6) = 'UPPER(' * can't have upper and don't need it anyway with text field type mkey = strtran(substr(key(i), 7), ')', '') otherwise mkey = key(i) endcase * * Added 11-19-2004 jjh * if upper still there or dtoc then just skip this key. * we need the table more than the index. if 'DTOC' $ mkey or 'UPPER' $ mkey or 'TTOC' $ mkey ? 'Index: ' + mKey + ' Skipped in table: '+ mtable loop endif * * convert a few things mkey = strtran(mkey, '+', ', ') mkey = strtran(mkey, 'DESC', 'DESCR') mtag = strtran(tag(i), 'DESC', 'DESCR') * mIndex = mIndex + [ " ;] + chr(13) ; + [+ ", ] + iif(unique(i), ' UNIQUE ', '') ; + 'INDEX ' + mtag +' ('+ mkey +')' endfor ENDIF * dimension aflds(1) m.lnFieldcount = AFIELDS(aflds) && Create array m.maxflds = m.lnFieldcount * mline = 'mcommand = "CREATE TABLE ' + mFile + ' (" ;' + iLf =FWRITE( m.lnFHandle, mline ) * FOR m.nn = 1 TO m.maxflds * ********************************* * Test for valid field names * DESC is in a number of our fields but is a reserved word if aflds(m.nn,1) == 'DESC' aflds(m.nn,1) = 'DESCR' endif if aflds(m.nn,1) = 'DEFAULT' aflds(m.nn,1) = 'DEFLT' endif if aflds(m.nn,1) == 'TO' aflds(m.nn,1) = 'TONAME' endif if aflds(m.nn,1) = 'FROM' aflds(m.nn,1) = 'FROMNM' endif * * CHANGE NAME FOR LOGICAL FIELDS if aflds(m.nn,2)='L' aflds(m.nn,1) = 'L_' +aflds(m.nn,1) endif ********************************* if m.nn = 1 mline = ' ' + aflds(m.nn,1) + ' ' else mline = ', ' + aflds(m.nn,1) + ' ' endif * do case * Changed CHAR TYPE 3-4-2005 jjh * From CHAR type to VARCHAR type - supposed to be faster * and to handel encrypted fields (varbinary) if jssys * case aflds(m.nn,2)='C' and mTable = 'JSSYS' * binary to hold passwords mline = mline +'varbinary'+'('+padr(aflds(m.nn,3),3)+')' case aflds(m.nn,2)='C' mline = mline +'varchar'+'('+padr(aflds(m.nn,3),3)+')' case aflds(m.nn,2)='I' mline = mline +'int'+'('+padr(aflds(m.nn,3),3)+')' case aflds(m.nn,2)='N' do case case empty(aflds(m.nn,4)) and aflds(m.nn,3) <=2 mline = mline +'tinyint'+'('+padr(aflds(m.nn,3),3)+')' case empty(aflds(m.nn,4)) mline = mline +'int'+'('+padr(aflds(m.nn,3),3)+')' otherwise mline = mline +'float'+'('+padr(aflds(m.nn,3),3)+','+padr(aflds(m.nn,4),2)+')' endcase case aflds(m.nn,2)='D' mline = mline +'date' case aflds(m.nn,2)='T' mline = mline +'datetime' case aflds(m.nn,2)='M' mline = mline +'text' case aflds(m.nn,2)='L' mline = mline +'char(1)' otherwise: mline = mline +'***' && Field type not recognized - we have problem =MessageBox('Problem in table ' + mtable ; + ' With Field ' + aflds(m.nn,1) + iLf ; + 'Field type not recognized.', 48, 'Convert') endcase * if m.nn = m.maxflds mline = '+ "' + mline + ' NOT NULL ' ; + mIndex + ' ) TYPE = InnoDB ;"' + iLf else mline = '+ "' + mline + ' NOT NULL " ;' + iLf endif * =FWRITE( m.lnFHandle, mline ) endfor * use in dbf_file * RETURN * * ------------------------------------------------------------------------- * PROCEDURE AddMySqlTable lParameters mCommand, mTable mretval = .t. * wait window nowait mTable * mretval = lRun(mCommand) * if sqlexec(mHandle, mCommand, 'result') < 0 * ? '>>> Error Creating ' + juststem(mTable) * mretval = .f. * endif * RETURN mretval