SQL SELECT stmt 1

ceaton · Nov 9, 1999

I have a very large user interface, several panels, components, buttons, etc. I have approximately 146 fields that the user can possible fill in, they can fill them all in or only part of the fields. What I would like to do is do and INSERT of the data into my MSAccess database then perform the necessary queries and calculations on the data. This is a two part question, do you think an INSERT of 146 items is too large? Should I split this into several INSERT statements, but what happens if the user goes back and changes something after it has already been entered? I know to use the UPDATE, just never have. My last question is the most important and I can't seem to find the answer to this anywhere, in a SELECT statement I wan't to do something like this: SELECT * FROM regions WHERE state = 'Florida' AND region = 'Palm Beach'"; Now this work however I don't want 'Florida' and 'Palm Beach' in there I wan't the the field from the user interface, ie chState and chRegion, how do I accomplish this, I've tried with double quotes and get anything to work. Any help would be greatly appreciated. Just a note, this a stand-alone application with no use of a server or web-browser, strictly run in the appletviewer. 
 
Thanks in advance! Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

Otto · Nov 10, 1999

Hi! 
 
1. No. (but M$Access is a M$ product...) 
2. You have two ways: 
 
Make the query string manually: 
String qs="select * from REGIONS where state='"+stateTextField.getText()+ 
"' and region='"+regionTextField.getText()+"'"; 
Statement stmt=conn.createStatement(); 
ResultSet rs=stmt.executeQuery(qs); 
 
Or use prepared statement and parameter passing: 
String qs="select * from REGIONS where state=? and region=?"; 
PreparedStatement pstmt=conn.prepareStatement(qs); 
pstmt.setString(1, stateTextField.getText()); // sets the first parameter 
pstmt.setString(2, regionTextField.getText()); // sets the second 
... // sets all parameters (setInt, setLong, etc. see the jdbc doc) 
ResultSet rs.pstmt.executeQuery(); 
 
Good luck. Bye, Otto.

ceaton · Nov 11, 1999

That worked perfectly!! Thanks, I have one more question for you though. On an INSERT I'm doing the following and getting this error: SQLException: General Error 
 
Here is the SQL: 
 
String query = "INSERT INTO caseInfo(caseName, evalDate, " 
 
+ "docketNum, state, county, geoType, caseSum," 
 
+ "plainName, plainAge, plainSex, " 
 
+ "plainRace, plainMarital, plainOcc, plainAlcohol, " 
 
+ "plainObesity,plainCriminal, plainDeathUn, " 
 
+ "plainHandi ) " 
 
+ "VALUES('"+labelcaseName.getText()+"', " 
 
+ "'"+labelevalDate.getText()+"', " 
 
+ "'"+labelfileNum.getText()"', " 
 
+ "'"+labelstate.getText()+"', " 
 
+ " '"+labelcounty.getText()+"'," 
 
+ "'"+labeltypeofCounty.getText()+"', " 
 
+ "'"+labelcaseS.getText()+"'," 
 
+ "'"+labelpname.getText()+"', '"+labelpage.getText()+"'," 
 
+ "'"+labelpsex.getText()+"', '"+labelprace.getText()+"'," 
 
+ "'"+labelpmarry.getText()+"'," 
 
+ "'"+labelpocc.getText()+"', '"+labelpada.getText()+"'," 
 
+ "'"+labelobesity.getText()+"', '"+labelpcr.getText()+"'," 
 
+ "'"+labelpdeathUn.getText()+"', " 
 
+ "'"+labelhandi.getText()"')"; 
 
 
This is just a snippet, there are 96 total fiels that could be inserted. Is it possible that I'm getting this error because some of these are blank? If so, how do skip over blank fields? 
 
Thanks, 
Courtney 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

Otto · Nov 12, 1999

Hi! 
 
I do not know the structure (field types) of your table, and 
unfortunately I do not know what does it mean "Genereal error" in M$Access term, thus I can make guesses only: 
1. The whole query string is wrong ('' pairs, etc.). 
2. You use quotes all field types, but you for integers you must not do it. 
3. Try to assign empty string(s) to field(s), but them were created "not null". 
4. The getText() give back an empty string ("", not null) when the given field is empty. If you have only string fields (I think not), it will be good. 
But for integer, date, float, etc. fields it make conversion error. 
... 
X. M$ bug or feature

). 
 
I hope these things will help you, and you can find the mistake. If no, write here the structure and query string. 
 
Good luck. Bye, Otto.

ceaton · Nov 12, 1999

I guess I don't really fully understand because all of the fields that I'm getting my information from are all labelFields. Aren't these String fields? In my database (MSAccess) all of the fields are TextFields. So why would it matter if the data is numerical? I also really didn't fully understand the "Null" issue. Please elaborate. The stucture in my database is 96 fields, all of them text fields. I am retrieving the data from labelFields and a partial example of the query string is in my other post. 
 
Thanks, 
 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

Otto · Nov 12, 1999

Hi! 
 
