sql

phpmyadmin bummer

Hey all people :-) I think i did some nasty crap today... I deleted the user (admin root) in phpmyadmin (XAMPP) now I can't create new users. Any idea how to fix, or should I just try re-install the hole XAMPP packet :-/ I use Leopard iMac. /Willy ...

Reading SQL column name from querystring and building secure query from it

I am building in C#/.NET2.0 a page that updates different columns dynamically in SQL call for example: myajaxpage.aspx?id=1111&fieldname=title What is the correct way to build SQL query for reading column name from querystring? Is this good approach in a first place? I tried: cmd.CommandText = "UPDATE MyTable SET +"Request.QueryStri...

retrieve most recently executed SQL command (T-SQL)

One of my developers working on a trigger-based logging facility in SQL Server 2008 asked me if there was a command to retrieve the most recently executed SQL command within T-SQL. I thought there was a system stored procedure for just such a function, but it's possible I'm thinking of another product from a prior decade... online searc...

SQL with alphabet - thinking like a dictionary

I want to write a query that will match up a string from one table with the closest alphabetical match of a string fragment within another table. Given: There are 2 tables, table 1 has a record with string "gumby". Table 2 has letters that start words: "g", "ga", "gam", "go", "ha", "hi". Problem: Because I don't have a "gu" entry o...

Is there a way to write this as one query?

Here's my situation. I have a user table and a password_reset table which references the user table. I want to run a single query that will return some fields from a password_reset row that references their user row. However, I want to return just the user's email if they DO NOT have a row in password_reset. Here is what I have so far,...

What is the difference between String and nvarchar(5000) in SQL?

I am doing my first database project in PostgreSQL or Oracle. I would like to get an answer for my question. ...

What datatype to use when storing latitude and longitude data in SQL databases?

When storing latitude or longitude data in an ANSI SQL compliant database, what datatype would be most appropriate? Should float be used, or decimal, or ...? I'm aware that Oracle, MySql, and SQL Server have added some special datatypes specifically for handling geo data, but I'm interested in how you would store the information in a "p...

Does specifying a foreign key make it an index?

I have a table T with a primary key id and foreign key f. Is f automatically indexed when it is specified as a foreign key? Do I need explicitly add an index for f ? ...

how to use recently entered id in another table for primary key

declare @@newid int, @@newRequestId int INSERT INTO Projects (ProjectName) VALUES ('first') SELECT [@@newId] = SCOPE_IDENTITY() -- Insert statements for procedure here INSERT INTO Requests (projectId, Title, RequestedBy, ReviewedBy, AssignedTo, EstimatedHours, ActualHours, Status, DateCreated, DateDue, DateAssigned, DateQC, De...

To prevent the use of duplicate Tags in a database

I would like to know how you can prevent to use of two same tags in a database table. One said me that use two private keys in a table. However, W3Schools -website says that it is impossible. My relational table My logical table The context of tables How can you prevent the use of duplicate tags in a question? ...

Is substr or LIKE faster in Oracle?

Would WHERE substr(my_field,1,6) = 'search' or WHERE my_field LIKE 'search%' be faster in Oracle, or would there be no difference? ...

To separate answers in a database

This question is based on this thread. CREATE TABLE Answers ( QUESTION_ID integer FOREIGN KEY REFERENCES Questions(USER_ID) PRIMARY KEY CHECK (USER_ID>0), ANSWER nvarchar(4000) NOT NULL AUTO_INCREMENT ...

Prepared Statement Failing (With an Error Message!)

I am getting this error when trying to insert data into a data table Error Saving data. [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect I looked at the appropriate data table and there does not exist a field called COUNT either hidden or not hidden. Is this some SQL terminology that I should be familiar with? An Extens...

Create or replace role?

Initial Question How do you create or replace a role (that might or might not exist) in Oracle? For example, the following does not work: CREATE OR REPLACE ROLE role_name; GRANT SELECT ON SCM1_VIEW_OBJECT_VW TO role_name; Any way to do this without PL/SQL? Update #1 The following does not compile: CREATE OR REPLACE FUNCTION crea...

Normalize or Denormalize: Store Contact Details (Phone Numbers) in separate Table? Search Performance?

Hello, I'm designing a database application which stores simple contact information (First/Last Name etc.) and I also have to store phone numbers. Besides phone numbers I have to store what they are for (mobile, business etc.) and possibly an additional comment for each. My first approach was to normalize and keep the phone numbers in ...

BULK INSERT from one table to another all on the server

I have to copy a bunch of data from one database table into another. I can't use SELECT ... INTO because one of the columns is an identity column. Also, I have some changes to make to the schema. I was able to use the export data wizard to create an SSIS package, which I then edited in Visual Studio 2005 to make the changes desired and w...

SQL Server 2000 - Breaking a query up into 15 minute blocks

I have a continous time dataset and I want to break it up into 15 minute blocks using sql. I don't want to have to create a new table to be able to do this if I can help it. i.e. Time, Count 09:15, 1 09:30, 3 09:45, 0 10:00, 2 10:15, 3 ..... Does anyone have any idea of how I can do this. I presume that is use a select similar ...

How should an empty title be checked in a database?

I am doing my first database project. I would like to know why you should use NOT NULL in the following query ... TITLE nvarchar(60) NOT NULL .. Context CREATE TABLE Questions ( USER_ID integer FOREIGN KEY REFERENCES User_info(USER_ID) PRIMARY KEY ...

Updating a Table from a Stored Procedure

I am trying to learn database on my own; all of your comments are appreciated. I have the following table. CREATE TABLE AccountTable ( AccountId INT IDENTITY(100,1) PRIMARY KEY, FirstName NVARCHAR(50) NULL, LastName NVARCHAR(50) NULL, Street NVARCHAR(50) NULL, StateId INT REFERENCES STATETABLE(StateId) NOT NULL ) ...

Creating public synonym at system level

I have created public synonym as suggested in my other question about creating view at system level. Having said that I have created individual public synonym out of the view so that I don't have to connect to the individual domain anymore. My problem now is how to create a master kind of public synonym to capture all those synonyms whic...