sql

Error regarding cursor in SQL

Hi guys, following is my stored procedure. It contains a subquery Select StartTime From DrTimings Where DrID = @DrID If this subquery returns more than one value, I get an error. Subquery returns multiple rows. I want to get each @StartTime and @EndTime in cursor. Means I want to "fetch next from Doctor into @StTime and @EndTime" C...

What is the equivalent of 'go' in MySQL?

In TSQL I can state: insert into myTable (ID) values (5) GO select * from myTable In MySQL I can't write the same query. What is the correct way to write this query in MySQL? ...

sql question .. how can i dynamically add columns to a sql query result set?

I am writing a report to return details about an object ('files') in my database. My application lets users create their own flags for use against file objects. Flags basically consist of a name, then flag instances store a bit value to indicate whether it is set for the parent file object. I want to write a query that returns one row p...

Reporting Queries: Best Way to Join Multiple Fact Tables?

I'm working on a reporting system that allows the user to arbitrarily query a set of fact tables, constraining on multiple dimension tables for each fact table. I've written a query-builder class that automatically assembles all the correct joins and subqueries based on the constraint parameters, and everything works as designed. But, I...

How can I use JQuery to indicate that the user waits until the db updates

I'm new to JQuery, but as a follow on from my previous question regarding duplicated entries to the database, on a forum type application, the question is: Is JQuery a good solution to show to the user, transaction progress, state of submit etc And what is the best solution, if using JQuery Else what other solution is right. With a vie...

MYSQL optimize & questions

Hi, I am trying to optimize my MySQL queries and I need some help. Here is my current query : SELECT *, (SELECT name FROM stores WHERE id = products.store_id) AS store_name, (SELECT username FROM stores WHERE id = products.store_id) AS store_username, (SELECT region_id FROM stores WHERE id = products.store_id) AS re...

T-SQL Split string into many-to-one relationship?

I have the following SQL script: DECLARE @temp table ( ID int IDENTITY(1, 1), data nvarchar(100) ) INSERT INTO @temp (data) VALUES ('a,b,c') INSERT INTO @temp (data) VALUES ('d,e,f') SELECT * FROM @temp AS T INNER JOIN (SELECT * FROM dbo.__StringSplit(T.data, ',', T.ID)) AS S ON T.ID = S.RefID And on ...

create and edit view table?

How to create a view with the name "changedata" to see the full name of all employees, salary, department name and the name of the region and allows edit of data from the table s_emp view? create view change as SELECT a.last_name||','||a.first_name as "Nombre", a.salary as "Salary", b.name"Department", c.name as "Region...

What's wrong with this MySQL statement: DECLARE @ID INT

DECLARE @ID INT ; This statement parses fine with MS SQL Server, but gives me You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @ID INT' at line 1 Does anyone have any idea about the reason? ...

How to permit update of a view's results in Oracle?

I understand that database views are read-only, or at least read-only by default. Is it possible to enable the change of data brought by an oracle view? Rephrasing: If I make a view of just one table, just to hide some columns, will changes to this data be updated on the table? ...

Learning resources to transerfer my MS SQL Server knowledge into MySQL

I'm an experienced MS SQL Server developer But with my first MySQL project I got problems with very simple issues So can anyone suggest good resources helping me transfer my MS SQL Server knowledge into MySQL I'm not looking for a complete reference guide for MySQL I'm looking for something targeted for experienced SQL developers EDI...

Why does my query produce error "ORA-00933: SQL command not properly ended"?

My query: CREATE VIEW cambiodatos AS SELECT a.last_name||','||a.first_name AS "Nombre", a.salary AS "Salario", b.name AS "Nombre Departamento", c.name AS "Nombre de Region" FROM s_emp a, s_dept b, s_region c WHERE a.dept_id = b.id AND b.region_id = c.id UPDATE cambiodatos SET name = 'North Am...

What are the disadvantages of having many indices?

I recently sped up a complicated query by an order of magnitude by giving SQLite a good index to work with. Results like this make me wonder if I should index a lot of other fields that are commonly used for JOINs or ORDER BY clauses. But I don't want to get overzealous and have it backfire on me: I assume there must be some reasons not ...

Flatten Adjacency List Hierarchy To A List Of All Paths

I have a Table that stores Hierarchical information using the Adjacency List model. (uses a self referential key - example below. This Table may look familiar): category_id name parent ----------- -------------------- ----------- 1 ELECTRONICS NULL 2 TELEVISIONS 1 3 TUBE ...

binary format to pass tabular data

I'm maintaining a legacy embedded device which interacts with the real world. Generally speaking, this device collects data from sensors, processes the data using its internal algorithm, and displays warning when data reaches a certain "bad" state. For debugging purposes, we wish this device will send us on a regular basis many of the d...

What's a good resource for grokking database connection strings?

Often the most frustrating thing in starting a new database project or learning a new database is figuring out the magic incantation known as the "connection string". Where are the good resources for understanding each database's connection string format? ...

SQL trigger question: why is this a syntax error?

I would to create a trigger for Sybase, but it shows an error. What I want to do is, when a delete operation is done on the [student] table, to check whether there is any record related to student on [account], and if so, raise an exception. There seems to be a lack of support by Sybase. Their official seem not people to visit. *CREAT...

Best Relational DataBase Representation Of Time Bound Hierarchies

What in everyone's opinion is the best representation for a time-bound hierarchy in SQL? What I mean by this is: - On any given date you have a normal tree hierarchy - This hierarchy can change from day to date - Each child still only has one parent on any given date Day 1... Business | |-Joe | |-Happy | |-Sneezy | |-Doc(*) ...

Calculating a T-SQL Query Parameter at Load Time

I'm trying to SELECT records out of an Events Database (with an EventDate field as Date) that have a EventDate that is in the current month. I have functions for calculating the first day of the month: public string GetFirstofMonth(DateTime dt) { int thisMonth = dt.Month; int thisYear = dt.Year; string firstOfMonth = thisMonth.ToStr...

how to fetch data from a Microsoft Access database through JDBC and insert the table as a test field in an Applet

hi!! I have made a small letter game in a Java Applet. I have made a microsoft access database through jdbc for the high scores. I hav managed to insert values(scores) into the database but i hav truble in fetching them and display the table in a textArea of ajFrame. I am not even sure if the connection is established. I hav created the ...