Обсуждение: macaddr data type and prepared statements
Hi All,
I'm trying to bulk load some MAC addresses using a prepared statement.
But I keep on getting an error about incorrect datatype (complains that
I'm trying to insert "character varying"). Bellow is an example of the
code that I'm using:
try {
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection(jdbc_url, jdbc_user,
jdbc_pass);
PreparedStatement stmt = conn.prepareStatement("insert into
log (date, time, mac, network) values (?,?,?,?)");
while (inputLineIterator.hasNext()) {
String[] line = inputLineIterator.next();
if (line == null) {
continue;
}
stmt.setDate(1, new java.sql.Date(
dfmt.parse(line[0]).getTime()) );
stmt.setDate(2, new java.sql.Date(
tfmt.parse(line[1]).getTime()) );
stmt.setString(3, line[2]);
stmt.setString(4, line[3]);
stmt.execute();
}
stmt.close();
}
Any recommendations?
Cheers
Steve
Steve,
What are the column types in the table. If it is macaddr type then
this is expected behaviour.
Dave
On 4-Aug-08, at 9:28 AM, Steve Foster wrote:
> Hi All,
>
> I'm trying to bulk load some MAC addresses using a prepared
> statement. But I keep on getting an error about incorrect datatype
> (complains that I'm trying to insert "character varying"). Bellow
> is an example of the code that I'm using:
>
> try {
>
> Class.forName("org.postgresql.Driver");
> conn = DriverManager.getConnection(jdbc_url, jdbc_user,
> jdbc_pass);
>
> PreparedStatement stmt = conn.prepareStatement("insert
> into log (date, time, mac, network) values (?,?,?,?)");
>
> while (inputLineIterator.hasNext()) {
> String[] line = inputLineIterator.next();
> if (line == null) {
> continue;
> }
> stmt.setDate(1, new
> java.sql.Date( dfmt.parse(line[0]).getTime()) );
> stmt.setDate(2, new
> java.sql.Date( tfmt.parse(line[1]).getTime()) );
> stmt.setString(3, line[2]);
> stmt.setString(4, line[3]);
> stmt.execute();
> }
>
> stmt.close();
> }
>
> Any recommendations?
>
> Cheers
>
> Steve
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
Dave,
Yes its of type macaddr, if I build the statement manually (with all the
quoting uglyness) then its fine, e.g.
create table test (a serial, b macaddr);
insert into test (b) values ('00:00:00:00:00:00');
insert into test (b) values ('00:00:00:00:00:01');
insert into test (b) values ('00:00:00:00:00:02');
Cheers
Steve
Dave Cramer wrote:
> Steve,
>
>
> What are the column types in the table. If it is macaddr type then
> this is expected behaviour.
>
> Dave
> On 4-Aug-08, at 9:28 AM, Steve Foster wrote:
>
>> Hi All,
>>
>> I'm trying to bulk load some MAC addresses using a prepared
>> statement. But I keep on getting an error about incorrect datatype
>> (complains that I'm trying to insert "character varying"). Bellow is
>> an example of the code that I'm using:
>>
>> try {
>>
>> Class.forName("org.postgresql.Driver");
>> conn = DriverManager.getConnection(jdbc_url, jdbc_user,
>> jdbc_pass);
>>
>> PreparedStatement stmt = conn.prepareStatement("insert into
>> log (date, time, mac, network) values (?,?,?,?)");
>>
>> while (inputLineIterator.hasNext()) {
>> String[] line = inputLineIterator.next();
>> if (line == null) {
>> continue;
>> }
>> stmt.setDate(1, new java.sql.Date(
>> dfmt.parse(line[0]).getTime()) );
>> stmt.setDate(2, new java.sql.Date(
>> tfmt.parse(line[1]).getTime()) );
>> stmt.setString(3, line[2]);
>> stmt.setString(4, line[3]);
>> stmt.execute();
>> }
>>
>> stmt.close();
>> }
>>
>> Any recommendations?
>>
>> Cheers
>>
>> Steve
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
--
Steve Foster Email: s.p.foster@leeds.ac.uk
Unix team Phone: 0113 343 7225
Information Systems Services Fax: 0113 343 5411
University of Leeds
Leeds LS2 9JT
Steve,
Prepared statements bind the in params to the type.
which is why it won't work.
if you used a statement and did what you did below it would work.
You can extend PGobject to create a PGMacaddr object to get it to work
with prepared statements
Dave
On 4-Aug-08, at 10:46 AM, Steve Foster wrote:
> Dave,
>
> Yes its of type macaddr, if I build the statement manually (with all
> the quoting uglyness) then its fine, e.g.
>
> create table test (a serial, b macaddr);
>
> insert into test (b) values ('00:00:00:00:00:00');
> insert into test (b) values ('00:00:00:00:00:01');
> insert into test (b) values ('00:00:00:00:00:02');
>
> Cheers
>
> Steve
>
> Dave Cramer wrote:
>> Steve,
>>
>>
>> What are the column types in the table. If it is macaddr type then
>> this is expected behaviour.
>>
>> Dave
>> On 4-Aug-08, at 9:28 AM, Steve Foster wrote:
>>
>>> Hi All,
>>>
>>> I'm trying to bulk load some MAC addresses using a prepared
>>> statement. But I keep on getting an error about incorrect datatype
>>> (complains that I'm trying to insert "character varying"). Bellow
>>> is an example of the code that I'm using:
>>>
>>> try {
>>>
>>> Class.forName("org.postgresql.Driver");
>>> conn = DriverManager.getConnection(jdbc_url, jdbc_user,
>>> jdbc_pass);
>>>
>>> PreparedStatement stmt = conn.prepareStatement("insert
>>> into log (date, time, mac, network) values (?,?,?,?)");
>>>
>>> while (inputLineIterator.hasNext()) {
>>> String[] line = inputLineIterator.next();
>>> if (line == null) {
>>> continue;
>>> }
>>> stmt.setDate(1, new
>>> java.sql.Date( dfmt.parse(line[0]).getTime()) );
>>> stmt.setDate(2, new
>>> java.sql.Date( tfmt.parse(line[1]).getTime()) );
>>> stmt.setString(3, line[2]);
>>> stmt.setString(4, line[3]);
>>> stmt.execute();
>>> }
>>>
>>> stmt.close();
>>> }
>>>
>>> Any recommendations?
>>>
>>> Cheers
>>>
>>> Steve
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>
>
> --
> Steve Foster Email: s.p.foster@leeds.ac.uk
> Unix team Phone: 0113 343 7225
> Information Systems Services Fax: 0113 343 5411
> University of Leeds
> Leeds LS2 9JT
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
Dave Cramer wrote: > You can extend PGobject to create a PGMacaddr object to get it to work > with prepared statements Or you can put an explicit cast in your insert statement. -O
On Mon, 4 Aug 2008, Steve Foster wrote: > I'm trying to bulk load some MAC addresses using a prepared statement. But I > keep on getting an error about incorrect datatype (complains that I'm trying > to insert "character varying"). Bellow is an example of the code that I'm > using: > > stmt.setString(3, line[2]); > Don't use setString for non-string types. With a recent JDBC driver you should use setObject(3, line[2], Types.OTHER); Kris Jurka
Hi Folks,
OK Think I've got it sorted... At least I'm inserting using a prepared
statement, the following worked fine. Anyone got any suggestions as to
why I shouldn't do it this way, it feels right but its too simple so I'm
being cautious.
Cheers
Steve
----Code----
org.postgresql.util.PGobject MACADDR = new org.postgresql.util.PGobject();
MACADDR.setType("macaddr");
org.postgresql.util.PGobject CIDR = new org.postgresql.util.PGobject();
CIDR.setType("cidr");
try {
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection(jdbc_url, jdbc_user,
jdbc_pass);
PreparedStatement stmt = conn.prepareStatement("insert into
log (date, time, mac, network) values (?,?,?,?)");
while (inputLineIterator.hasNext()) {
String[] line = inputLineIterator.next();
if (line == null) {
continue;
}
MACADDR.setValue(line[2]);
CIDR.setValue(line[3]);
stmt.setDate(1, new java.sql.Date(
dfmt.parse(line[0]).getTime()) );
stmt.setTime(2, new java.sql.Time(
tfmt.parse(line[1]).getTime()) );
stmt.setObject(3, MACADDR);
stmt.setString(4, CIDR);
stmt.execute();
}
stmt.close();
}
----Code----
Oliver Jowett wrote:
> Dave Cramer wrote:
>
>> You can extend PGobject to create a PGMacaddr object to get it to
>> work with prepared statements
>
> Or you can put an explicit cast in your insert statement.
>
> -O
>
might work for you:
prepstatement.setObject( index, macaddressString, Types.OTHER);
I use that for inet data type, never tried macaddr.
regards
gustav trede
Steve Foster skrev:
> Hi All,
>
> I'm trying to bulk load some MAC addresses using a prepared statement.
> But I keep on getting an error about incorrect datatype (complains
> that I'm trying to insert "character varying"). Bellow is an example
> of the code that I'm using:
>
> try {
>
> Class.forName("org.postgresql.Driver");
> conn = DriverManager.getConnection(jdbc_url, jdbc_user,
> jdbc_pass);
>
> PreparedStatement stmt = conn.prepareStatement("insert into
> log (date, time, mac, network) values (?,?,?,?)");
>
> while (inputLineIterator.hasNext()) {
> String[] line = inputLineIterator.next();
> if (line == null) {
> continue;
> }
> stmt.setDate(1, new java.sql.Date(
> dfmt.parse(line[0]).getTime()) );
> stmt.setDate(2, new java.sql.Date(
> tfmt.parse(line[1]).getTime()) );
> stmt.setString(3, line[2]);
> stmt.setString(4, line[3]);
> stmt.execute();
> }
>
> stmt.close();
> }
>
> Any recommendations?
>
> Cheers
>
> Steve
>