Problem with "load data infile..." on 3.23.52 for Windows

Version: 3.23.52 for Windows

I've found that query results via Java programs are missing the first column
in cases where the data has been put into the table using "load data infile
'xxx.txt' into table xxx;" in Windows command prompt, but this problem does
not appear in cases where data was manually input row-by-row using MySQLCC.
Why is that?

My data file consists of 4 columns and 3 rows and is tab-delimited. The
columns are: PID, LAST, MIDDLE, and FIRST; PID is unique number key, and the
others are varchar.

My Java code is:

import java.sql.*;

public class Connect {

public static void main(String[] args) {
String pid;
String last;
String middle, first;

try {
Class.forName("com.mysql.jdbc.Driver");
} catch (LinkageError e_linkage) {
System.out.println(e_linkage);
} catch (ClassNotFoundException e_classNotFound) {
System.out.println(e_classNotFound);
}

try {
Connection conn =
DriverManager.getConnection("jdbc:mysql://pc1:3306/schedule? user=root");
PreparedStatement ps = conn.prepareStatement("select pid, last,
middle, first from junk2");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
pid = rs.getString(1);
last = rs.getString(2);
middle = rs.getString(3);
first = rs.getString(4);
System.out.println(pid + " " + last + " " + middle + "
" + first + " ");
}
rs.close();
} catch (SQLException e_sql) {
System.out.println (e_sql);
}
}
}

In execution, the PID column does not appear in the resulting query unless
the following line is used instead:
System.out.println(" " + pid + " " + last + " " + middle + " " +
first + " "); //Notice the 3 blanks I must insert in the very front.



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Problem with "load data infile..." on 3.23.52 for Windows

Franklin Phan writes:
> Version: 3.23.52 for Windows
>
> I've found that query results via Java programs are missing the first column
> in cases where the data has been put into the table using "load data infile
> 'xxx.txt' into table xxx;" in Windows command prompt, but this problem does
> not appear in cases where data was manually input row-by-row using MySQLCC.
> Why is that?
>
> My data file consists of 4 columns and 3 rows and is tab-delimited. The
> columns are: PID, LAST, MIDDLE, and FIRST; PID is unique number key, and the
> others are varchar.
>

[skip]

>
> In execution, the PID column does not appear in the resulting query unless
> the following line is used instead:
> System.out.println(" " + pid + " " + last + " " + middle + " " +
> first + " "); //Notice the 3 blanks I must insert in the very front.
>
>

Hi!

It is possible that those results are simply empty strings.

Use mysql.exe program and select just that column.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Problem with "load data infile..." on 3.23.52 for Windows

Here's the content of my xxx.txt file:
1 abc a abc
2 abc2 a2 abc2
3 abc3 a3 abc3

(all rows are tab-delimited; first column is PID, second is LAST, third
column is MIDDLE, fourth column is FIRST)

I load it in Windows Command Prompt DOS window in Windows 2000 using:
load data infile "xxx.txt" into table junk2;

When I execute the aforementioned Connect Java class, the result is:
abc a abc
abc2 a2 abc2
abc3 a3 abc3

When I do "select * from junk2" in the Command Prompt DOS window, I can see
all the data in all the columns but in a badly aligned and formatted
display.

If I were to manually insert the rows in MySQLCC, I get the following when I
execute the Connect Java class:
1 abc a abc
2 abc2 a2 abc2
3 abc3 a3 abc3

(All the columns appear.) And when I do a "select * from junk2" in the
Command Prompt DOS window, the result is displayed perfectly.

What's going on?

-----Original Message-----
From: Sinisa Milivojevic [mailto:sinisa@mysql.com]
Sent: Wednesday, April 23, 2003 11:26 AM
To: franklin.phan@mindspring.com
Cc: bugs@lists.mysql.com
Subject: Re: Problem with "load data infile..." on 3.23.52 for Windows


Franklin Phan writes:
> Version: 3.23.52 for Windows
>
> I've found that query results via Java programs are missing the first
column
> in cases where the data has been put into the table using "load data
infile
> 'xxx.txt' into table xxx;" in Windows command prompt, but this problem
does
> not appear in cases where data was manually input row-by-row using
MySQLCC.
> Why is that?
>
> My data file consists of 4 columns and 3 rows and is tab-delimited. The
> columns are: PID, LAST, MIDDLE, and FIRST; PID is unique number key, and
the
> others are varchar.
>

[skip]

>
> In execution, the PID column does not appear in the resulting query unless
> the following line is used instead:
> System.out.println(" " + pid + " " + last + " " + middle + " " +
> first + " "); //Notice the 3 blanks I must insert in the very front.
>
>

Hi!

It is possible that those results are simply empty strings.

Use mysql.exe program and select just that column.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Problem with "load data infile..." on 3.23.52 for Windows

Franklin Phan writes:
> Here's the content of my xxx.txt file:
> 1 abc a abc
> 2 abc2 a2 abc2
> 3 abc3 a3 abc3
>
> (all rows are tab-delimited; first column is PID, second is LAST, third
> column is MIDDLE, fourth column is FIRST)
>
> I load it in Windows Command Prompt DOS window in Windows 2000 using:
> load data infile "xxx.txt" into table junk2;
>
> When I execute the aforementioned Connect Java class, the result is:
> abc a abc
> abc2 a2 abc2
> abc3 a3 abc3
>
> When I do "select * from junk2" in the Command Prompt DOS window, I can see
> all the data in all the columns but in a badly aligned and formatted
> display.
>
> If I were to manually insert the rows in MySQLCC, I get the following when I
> execute the Connect Java class:
> 1 abc a abc
> 2 abc2 a2 abc2
> 3 abc3 a3 abc3
>
> (All the columns appear.) And when I do a "select * from junk2" in the
> Command Prompt DOS window, the result is displayed perfectly.
>
> What's going on?


In order that we test this, please send us CREATE TABLE statement.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Problem with "load data infile..." on 3.23.52 for Windows

Here's the create table statement:

CREATE TABLE PATIENT (
PID smallint(5) NOT NULL,
LAST VARCHAR(20) NOT NULL,
MIDDLE VARCHAR(20),
FIRST VARCHAR(20) NOT NULL,
PRIMARY KEY ( PID, LAST, FIRST )) type=innodb;

You don't even need to use it. The problem occurs even when I used MySQLCC
to create the table. As long as the data is loaded using 'load data infile
"xxx.txt" into table patient', the problem with the first column not
displaying would occur while executing:

import java.sql.*;

public class Connect {

public static void main(String[] args) {
String pid;
String last;
String middle, first;

try {
Class.forName("com.mysql.jdbc.Driver");
} catch (LinkageError e_linkage) {
System.out.println(e_linkage);
} catch (ClassNotFoundException e_classNotFound) {
System.out.println(e_classNotFound);
}

try {
Connection conn =
DriverManager.getConnection("jdbc:mysql://pc1:3306/schedule? user=root");
PreparedStatement ps = conn.prepareStatement("select pid, last,
middle, first from patient");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
pid = rs.getString(1);
last = rs.getString(2);
middle = rs.getString(3);
first = rs.getString(4);
System.out.println(pid + " " + last + " " + middle + "
" + first + " ");
}
rs.close();
} catch (SQLException e_sql) {
System.out.println (e_sql);
}
}
}

-----------------------------------------------------------
Also, the following query result is what I get from the table that was
loaded using 'load data infile "xxx.txt" into table patient". Notice the
mal-formed display of the query result:

F:\mysql\bin>mysql -u root schedule
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.52-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from patient;
+-----+----------+--------+----------+
| PID | LAST | MIDDLE | FIRST |
+-----+----------+--------+----------+
| 1 | Patient1 | | Patient
| 2 | Patient2 | | patient
| 3 | Patient3 | | patient
+-----+----------+--------+----------+
3 rows in set (0.28 sec)

mysql>

This badly formatted display does not occur when the data is manually input
using MySQLCC. What's going on?

The following is my my.ini file:
[client]
port=3306

[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
server-id = 1

basedir = F:/mysql/
datadir = F:/mysql/data/

set-variable = innodb_log_file_size=25M
set-variable = innodb_log_buffer_size=8M

innodb_flush_log_at_trx_commit=1

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=8M
set-variable = sort_buffer=8M

[myisamchk]
set-variable = key_buffer=8M
set-variable = sort_buffer=8M

[mysqlhotcopy]
interactive-timeout
[WinMySQLAdmin]
Server=F:/mysql/bin/mysqld-nt.exe


Franklin

-----Original Message-----
From: Sinisa Milivojevic [mailto:sinisa@mysql.com]
Sent: Wednesday, April 23, 2003 11:58 AM
To: franklin.phan@mindspring.com
Cc: bugs@lists.mysql.com
Subject: RE: Problem with "load data infile..." on 3.23.52 for Windows


Franklin Phan writes:
> Here's the content of my xxx.txt file:
> 1 abc a abc
> 2 abc2 a2 abc2
> 3 abc3 a3 abc3
>
> (all rows are tab-delimited; first column is PID, second is LAST, third
> column is MIDDLE, fourth column is FIRST)
>
> I load it in Windows Command Prompt DOS window in Windows 2000 using:
> load data infile "xxx.txt" into table junk2;
>
> When I execute the aforementioned Connect Java class, the result is:
> abc a abc
> abc2 a2 abc2
> abc3 a3 abc3
>
> When I do "select * from junk2" in the Command Prompt DOS window, I can
see
> all the data in all the columns but in a badly aligned and formatted
> display.
>
> If I were to manually insert the rows in MySQLCC, I get the following when
I
> execute the Connect Java class:
> 1 abc a abc
> 2 abc2 a2 abc2
> 3 abc3 a3 abc3
>
> (All the columns appear.) And when I do a "select * from junk2" in the
> Command Prompt DOS window, the result is displayed perfectly.
>
> What's going on?


In order that we test this, please send us CREATE TABLE statement.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:
http://lists.mysql.com/bugs?unsub=franklin.phan@mindspring.c om


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org