I have come across various situations, where I want to store some formatted data in a way such that it can be easily queried. For example $ cat so.txt "question_id": 58640, "tags": ["polls", "fun", "quotes"], "title": "Great programming quotes" "question_id": 184618, "tags": ["polls", "fun", "comment"], "title": "What is the best comme...
I'm creating a multitenant app where some of the tables need to have sequentially assigned integer values. The ordering is done independently for each tenant. As a concrete example, consider a Student table with a RegNumber column. RegNumber has to be assigned sequentially, but the sequence is local to each tenant. The solution I'm thin...
I'm trying to write code to pull a list of product items from a SQL Server database an display the results on a webpage. A requirement of the project is that a list of categories is displayed at the right hand side of the page as a list of checkboxes (all categories selected by default) and a user can uncheck categories and re-query th...
Hi, I am building an application with objects which have their data stored in mysql tables (across multiple tables). When I need to work with the object (retrieve object attributes / change the attributes) I am querying the sql database using mysqldb (select / update). However, since the application is quite computation intensive, the e...
I need to update (replace) fields in a MySQL database, but only if they have changed. The table contains an ID, text field and date changed. Users query the data by ID based on the date it was changed. i.e. If the date is before the last the time user queried the data, he doesn't want it. I would like to change the date in the data...
Hi guys, So I was wondering whether it is best to update multiple rows in a database using an hql query or calling nhibernates save after modifying each object.. I know that the answer is the HQL query but please give me adequate reasons for the same. but there are disadvantages of using an HQL query right like my API has a hard depend...
Hello, I am new to CQRS and confused on how command will write a address change to a customer object Lets say I have divided customer information into two tables customer - Domain database Active Preferred Customer_Read database Name, Address, Phone, email User modifies address of the customer. The address fields are all in re...
Have a look at the following code snippet. response.setContentType("image/gif"); String url="jdbc:oracle:thin:@localhost:1521:xe"; String username="xyz"; String password="abc"; Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn=DriverManager.getConnection(url,username,password); String sql="Select name,description,im...
Hi, What are the equivalent of SYSIBM. in Oracle? ...
Hi, I would like to know if there is any way of counting the number of tuples in a table without actually using the COUNT function? A B C XXXX YYYY IIII XXXX SSSS PPPP RRRR TTTT FFFF KKKK AAAA BBBB If I would like to know how many times XXXX has appeared with...
I have a Joomla website that I've written a custom shopping cart component for. The user is basically purchasing codes we're storing in our database - these are associated with a printed incentive card. When the user checks out, I need to grab a chunk of codes from the database (however many they've purchased), then loop through the lis...
I have the following CTE SQL WITH Tasks AS ( SELECT TaskID, ParentTaskID, CAST(SortKey AS nChar) AS sort_key /*,cast(SortKey as char) as sort_key */ FROM oaTasks AS s WHERE (TaskID = 1) UNION ALL SELECT s2.TaskID, s2.ParentTaskID ,Cast( '0.'+ cast(Tasks_2.sort_key as...
Hello, I am using SQL 2005 and having a simple query as below trap duplicates: SELECT x,y,COUNT(DISTINCT z) AS z_count FROM tblA GROUP BY x,y HAVING (COUNT(DISTINCT z) > 1) Now the issue is this column z is sometimes having NULL values, which get ignored by count distinct. As such, duplicates having z as NULL in one record and as ...
I have the following table: CREATE TABLE "posting" ( "id" integer NOT NULL PRIMARY KEY, "amount" real NOT NULL, "balance" real NOT NULL, "account_id" integer NOT NULL REFERENCES "account" ("id"), "asset_type_id" integer NOT NULL REFERENCES "asset_type" ("id") ) For this table, I manually generate ids in a w...
I have a table called "r", inside that table I store id, ip, and country. I am running this query: SELECT r.country, count(rr.ip), count(DISTINCT rr.ip) FROM `r` LEFT JOIN r rr ON r.country = rr.country GROUP BY r.country I do get rows with columns: "country";"number";"number"; But both "counts()" doesn not show what I want it to...
I am writing some code that will lookup for unique id's in a table with over 60,000 rows for the ones that are in an array using mysql_query("SELECT * FROM users WHERE unique_indexed_user_id IN('".join("', '", $array)."')") ; the amount of this array is not limited to the end user so they might end up selecting a large array. thus I...
something is seriously wrong with my SQLITE I included echo $dbquery to see exactly what query is being run, and the statment seems correct - but when trying to view the db after, it comes up blank! I've checked the actuall file, and theres still data in the db file, so maybe it corrupting it? the query prints as: DELETE FROM tool...
Hi, A little context first: I would say I have good SQL server experience, but am a developer, not a DBA. My current task is to improve on the performance of a database, which has been built with a very heavy reliance on views. The application is peppered with inline sql, and using Tuning Advisor only suggests a couple of missing index...
I am using this query: SELECT p.id, count(clicks.ip) FROM `p` LEFT JOIN c clicks ON p.id = clicks.pid WHERE clicks.ip = '111.222.333.444' To select clicks from table "c", that has "pid" = "p.id". The query seems to work fine, but now I want to use that query with date ranges. The "c" table has a column "time" that uses MySQL CURRENT_T...
Is there any way given a column, which table it belongs using SQL Query? ...