Weblabor

Konzolban tudsz fájlból importálni, és szerintem ebből semmi gond nem lehet.

Ékezet probléma akkor van, ha valahol nem UTF-8 a kódolás. Egyszerű, mint a faék.

Hali!

Írd be a script fájl első sorába, hogyEz meg kell, hogy oldja a karakterkódolási problémát. Utána jöhet Poetro megoldása.

Hello

Először is ANSI-ba mentettem le a script-eket...
Egy kollégám adott egy 5-letet/alapot/ aztán onnan kiindulva kissé átírtam, mert kb 120000 sor után elszált OutOfMemory Exception-nel, s ha végigfutott volna akkor is majdnem 3 óra lett volna.
Ez a végleges változat/részemről/, no persze lehet még rajta tuningolni de ez már jó arra, hogy ne rabolja el a művelet feleslegesen az időmet.

Ime a java forrás:


/**
*
* @author ujtordai
* sqlimp
*/
public class sqlimp {
public static String calculateEstimatedTime(long start) {
String estimatedAsText = "";
Calendar cs = Calendar.getInstance();
cs.setTimeInMillis(System.currentTimeMillis() - start);
estimatedAsText += "" + (cs.get(Calendar.HOUR_OF_DAY)-1) + " hour(s) " + cs.get(Calendar.MINUTE) + " mins " + cs.get(Calendar.SECOND) + " secs. " + cs.get(Calendar.MILLISECOND) + " mils.";
cs = null;
return estimatedAsText;
}

/**
* Method: main
* @param args
*/
public static void main(String[] args) {
File file = new File("C:/wbwork/sql/ins_ansi.sql");
Connection connection = null;
BufferedReader bufferedReader = null;
int ix = 0;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "root");
connection.setAutoCommit(false);
System.out.println("Connection is autocommit " + connection.getAutoCommit());
FileInputStream fs = new FileInputStream(file);
InputStreamReader inputStreamReader = new InputStreamReader(fs);
System.out.println(inputStreamReader.getEncoding());
bufferedReader = new BufferedReader(inputStreamReader);
long startTime = System.currentTimeMillis();
String row = null;
// create statement outside/if inside and not use executeBatch OutOfMemory Exception will occurring/ the loop
Statement statement = connection.createStatement();
while ((row = bufferedReader.readLine()) != null) {
row = row.trim();
if (!row.startsWith("-") && !row.isEmpty()) {
statement.addBatch(row);
ix++;
// after every 10000th rows need to commit
if ((ix % 10000) == 0) {
int[] res = statement.executeBatch();
connection.commit();
statement.clearBatch();
System.out.println("" + ix + ". INSERTED Elapsed time " + calculateEstimatedTime(startTime));
}
}
}
//execute and update over the last commit
int[] res = statement.executeBatch();
connection.commit();
statement.clearBatch();
System.out.println("INSERTED " + ix + "rows Elapsed time " + calculateEstimatedTime(startTime));
connection.commit();
connection.close();
bufferedReader.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

És ime a végeredmény:

Connection is autocommit false
Cp1250
10000. INSERTED Elapsed time 0 hour(s) 0 mins 1 secs. 625 mils.
20000. INSERTED Elapsed time 0 hour(s) 0 mins 2 secs. 956 mils.
30000. INSERTED Elapsed time 0 hour(s) 0 mins 4 secs. 389 mils.
40000. INSERTED Elapsed time 0 hour(s) 0 mins 5 secs. 907 mils.
50000. INSERTED Elapsed time 0 hour(s) 0 mins 7 secs. 466 mils.
60000. INSERTED Elapsed time 0 hour(s) 0 mins 8 secs. 861 mils.
70000. INSERTED Elapsed time 0 hour(s) 0 mins 10 secs. 321 mils.
80000. INSERTED Elapsed time 0 hour(s) 0 mins 12 secs. 29 mils.
90000. INSERTED Elapsed time 0 hour(s) 0 mins 13 secs. 249 mils.
100000. INSERTED Elapsed time 0 hour(s) 0 mins 14 secs. 545 mils.
110000. INSERTED Elapsed time 0 hour(s) 0 mins 16 secs. 161 mils.
120000. INSERTED Elapsed time 0 hour(s) 0 mins 17 secs. 614 mils.
130000. INSERTED Elapsed time 0 hour(s) 0 mins 19 secs. 74 mils.
140000. INSERTED Elapsed time 0 hour(s) 0 mins 20 secs. 538 mils.
150000. INSERTED Elapsed time 0 hour(s) 0 mins 22 secs. 2 mils.
160000. INSERTED Elapsed time 0 hour(s) 0 mins 23 secs. 480 mils.
170000. INSERTED Elapsed time 0 hour(s) 0 mins 24 secs. 940 mils.
180000. INSERTED Elapsed time 0 hour(s) 0 mins 26 secs. 721 mils.
190000. INSERTED Elapsed time 0 hour(s) 0 mins 28 secs. 51 mils.
200000. INSERTED Elapsed time 0 hour(s) 0 mins 29 secs. 517 mils.
210000. INSERTED Elapsed time 0 hour(s) 0 mins 30 secs. 774 mils.
220000. INSERTED Elapsed time 0 hour(s) 0 mins 32 secs. 200 mils.
230000. INSERTED Elapsed time 0 hour(s) 0 mins 33 secs. 495 mils.
240000. INSERTED Elapsed time 0 hour(s) 0 mins 35 secs. 471 mils.
250000. INSERTED Elapsed time 0 hour(s) 0 mins 37 secs. 160 mils.
INSERTED 254867rows Elapsed time 0 hour(s) 0 mins 37 secs. 901 mils.

Bocsi ha kicsit off, mert ez nem egy java topik...
Nemtom hogy kell megformázni szépen a source-ot...