I am very sorry, but my english is poor :-(((. 
I saw in your post evalDate, docketNum, etc. fields, so I thought the evalDate was declared as DATE, docketNum was declared as INTEGER in the M$Access database (table), so I thought when the user give empty string then the query string will be "insert into .. (evalDate) values ('')" and it will cause sql exception (the "... (docketNum) values ('1')" is wrong too). 
 
In your last post wrote that all fields are declared as STRING, so that I wrote above will not matter. 
 
If you create the database so that some fields will not not blank (like this "DECLARE TABLE PERSONS(ID INTEGER, NAME CHAR(30) NOT NULL)&quot

and the user leave blank these fields it will cause sql exception. 
 
But what does it mean "General error" in M$Access, I do not know (I installed the access98 now, but I could not find this error :-((). 
 
I am really sorry. Anyway you may try other SQL server(s) (if possible). 
 
Good luck. Bye, Otto.

ceaton · Nov 12, 1999

Basically what I need to know how to do is pass a null value to the database. I cannot use an SQL server because this is a stand-alone applications which will be installed by the user from a CD-ROM. So for example if I have the SQL 
 
String query = "INSERT INTO caseInfo(caseName, evalDate, " 
 
+ "docketNum )" 
 
+ "VALUES('"+labelcaseName.getText()+"', " 
 
+ "'"+labelevalDate.getText()+"', " 
 
+ "'"+labelfileNum.getText()"')"; 
 
 
and nothing is entered in for the Docket/File Number field, how do I send the other info to the database without the SQL failing entirely? 
 
Thanks, Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

Otto · Nov 13, 1999

The "INSERT INTO caseInfo(caseName, evalDate) VALUES(null, null)"; inserts a record into the table and sets these two fields null (blank). You should do this for all fields. You can make a converter method like this: public String getAsSQLString(TextField tf) { String s; s=tf.getText(); if ((s==null) ¦ (s.length()==0)) { s="null"; } else { s="'"+s+"'"; } } After all the sql string will be like this: String query = "INSERT INTO caseInfo(caseName, evalDate, " +"docketNum )" +"VALUES("+getAsSQLString(labelcaseName) +","+getAsSQLString(labelevalDate) +","+getAsSQLString(labelfileNum) +&quot

"; Good luck. Bye, Otto.

Otto · Nov 14, 1999

Of course, I wanted to write ¦¦, not ¦. 
if ((s==null) ¦¦ (s.length()==0)) { 
 
Sorry. By, Otto.

ceaton · Nov 16, 1999

Hey Otto, That all works great, yes of course there is but to this. When I need to pass a name such as "O'brian" or "Plaintiff's" it is giving me an error. How do I get around this? 
 
Thanks, 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

Otto · Nov 16, 1999

"Nothing is perfect", said the hedgehog and climbed off the scrubbing-brush... 
 
Possible solutions: 
1. Use parameter passing (I have adviced and described it in my first post). 
2. Put more cleverness to the converter method, like this: 
 
public String getAsSQLString(TextField tf) { 
String s; 
StringBuffer sb; 
int i, l; 
 
s=tf.getText(); 
if ((s==null) ¦¦ (s.length()==0)) { s="null"; } 
else { 
sb=new StringBuffer(s); 
l=s.length(); 
for (i=l-1; i>=0; i--) { 
if (sb.charAt(i)==''') { sb.insert(i, '''); } 
} 
s="'"+sb.toString()+"'"; 
} 
return s; 
} 
Note: If you want to put ' to the query string put '' (' twice). 
 
Good luck. Bye, Otto.

ceaton · Nov 17, 1999

Thanks Otto for the help, but the code won't compile. 
 
I'm getting errors on this part of the code: 
 
if (sb.charAt(i)== ' ' ' ) { sb.insert(i, ' ' ' ); 
 
Invalid character constant. and it points to the first single quote on each one. These are single quotes right? 
 
Any ideas, I tried couple of things, but my inexperience isn't helping. Sorry to keep bothering you about this, I do really appreciate all of your assistance. 
 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

ceaton · Nov 17, 1999

I forgot to mention that I will also need to pass an 's similar to "Plaintiff's Drug Addiction". Will this function work with this also? 
 
Thanks Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

Otto · Nov 17, 1999

Hi! 
 
It seems that I found an incompatibility (bug) in jdk1.1.8. 
I work with 1.1.8, and tested the code on it, and worked well. 
But after your post I tried to compile with 1.2.1, and I got the 
errors like you. Sorry. 
The corrected line is: 
if (sb.charAt(i)=='\'') { sb.insert(i, '\''); } 
 
I think (and hope

) finally

it will work for every variations. 
 
Good luck. Bye, Otto.

ceaton · Nov 17, 1999

I am using jdk1.2.2 and I tried that one :-( earlier and got errors for everyone of my Inserts. Unbelievable isn't! 
 
I tried a bunch of various ways with the single quote and the double quote with the escape and I can't get it to work. 
 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

ceaton · Dec 1, 1999

Otto, 
 
I was mistaken when I told you that I tried that, I hadn't. 
 
This is what I did try: 
 
public String getAsSQLString(Label tf) { 
String s; 
 
s=tf.getText(); 
if (s.charAt(i)==' \ ' ') { s.insert(i, ' \ ' '); } 
else { s=" ' "+s+" ' "; } 
return s; 
} 
 
The error that I'm getting is for the i and it reads: 
Undefined variable: i 
 
I know I must be missing something, I'm just not sure what. 
 
Any ideas? 
 
Thanks again, 
Courtney 
 Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

Otto · Dec 1, 1999

Hi! 
 
Here is the full code again

) : 
 
public String getAsSQLString(TextField tf) { 
String s; 
StringBuffer sb; 
int i, l; 
 
s=tf.getText(); 
if ((s==null) ¦¦ (s.length()==0)) { s="null"; } 
else { 
sb=new StringBuffer(s); 
l=s.length(); 
for (i=l-1; i>=0; i--) { 
if (sb.charAt(i)=='\'') { sb.insert(i, '\''); } 
} 
s="'"+sb.toString()+"'"; 
} 
return s; 
} 
I have tested it with 1.1.8 and 1.2.1, and it's worked well. 
 
 
Good luck. Bye, Otto.

ceaton · Dec 2, 1999

Thanks a million Otto! 
 
It works like a champ!! 
 
Courtney Courtney <a href=mailto: ceaton@lrp.com> ceaton@lrp.com</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL SELECT stmt 1

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

Programmer

MIS

Programmer

MIS

MIS

Programmer

MIS

MIS

Programmer

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor