1. Query works in DB, but not with JDBC (MS version) coderanch.comJoe, That is a valid SQL statement. Are you by any chance doing rs.get("EntryNum") ? If so, that won't work without creating an alias for the column. Alternatives are to use "as name" or retrieve the result by column number. If this isn't the problem, can you show your JDBC code? Also, check which line throws the exception. Is it when ... |
2. graceful way to avoid hardcoding as400 library name in query. coderanch.comTed, Welcome to JavaRanch! You need to have the schema/library name in the query. A datasource wouldn't know the schema name. (Too bad, that would be a nice feature!) I prefer the second query with the schema in a property file. It isn't that ugly. And you can make it a little nicer by introducing a getUsrsTable() method. This works well ... |
3. Can anybody help me in tuning Query in JDBC? coderanch.comHi all thanks to everybody ...reg using "in" clause instead of "OR"clause we have some limitation i suppose in number of questionmarks used.(remember we have to use prepared statements only)..here the requirement in client;s environment is infinite...we cant limit the size of the in clause...what could be done for this... Reg the existing OR clause :it is using same column only ... |
4. Reports ans Queries coderanch.comHello, I have few reports created using simple jsp and using sql queries, I am using Oracle 8i as database. I want to create advanced reports, is there any way I could use indexing kind of search facility other than ultra search, without using sql queries is it possible to get results regardless user search for anything on that database? Any ... |
5. Creating a Query on the fly coderanch.comHi All, I dont know whether this is possible or not, but if anyone has an idea can he please let me know how it can be done... I am creating a data Model.(Replication of the database.) Now the user selects number of columns from two or more tables. Once the user clicks submit I have to create a query in ... |
6. query returning null coderanch.comHi When I ran the following query in sqlplus of oracle 9i it returns all column values properly. (matching table affiliation status properly). But when I ran the query in java, it returns 'null' values for status column. Can some one tell me, what is wrong with the query ? Thanks in advance. Krishna Select * from ( SELECT rownum as ... |
7. need of query tuning coderanch.com |
8. equivalent query coderanch.com |
9. query to find database.. coderanch.comHi paul my problem is I have two statements which do the same thing. oracle version: select u.useraccountid,u.personid,u.useraccountnm,u.passwordtxt from PRSNACCSASSIGN p,USERACCOUNT u,PERSONSTATUSMM ps where p.MGRACCESSLASID <> -2 and u.useraccountid > 0 and ps.EmploymentStatID = 1 And ps.UserAcctStatID = 1 And ps.ExpirationDTM >= to_date('03/03/2005', 'mm/dd/yyyy') and ps.EffectiveDTM <= to_date('03/03/2005', 'mm/dd/yyyy') and u.personid = p.personid and ps.personid = p.personid SQL server version: select ... |
10. Query Optimization coderanch.com |
11. best way of querying? coderanch.comHi all, well, it is regarding best ways of creating SQL queries.Plz don't suggest ORM tools like Hibernate for this small project.. You know, i have to do a big query, kind of involves 12 tables,.. when a user submits a form. So i am just doing it in a single query, involving some Outer Joins and all; it takes quite ... |
12. how to write this query... count, unique, ?? coderanch.comSo I've got a DB that's got all these codes in it. I can write --- select unique CODE from TABLE; and I get a list of all the different kinds of codes in the table I can write --- select count( CODE ) from TABLE; and I get the number of codes in the whole table. I can write --- ... |
13. Present a query to a database. coderanch.comHello I am making a website to search other database driven websites, the problem is I dont have access to their databases. Is it possible to present a query to the websites I want to search so I can search the information that is extracted from their database? Any help would be very much appreciated |
14. assistance with a subselect query coderanch.com |
15. join vs sub query coderanch.comHi all, I have two large tables. I have particular problem for which i can choose either join or sub query, which one is better? Shall i need to consider the dbms optimization when selecting my strategy? What are the pro and cons in selecting one. Thanks in advance --Ramki (SCJP--93%)(preparing for SCWCD) |
16. Building a query for Tree coderanch.comHi In my application i have a folder tree where its stored in the Db in one table which maintains the parent id , folderid and the name of the folder For example i have a folder structure like a --b --c --d and the above will be stored in DB as foldername folderid parentid a 1 0(This is dummy id ... |
17. Can anyone help with query ? coderanch.comHi All, I'm having a bit of trouble writing a query and wondered if someone might take a quick look. I have 2 tables (using MYSQL by the way) , Dives and Locations, there is a location_id in the dives tables linking to location_id in the locations table. I'm trying to build a resultset that I can show in a JTable ... |
18. JDBC isolation level vs query coderanch.comI'm using an EJB container (websphere) in which I use a JDBC datasource. The settings on the datasource let me define a JDBC transaction isolation level (serializable,repeatable read etc). I do understand the differences of these settings, but I do not understand the following: Regardless of the isolation setting on the JDBC connection I can perform queries which include this information ... |
19. DB QUERY optimization coderanch.comSteve, As you noticed, combining the queries makes one really slow query. Sometimes this works out better an sometimes it doesn't. The first place to start is to make sure you have the proper indexes. Having one on sched_time is critical. You can also use your database's explain feature to see what the execution plan is for optimization ideas. |
20. Query about DTO's ? coderanch.comHi folks, My Query regarding to Data Transfer Objects. 1)At what situvation can we use DTO's? 2)How to write a DTO application to communicate with one table in the DataBase? I request you plz send me some sample code. Thanks & Regards, G Sirish Reddy., {Bear edit: removed the 'urgent'] [ August 10, 2005: Message edited by: Bear Bibeault ] |
21. query hangs in app server, not dbvisualizer coderanch.comi have a query that takes an extremely long time to execute in my app server yet i do not have this problem when i execute it inside dbvisualizer (a jdbc db client). what could explain the difference? i notice this behavior only when i return a large number of rows, ~32k. if i supply parameters that would return a small ... |
22. Help me to know how to write named query coderanch.com |
23. Problems using a query coderanch.comYuck! Ok, the best way to handle that is to not allow it in the first place. If, however, the application requirements stated the need for that. I would write a class/method that would accept a string as a argument and parse through the string and replace the ' with a ~a and return the modified string back to be processed ... |
24. Help with a query? coderanch.comCan someone help me with a query? I have 2 tables. table1 ------- id name item table2 ------- id item table1 is a link table where the id's can be the same but the names will be different for each id. table1.item = table2.item. What I need to do is count the number of times table1.item exists of a specific item ... |
25. query coderanch.com |
26. database query taking too long coderanch.comHello, We are using SQl Server2000 and have to run this below query for the results. The query itself is taking more that 10 seconds. I tried the query on the query analyzer itself and took the same amount of time. the query is: SELECT a.itemno, a.iedesc,a.icode2,b.pcprc1,b.pcprc8, b.pcprc9, a.igroup, b.ptaxfb, b.ptp01, d.serial_required_ind, a.icode1, a.istat, a.ilcst, (sqtyo1 + sqtyo2 + sqtyo3 + ... |
27. Checking existence query? coderanch.comAll I want to know is if Name is not null. I have a unique constraint on my name so I need to ensure none exists which is the same as the one I plan to use. I am using String qstring = "SELECT Name FROM masterFuncCnctrs WHERE Name = '" + name + "' AND ComponentID = " + component.getID() ... |
28. Problem in Executing Join Query in Access coderanch.comHi, I have written one query using right Join. That works fine on MYSQL but does not work on Access. getting error as Join expression not supported. can Anydbody explains it to me what does it mean? and how to write right join in access? (Right join syntanx is - select * from Table1 right join table2 on condition ) is ... |
29. Urgent - how to wirte dynamic query ?` coderanch.com |
30. JDBC Query Help!!!!! coderanch.comKoh, Welcome to JavaRanch! SQL has a clause called order by, which allows you to specify what to sort the result by. So you could do "Select * from LockerInfo order by lockerid" to sort ascendingly or "Select * from LockerInfo order by lockerid desc" to sort descendingly. Note that we have a forum at the top of the page for ... |
31. Fetching data using jdbc....query doubt coderanch.comcan someone help me out with the following query. Insert into employee select 'rajesh',age,sex from xyz. The thing is I am trying to insert into employee table the values from zyz table. in future incase a new column is added to xyz table, then I have to manually add to this query. I need a solution where I need to hardcode ... |
32. JDBC Query coderanch.com |
33. can i still optimize this query, Any real why running slow coderanch.comDear Friends, I have written a query which takes around 2 minutes to execute. Is it possible to cut the time in execution. Any trick select b.person_id, b.empl_rcd_num, a.name, d.email_address, c.phone from (select * from sp_names a1 where a1.name_type= :2: and a1.effdt = (select max(effdt) from sp_names where person_id = a1.person_id and name_type = a1.name_type and effdt <= trunc(sysdate) ) ) ... |
34. Problem in query coderanch.comHai friends, I am not geting a proper query for executing the following. can anyone help me. I have two tables book and usershelf the columns in the tables are following: book : bid usershelf:uid,bid i have to retrieve bid from book such that this bid is not appeared in usershelf for a particular user(uid). select b.bid from book b where ... |
35. Relative query efficiencies coderanch.comDue to Access' lack of case sensitivity on the primary key, I need to check if a record exists before I insert a new record. Is it faster to return a count, or to return actual data from a table? I.E. which of the following queries will be faster? 1) SELECT COUNT(*) FROM table WHERE id = 'Test'; or 2) SELECT ... |
36. problem in query coderanch.com |
37. Urgent Query Help coderanch.comhi , i am doing this application in which--- i want to check in database whether a particular record exist between timein and timeout field. e.g suppose rishi takes lecture and his timein is 12:30 and his timeout is 14:50 this record already there in data now when i again create a new record i want to check whether new record ... |
38. time to execute query coderanch.comhai can anyone just help me to find the time taken to execute a uery in oracle. i just tried with calculating the difference between the system time before and after executing the query in milliseconds.but its not working properly. Is there any predefined function in oracle api to caluclate the time taken to execute query. |
39. time to execute query coderanch.com |
40. execute query coderanch.comthanks but i know how to get the column name and column type using databasemetadata. but i am developing editor in java to run all sql queries like select,delete,insert and all. its all working fine for me,but if i give the command like dec tablename , show user,rollback/ it wont work. i think u understand what i am asking. thanks .. ... |
41. Stored Procedure VS normal Query coderanch.comHi All, I have often heared that SP will improve performance when compared with similar query.Both SP and query as executed from Java ( CallableStmt and prepared Stmt respectively) But any idea like howmuch % of performance is improved and what all are the factors added to this improved performance? Thanks Anil |
42. web querying center project coderanch.comHello, I want to upgrade my web system's querying module into coharent integraded module. Does any one knows about a open source/free of charge project/wizards/etc.. for building a big different querying interfaces on many tables with different outputs types mainly as tables with option to reorder the data by columns? I know It is big to ask, but I won't forgive ... |
43. A query coderanch.comThat code does is not doing what I want it to do, the numbers you see below they are A.M. Times. It supposed to begin from 6 to 10. How can I take the ten and put it at the end. CODE DECODE_ONE 1000 10:00 //noticed in database looks like this but I am trying to 600 6:00 //make the top ... |
44. creating a front end for a query which coderanch.comHi All, This is my first ever post. so here we go... Im trying to get "names of customer" from a "customer" table and displayed them in a GUI. where it will display the first record, and when i click the "next" button it will display the next record... what i have done: - i've created a simple testing GUI which ... |
45. How to execute an scalar query ? coderanch.com |
46. can you do that in one query? coderanch.comSay I gave 2 tables A and B As fields: Date_A and Amount_A Bs files: Date_B and Amount_B The end-user asks this question: select amount (A+B) between 2006-01-01 to 2006-12-31 the return result should be as follow: Total_A is 1500 (until 2006-05-01) And Total_B is 1000 (from 2006-06-01) (table A has info until 2006-05-01 this is the max date) once ... |
47. Query help coderanch.comYou have to use 2 seperate queries; if you do it in a stored procedure, then it will be one round trip to the database. However, unless this thing gets execute a lot, I'd just code it as 2 seperate queries, the extra time for the extra round trip to the DB should be about as minimimal as a DB call ... |
48. Is this query even possible? coderanch.comHi, I have several columns in my query. One is a select distinct and another is a count. The third Column is another count(*) BUT the WHERE clauses for all three queries are different. I'm trying to display a unified listing of counts. I tried to use a UNION, but I ended up with some rows having the data filled in ... |
49. query help coderanch.comHi All, Puzzling over a query I have to write. I have the following tables. Users, Orders, Products & order_products. I need a resultset that contains ALL user information & also 1 additional column that indicates if the user has every placed an order of a product that has a productkey with a value < 1000. I decided to start by ... |
50. What will be the query for this logic. coderanch.comThe ability to efficiently do this will depend on what features your database supports. For example, if your database is postgresql, using the group by clause in your query, then to "find all item_numbers for which there are more than one supplier" (assuming that the item_number, supplier combination is unique in this table) might look like: select item_number from it_sup group ... |
51. related links query coderanch.comDear All, I'm working on Search engine, my problem is whenever there is a search, i have to display related links also along with the results. We have to get the keywords from the table based on what the word given by the user. Example: there are 5 keywords, user used 1 keyword and i have to get the other 4 ... |
52. Query coderanch.com |
53. if else in query coderanch.com |
54. PreparedStatement-2Sql queries coderanch.comFirst, you should have a unique-ness constraint in the database so that at the very least, the database never allows 2 users to have the same name. In this way, you could just look for an exception to be thrown although since you expect a problem, its better to try to catch it ahead of time. Second, usually checking if the ... |
55. running a query within a loop. Need help coderanch.comHello, This is my first java ranch posting so please be kind... I have a problem, I'm creating a jsp running on tomcat, Im trying to use a mysql db to create a page that shows what permissions different users for the application have. SO... imaging a table where the headers going across are the names of the permissions and then ... |
56. need help to optimize this query coderanch.comHi, I have this query running in application and I found out that this query was taking maximum time in execution. Can any body help me in optimizing this query. Your suggestions are welcome SELECT (A.FIRST_NAME||' ' ||A.LAST_NAME) NAME, b.EXTERNAL_ID EMPLID , decode(d.descr,NULL,'', d.descr ) jobcodedescr, decode(f.descr2,NULL,'', f.descr2 ) deptdescr, decode(i.descr2,NULL,'', i.descr2 ) city from ( select descr2 ,fieldvalue FROM sp_code_descr_tbl ... |
57. problem in query using java code coderanch.com |
58. problem in query using java code coderanch.com |
59. query on setAutoCommit() coderanch.com |
60. help needed for this query... coderanch.com |
61. Problem with UNION query coderanch.comHi frnds.... i would like to know if java supports a result set containng a query of type UNION i.e. a query containng a UNION of two SELECT statements i am askng this becoz, i want to create a JTable using such a query. The code which i have written to create a TableModel from the ResultSet has been created and ... |
62. Query Needed - please help coderanch.comI am no sql expert. I need query which i can fire using JDBC. The table to be queried is as follows: report varchar2(60), rundate date, ----Time also included in this. userid varchar2(20) This table logs userid against a report that the user has run. It also logs the time they run the report. I want to query this table so ... |
63. how to represent the output from a query in bar or pie graphs coderanch.comMy query will retrive the counts of different groups.I would like to represent the count which will be different for different groups in a graphical format that is either bar graph or pie graph.can you please suggest me any procedure or give me a piece of code as how to do it. thanks in advance |
64. Please solve this query coderanch.comDear ranchar first of all sorry to ask this oracle question on this forum. But if possible the please give me the answer of the following query please sir as soon as i have a assignment ahead Create table client_master with following constraints: a) Client_no must be the primary key. b) Data variable being inserted into the coloumns Client_no must start ... |
65. Minus Operation Query coderanch.com |
66. PreparedStatement/update query coderanch.comTheoritically your statement should work fine.. (upto my knowledge) I mean I have worked on much more complicated statements. If an error doesn't occur my guess is that you didn't properly initialize the fields you're pa.. if anythings wrong with the database or your sql syntax an SQLException should occur.. make sure you catch all of'em. You better also use a ... |
67. Simply Query Problem coderanch.comResultSets are *sets* of objects and do not point to a record but a *set* of records. Think of it like opening a pointer to a shelf in a library. Whether there's one record or hundreds, initially the result set only points to the shelf. In order to view the contents of the shelf you need to iterate over it using ... |
68. database query coderanch.comThree different queries have been written to get the count of same columns based on different conditions.But this results in hitting the database very often and thus performance comes down.So we want to write a query which combines all the three queries into a single one.We use three different methods to invoke these queries individually.This should be avoided and made into ... |
69. query slowdown coderanch.com |
70. Please help with this query .. coderanch.comHi, I have an oracle table called T_CUSTOMER_DEBT as follows T_CUSTOMER_DEBT ---------------------------- CUST_IDVARCHAR2(10) AMOUNT_OWED VARCHAR2(10) OWED_FORVARCHAR2(10) This table contains close to 200,000 records for 3000 unique CUST_IDs. I need to print a report that lists down these 3 coulmns and also checks if a bank account exists for the customer in the system. There is a separate function to check if ... |
71. Any ideas on optimizing this query?? coderanch.comSelect distinct DPM.dep_id,OPM.org_id,A.year from DPM,OPM,M,A where M.pro_id=? and DPM.Affil_id(+)=A.affil_id and OPM.affil_id=A.affil_id and M.seqno=A.seqno and M.pubid=A.pubid order by A.year desc; So this is a query which is being used in my project and apparently it takes a long time for it to execute. Is there so way to optimize the where clause so that the query executes faster?? Also, what is the ... |
72. Order By in Query? coderanch.comHello All, I know its Java forum, but Java ranch has solved my lot of problem so i even for SQL i am posting my question on Ranch. Here i have column of type varchar in a table of SQL 2000..... the problem begins with order by queries....... the column usually have data like A) 1,2,3,4,5,10,1F,2F........ when i put order by ... |
73. DB query with prefix coderanch.com |
74. query trouble coderanch.comkindly see the following code pgres.otherquery("insert into public.author (authorname) values ('"+jComboBox3.getSelectedItem().toString().trim()+"'"); pgres.otherquery("insert into public.subject (subject) values ('" + jComboBox1.getSelectedItem().toString().trim()+"'"); aset = pgres.selectquery("select authid from public.author where authorname =" + jComboBox3.getSelectedItem()); sset = pgres.selectquery("select sid from public.subject where subject =" + jComboBox1.getSelectedIndex()); try { pgres.otherquery("insert into public.author_subject values (" + aset.getInt("authorid")+", "+sset.getInt("sid") +")"); } catch (Exception E) {System.out.println(E.getMessage());} it gives the error ... |
76. Using comments in a query coderanch.comI found a peculiar problem when i tried retrieving values from a table using JDBC. I had 2 parts of a queries separated by UNION. When i used the m$ sql comment "--" in the first part of the query, the code after union did not get executed. Say for example:- sbSample.append("select 'abcd', 'efgh'"); sbSample.append("--order by 1"); union sbSample.append("select 'ijkl', 'mnop'"); ... |
77. using queries coderanch.comi want to compare textfield input with values in the database, this is what i have so far. public static class LoginListener implements ActionListener{ public void actionPerformed(ActionEvent event) { if(event.getSource() == login) { TYPE = users[userType.getSelectedIndex()]; if(TYPE.equals("Student")) { String userlogin = "'"+Sname.getText()+"'"; String userPass = "'"+SID.getText()+"'"; String whereClause = ("WHERE FName = "+userlogin +" AND "+"SID = "+userPass); try{ Statement statement ... |
78. Doubt in FOREACH query in Stored Function? coderanch.comHi, I want to write a Stored Function which will return a list of value. For that i am using the FOR EACH query.. The following query is not running for me.. CREATE FUNCTION get_temp_users() RETURNING INTEGER; DEFINE v_id INTEGER; FOREACH temp_cursor FOR select temp_id into v_id from a_user where status = 'C'; RETURN v_id WITH RESUME; END FOREACH END FUNCTION; ... |
79. Query Optimization coderanch.com |
80. query optimizer coderanch.com |
81. Head First - Hierachical Query coderanch.com |
82. Query Optimization coderanch.com |
83. query disaster coderanch.comYou might be able to get what you want this way: select tyche_id,max(summit_id) from "your table" group by tyche_id. the group by will get you uniqueness of the tyche_ids and the aggregate function will get you some meaningful value for the summit_id, unless it's 0 in which case it will return the 0. It's a bit of a kludge, but it ... |
84. Optimizing the query coderanch.comOptimizing SQL statement can be done by looking at how you are using database objects: do you have well placed index, how you partition your tables, do you have appropriate views, how your database memory managed, fast is your hard disk and log hard disk,... I think you should use an sql profiler or sql query plan analyzer which come with ... |
85. query regarding getFetchSize coderanch.comThanks Jeanne for your replies . So when you say there will be two trips to the database : 1 >Where is the resultset stored - is that stored in SGA ( In case of Oracle ) 2 >If we are using a resultset - then believe all the data is anyway on the server side ( not like disconnected rowset ... |
86. pass array to query coderanch.comLakshmi, You can build the where clause dynamically. If you had two months, you would want it to look like: AND MONTH(DATE) in ( ?, ? ) If you had five months, you would want it to look like: AND MONTH(DATE) in ( ?, ?, ?, ?, ? ) A loop builds this up well at runtime. My example uses the ... |
87. Doubt on '#' in query. coderanch.com |
88. Urgent Help in Query coderanch.comI have the table like following Time/Date Close Average 1 100 NO 2 200 No 3 129.39 No 4 200 No 5 129.39 No 6 100 NO 7 200 No 8 129.39 No 9 100 NO 10 200 No 11 123 avg(close(1 to 10)/10) 12 234 avg(close(2 to 11)/10)has to display 13 231 avg(close(3 to 12)/10)has to display 14 . ... ... |
89. Help in Query coderanch.comI have the table like following Time/Date Close Average 1 100 NO 2 200 No 3 129.39 No 4 200 No 5 129.39 No 6 100 NO 7 200 No 8 129.39 No 9 100 NO 10 200 No 11 123 avg(close(1 to 10)/10) 12 234 avg(close(2 to 11)/10)has to display 13 231 avg(close(3 to 12)/10)has to display 14 . ... ... |
90. java.lang.OutOfMemoryError: Java heap space while Executing the Query coderanch.comHey Senthil, Just to understand your requirement first,you get a resultset from the first query. This resultset would be the parameters for your second query for you to execute the second one.If I am write, and from the problem description you have given, your second query is causing the problems.You can try the below method. Instead of fetching lakhs of records ... |
91. Remote query management application coderanch.comI'm not exactly sure where to post this question, and I thought to start here since it is most related to SQL. Are there any open source projects out there that allows one to host parameterized SQL queries on a J2EE server? A remote user can select from the list of queries hosted on the server, build parameters, validate and send ... |
92. Query Correct, but not functioning coderanch.comHi, I am trying to write to a DB from my code however the data is not writing to the database. I ran the SQL statement through the DB Frontend that I use and it worked, however through my code it does not work. Here is the snippit: public class SubmitListener implements ActionListener { public void actionPerformed(ActionEvent arg) { Connection conn; ... |
93. Measure Query Duration coderanch.comI am conducting performance testing on a Java Application which uses a MySQL db. I am attempting to use MySQL Query Profiler to measure db performance. Both are on the same Linux machine. I initiate the profiler by logging into the MySQL client and "set profiling=1". However, this captures only the queries executed from the command line and not those from ... |
94. Need a Free Query Browser coderanch.com |
95. Query Slow in Java coderanch.comHi Shikhar , The query has a where clause, the confusing part is why only through JDBC the query is slow?. More precisely no other query in my application I see this issue, means different between query response directly from a database vs through Java. Is Statement better than Prepared Statement? Thanks, Vinod |
96. query coderanch.com |
97. Telnet Disconnecting from Client app when executing query. coderanch.com |
98. Which Query is faster coderanch.comMy gut says the opposite - I would think the second is faster if you have an index on primaryColumn. The execution plans would be different for both scenarios. First: 1) Retrieve all rows from disk (sorting by primaryColumn if no index is returned) 2) Return the first one across the network 3) Hope the db is smart enough to retrieve ... |
99. UTF-8 query problem coderanch.comi'm developing a web-based setup wizard for my appliation. it reads a utf-8 encoded file and executes the queries in the file. my reading code... InputStreamReader isr = new InputStreamReader(new FileInputStream(new File("C:/file.txt")),"UTF-8"); BufferedReader br = new BufferedReader(isr); String line = br.readLine(); when i read the file using the above code, i'm getting a '?' symbol as a very first character. due ... |
100. recursive query problem coderanch.comEmp table has 3 columns: emp_id, mgr_id, and level. this table creates hierarchical structure with level column store the level of tree. emp_id and mgr_id has many to one relationship (1 manager could have multiple emp, but every emp has at most 1 manager). now, i would like to write a query that will fetch all the managers and their managers' ... |