how do i use workbench to connect to a mysql DB?
i am trying to connect to my local db using workbench but i do not see any way to connect. how do i do it? i already have this connection saved and succesfully tested. ...
sql
Simulating an identity column within an insert trigger
I have a table for logging that needs a log ID but I can't use an identity column because the log ID is part of a combo key. create table StuffLogs { StuffID int LogID int Note varchar(255) } There is a combo key for StuffID & LogID. I want to build an insert trigger that computes the next LogID when inserting log records. ...
Which of these queries is more efficient?
Which of these queries are more efficient? select 1 as newAndClosed from sysibm.sysdummy1 where exists ( select 1 from items where new = 1 ) and not exists ( select 1 from status where open = 1 ) select 1 as newAndClosed ...
Storing SQL in MySQL: Insert as Text?
Hey all, Working in a team environment, each one of us has put together our own list of SQL statements that we use to help with our day to day job functions. As the case often is, there may be some redundancy with this, and we are often in need of each other's statements. To circumvent this, I'm looking to put together a small app tha...
check for empty columns
How can I check if any column in a given table only have null or empty string values? Can I in some way extend this for every table in the database? ...
Approach to Selecting top item matching a criteria
EDIT: my apologies, this was a MSSQL2008 issue. I have a SQL problem that I've come up against routinely, and normally just solved w/ a nested query. I'm hoping someone can suggest a more elegant solution. It often happens that I need to select a result set for a user, conditioned upon it being the most recent, or the most sizeable...
mysql: get all rows into 1 column
hi, i have 3 tables: post (id_post, title) tag (id_tag, name) post_tag (id_post_tag, id_post, id_tag) Lets suppose that id_post 3 has 4 linked tags 1,2,3,4 (soccer, basket, tennis and golf). Is there a way to return something like this in ONE row? col 1 id_post = 3 col 2 tags = soccer basket tennis golf Thanks ...
How to delete rows based on comparison from Data Flow Task in an SSIS?
I have a DataFlow task with two OLE DB Source objects. This is the SQL I want to achieve using SSIS: Insert into server2.db.dbo.[table2] (...) Select col1, col2, col3 ... from Server1.db.dbo.[table1] where [table1.col1] not in (Select col5 from server2.db.dbo.[table2] Where ...) I am pretty new to SSIS and not sure how to achieve thi...
PDO Database Connections Problem
Hey Everyone, Over a year ago I created my own database classes which use PDO, and handle all preparing, executing, and closing connections. These classes have been working great up until now. There are two different database severs I am grabbing from, MySQL, and MS SQL Express. I am retrieving an employee id from the MySQL server and...
Can I put update or create statements in from clause in DB2?
Can I use DML in from clause in DB2? thank you ...
Linked servers SQLNCLI problem. "No transaction is active"
Im trying to execute a stored procedure and simply insert its results in a temporary table, and I'm getting the following message: The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "MyServerName" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "MyServerNam...
MySQL: How to consume/discard the result of a query?
I have a stored procedure which executes an optimize table statement for every table in a DB. Those optimize table statements are prepared statements of course (they have to be built at runtime) and I need to call that procedure from PHP using ext/mysql API. Unfortunately, ext/mysql does't support doing such thing because optimize table...
how to design a schema where the columns of a table are not fixed
I am trying to design a schema where the columns of a table are not fixed. Ex: I have an Employee table where the columns of the table are not fixed and vary (attributes of Employee are not fixed and vary). Frequent addition of a new attribute / column is requirement. Nullable columns in the Employee table itself i.e. no normalization ...
Cost of logic in a query
I have a query that looks something like this: select xmlelement("rootNode", (case when XH.ID is not null then xmlelement("xhID", XH.ID) else xmlelement("xhID", xmlattributes('true' AS "xsi:nil"), XH.ID) end), (case when XH.SER_NUM is not null then xmlelement("serialNumber", XH.SER_NUM) ...
Create an Integrity error for an SQL Server Database for the purpose of Testing
How can I manually introduce an integrity error into my database for the purpose of testing, without totally corrupting the database so that I can still run my program. Updating with a more specific question... The actual feature that I am testing runs DBCC CHECKDB and what I am testing is that it reports a failure to the user. ...
Mysql syntax help
Query: select t1.col1 from table1 t1 inner join with (nolock) table2 t2 on t1.col2 = t2.col1 Am trying to use nolock option for optimized query in mySQL db, but for some reason the above query does not work and the error i receive is You have an error in your SQL syntax; Any thoughts? ...
DataContext Doesn't Exist in Dynamic Data Project?
This is really annoying...and I know it is something extremely simple... 1. I create a new Dynamic Data project. 2. I add a LINQ-to-SQL class and drag and drop some tables onto the class. 3. I open the global.asax.vb and uncomment the line: DefaultModel.RegisterContext(GetType(YourDataContext), New ContextConfiguration() With {.Scaffold...
Detecting abuse for post rating system
I am using a wordpress plugin called "GD Star Rating" to allow my users to vote on stories that I post to one of my websites. http://everydayfiction.com/ Recently we have been having a lot of abuse of the system. Stories that have obviously been voted up artificially. "GD Star Rating" creates some detailed logs when a user votes on a sto...
SQL Max Group By Query Help
Hi All, I have a quick question. How do I select the two values I need in one query? Currently I'm doing this, which works fine, but it's obviously running two queries when one should do the trick. I tried MAX(columnA) and GROUP BY ColumnB, but that returns multiple row. I only want one row returned. DECLARE @biID bigint , @dtThresho...
mysql join with conditional
Hi There, I am currently working on a MySQL query that contains a table: TBL:lesson_fee -fee_type_id (PRI) -lesson_type_id (PRI) -lesson_fee_amount this table contains the fees for a particular 'lesson type' and there are different 'fee names' (fee_type). Which means that there can be many entries in this table for one 'lesson type' ...