 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / October 2008Dbase and no 'Like' support in SQL
My program connects to an old dbase 4 database. The connectionstring is: jdbc:odbc:Driver={Microsoft dBASE Driver (*.dbf)};mode=read;DriverID=277;DBQ="<dbfdir>" I can read the contents of the tables with i.e. select * from nawaanv where functie='k' (80 records) But when I use: select * from nawaanv where functie like '%k%', I get zero records. I've changed % to *, single quotes to double quotes, nothing helps, always zero records (and sometimes error 15877). In a Delphi program, using the ADO component and odbc for DBASE, I get good results with ....like '%k%' (120 records). What am I doing wrong? Do I have to switch to ADO in Java (not much info).... I'am quit new to Java. Greetings, Kees > My program connects to an old dbase 4 database. The connectionstring > is: [quoted text clipped - 14 lines] > What am I doing wrong? Do I have to switch to ADO in Java (not much > info).... I'am quit new to Java. Sounds like a driver problem. There's many JDBC-dbase drivers out there, but it seems most are commercial. Can you set the dBase version in this driver? That might help tell it the features to support. ...snip >Sounds like a driver problem. There's many JDBC-dbase drivers out there, >but it seems most are commercial. indeed >Can you set the dBase version in this driver? That might help tell it >the features to support. I've set the version, but no difference. Greetings, Kees Sabine, ... snip >Sounds like a driver problem. There's many JDBC-dbase drivers out there, >but it seems most are commercial. > >Can you set the dBase version in this driver? That might help tell it >the features to support. Thanks for your answer. I've tried a few commercial products. Unfortunatly they fail at even simple join operations. Any idea for a good commerial product? An alternative is (maybe) a product to use ADO in Java? Any ideas? Greetings Kees > Sabine, > [quoted text clipped - 9 lines] > I've tried a few commercial products. Unfortunatly they fail at even > simple join operations. Ouch. Sorry. I never had to use dBase from Java - before we got there, we had changed our RDBMS to FirebirdSQL. > Any idea for a good commerial product? An alternative is (maybe) a > product to use ADO in Java? Any ideas? Do a search "java ado", there are ways. Most commercial products have a trial, so all you need to invest is some time. Maybe it's time to migrate your database to a more modern/maintained one. ... snip >Do a search "java ado", there are ways. Most commercial products have a >trial, so all you need to invest is some time. All commercial products I've found are using ADO under dot net. Initially I've ruled dot net out, but maybe it's a possibility. >Maybe it's time to migrate your database to a more modern/maintained >one. Unfortunatly I can't change the database. It is used by a big DBASE program (ugh...) running in a DOS window under Windows. Greetings, Kees > Unfortunatly I can't change the database. It is used by a big DBASE > program (ugh...) running in a DOS window under Windows. Wow. That was notably out of date the last time I saw something like that, which was a FoxPro for DOS program running under Windows in 1999. It is costlier to do what you're trying to do than to migrate the program, I predict. If, as you say, there is a "big DBASE [sic] program" running, a JDBC driver won't be enough. You'll need an interpreter for the dBASE code. If you're migrating the code anyway, migrating the data will not cost much. Move it out of dBASE into a real database, say, PostgreSQL, for which there is a robust JDBC driver. Many DBMSes will directly import .dbf files into tables.  SignatureLew > .... snip > >Do a search "java ado", there are ways. Most commercial products have a [quoted text clipped - 7 lines] > Unfortunatly I can't change the database. It is used by a big DBASE > program (ugh...) running in a DOS window under Windows. Depending, the dBase program can handle different databases - we used dBase programs with FirebirdSQL for a while. The program ran permanently like a daemon/service and did have a tendency to hang itself though, so that was phased out aswell. You would have to have access to the code of that though. > > .... snip > > >Do a search "java ado", there are ways. Most commercial products have a [quoted text clipped - 13 lines] > like a daemon/service and did have a tendency to hang itself though, so > that was phased out aswell. [...] I had a positive experience developing with Firebird 1.5 for about a year on servers running Mac OS X 10.4, RedHat 9 and Debian 3.1. The Jaybird driver was solid. I see Firebird 2.1 is current. SignatureJohn B. Matthews trashgod at gmail dot com home dot woh dot rr dot com slash jbmatthews >> > Depending, the dBase program can handle different databases - we used > > dBase programs with FirebirdSQL for a while. The program ran permanently [quoted text clipped - 5 lines] > year on servers running Mac OS X 10.4, RedHat 9 and Debian 3.1. The > Jaybird driver was solid. I see Firebird 2.1 is current. Yes, that's what we use currently. Just to clarify, dBase was phased out as a programming language, not FirebirdSQL ;) We chose FirebirdSQL, because back then PostgreSQL on Windows did not support UTF-8, and we need/ed that. > > > Depending, the dBase program can handle different databases - we > > > used dBase programs with FirebirdSQL for a while. The program ran [quoted text clipped - 8 lines] > Yes, that's what we use currently. Just to clarify, dBase was phased > out as a programming language, not FirebirdSQL ;) Ah, thank you; I had misunderstood. So many good choices, so little time. > We chose FirebirdSQL, because back then PostgreSQL on Windows did not > support UTF-8, and we need/ed that. SignatureJohn B. Matthews trashgod at gmail dot com home dot woh dot rr dot com slash jbmatthews > ... snip > >Sounds like a driver problem. There's many JDBC-dbase drivers out there, [quoted text clipped - 10 lines] > Any idea for a good [commercial] product? An alternative is (maybe) a > product to use ADO in Java? Any ideas? It's a long shot, but you could try using the Perl DBD::XBase driver: http://search.cpan.org/~janpaz/DBD-XBase-0.241/lib/DBD/XBase.pm http://search.cpan.org/~patl/Inline-Java-0.52/Java/PerlInterpreter/PerlInterpret er.pod Another possibility would be to read the entire query-results into memory and just search for matching data in the Java "while (rs.next()) {" loop. (Most databases can't actually optimize "LIKE '%foo%' " to anything better than a full-table scan anyway, and I'm sure DBF can't.) See: http://java.sun.com/j2se/1.5.0/docs/api/java/lang/String.html#indexOf(java.lang. String) http://java.sun.com/j2se/1.5.0/docs/api/java/lang/String.html#matches(java.lang. String) Finally, you could have a separate process dump a copy of all the DBF tables to a better database: Postgres, MySQL, Oracle XE, SQL Server Express, whatever. -- DLL >> My program connects to an old dbase 4 database. The connectionstring >> is: [quoted text clipped - 20 lines] > Can you set the dBase version in this driver? That might help tell it > the features to support. Perhaps a long shot and far from a Java solution: I have had acceptable results using Access as the middle-thingy with FoxPro tables. Linked them as tables into Access and then queried the Access database via JdbcOdbc bridge. I an glad this is something I faintly remember from about eight years ago... Silvio > My program connects to an old dbase 4 database. The connectionstring > is: [quoted text clipped - 17 lines] > Greetings, > Kees How are you handling the SQL in your Java program? If you are using PreparedStatement, where do you have the percent signs? PreparedStatement test1 = dbConn.prepareStatement ("select * from nawaanv where functie like '%?%"); PreparedStatement test2 = dbConn.prepareStatement ("select * from nawaanv where functie like ?"); String target = "k"; test1.setString (1, target); test2.setString (2,"%"+target+"%"); > My program connects to an old dbase 4 database. The connectionstring > is: [quoted text clipped - 17 lines] > Greetings, > Kees How are you handling the SQL in your Java program? If you are using PreparedStatement, where do you have the percent signs? PreparedStatement test1 = dbConn.prepareStatement ("select * from nawaanv where functie like %?%"); PreparedStatement test2 = dbConn.prepareStatement ("select * from nawaanv where functie like ?"); String target = "k"; test1.setString (1, target); test2.setString (2,"%"+target+"%"); Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.