Search Results

Search found 1805 results on 73 pages for 'varchar'.

Page 63/73 | < Previous Page | 59 60 61 62 63 64 65 66 67 68 69 70  | Next Page >

• T SQL Rotate row into columns

  - by cshah

  SQL 2005 using T-SQL, I want to rotate rows into columns. Sample script: Use TempDB Go CREATE TABLE [dbo].[CPPrinter_InkLevels]( [CPPrinter_InkLevels_ID] [int] IDENTITY(1,1) NOT NULL, [CPMeasurementGUID] [uniqueidentifier] NOT NULL, [InkName] [varchar](30) NOT NULL, [InkLevel] [decimal](6, 2) NOT NULL, CONSTRAINT [PK_CPPrinter_InkLevels] PRIMARY KEY CLUSTERED ( [CPPrinter_InkLevels_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[CPPrinter_InkLevels] ON INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (1, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Black', CAST(0.60 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (2, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Cyan', CAST(0.69 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (3, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Magenta', CAST(0.55 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (4, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Yellow', CAST(0.51 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (5, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Light Black', CAST(0.64 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (6, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Light Cyan', CAST(0.43 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (7, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Light Magenta', CAST(0.30 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (8, N'6acc1562-4e02-45ff-b480-9e01fb97fccf', N'Waste Tank', CAST(0.18 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (9, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Black', CAST(0.60 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (10, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Cyan', CAST(0.69 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (11, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Magenta', CAST(0.55 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (12, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Yellow', CAST(0.51 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (13, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Light Black', CAST(0.64 AS Decimal(6, 2))) INSERT [dbo].[CPPrinter_InkLevels] ([CPPrinter_InkLevels_ID], [CPMeasurementGUID], [InkName], [InkLevel]) VALUES (14, N'932348a7-6e2f-4a10-9760-be1ae640c7d7', N'Light Cyan', CAST(0.43 AS Decimal(6, 2))) Go SELECT * FROM [dbo].[CPPrinter_InkLevels] --Desired output CPMeasuremnetGUID, Ink1, Level1, Ink2, Level2, Ink3, Level3....

  Read the article

• MySQL stored procedure, handling multiple cursors and query results

  - by Tirithen

  How can I use two cursors in the same routine? If I remove the second cursor declaration and fetch loop everthing works fine. The routine is used for adding a friend in my webapp. It takes the id of the current user and the email of the friend we want to add as a friend, then it checks if the email has a corresponding user id and if no friend relation exists it will create one. Any other routine solution than this one would be great as well. DROP PROCEDURE IF EXISTS addNewFriend; DELIMITER // CREATE PROCEDURE addNewFriend(IN inUserId INT UNSIGNED, IN inFriendEmail VARCHAR(80)) BEGIN DECLARE tempFriendId INT UNSIGNED DEFAULT 0; DECLARE tempId INT UNSIGNED DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT id FROM users WHERE email = inFriendEmail; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; REPEAT FETCH cur INTO tempFriendId; UNTIL done = 1 END REPEAT; CLOSE cur; DECLARE cur CURSOR FOR SELECT user_id FROM users_friends WHERE user_id = tempFriendId OR friend_id = tempFriendId; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; REPEAT FETCH cur INTO tempId; UNTIL done = 1 END REPEAT; CLOSE cur; IF tempFriendId != 0 AND tempId != 0 THEN INSERT INTO users_friends (user_id, friend_id) VALUES(inUserId, tempFriendId); END IF; SELECT tempFriendId as friendId; END // DELIMITER ;

  Read the article

• vbCrLf in Multiline TextBox shows up only when .Trim() is called

  - by Brandon Montgomery

  I have an ASP TextBox with TextMode set to MultiLine. I'm having problems with preserving the vbCrLf characters when a user tries to put line breaks into the text. When a button on the page is pressed, I'm taking the text from the control, trimming it using String.Trim, and assigning that value to a String property on an object (which, in turn assigns it to a private internal String variable on the object). The object then takes the value from the private internal variable and throws it into the database using a stored procedure call (the SP parameter it is put into is an nvarchar(4000)). ASPX Page: <asp:UpdatePanel ID="UpdatePanel2" runat="server" RenderMode="Inline" UpdateMode="Conditional" ChildrenAsTriggers="true"> <ContentTemplate> <!-- some other controls and things --> <asp:TextBox TextMode="MultiLine" runat="server" ID="txtComments" Width="100%" Height="60px" CssClass="TDTextArea" Style="border: 0px;" MaxLength="2000" /> <!-- some other controls and things --> </ContentTemplate> </asp:UpdatePanel> code behind: ProjectRequest.StatusComments = txtComments.Text.Trim object property: Protected mStatusComments As String = String.Empty Property StatusComments() As String Get Return mStatusComments.Trim End Get Set(ByVal Value As String) mStatusComments = Value End Set End Property stored proc call: Common.RunSP(mDBConnStr, "ProjectStatusUpdate", _ Common.MP("@UID", SqlDbType.NVarChar, 40, mUID), _ Common.MP("@ProjID", SqlDbType.VarChar, 40, mID), _ Common.MP("@StatusID", SqlDbType.Int, 8, mStatusID), _ Common.MP("@Comments", SqlDbType.NVarChar, 4000, mStatusComments), _ Common.MP("@PCTComp", SqlDbType.Int, 4, 0), _ Common.MP("@Type", Common.TDSqlDbType.TinyInt, 1, EntryType)) Here's the strangest part. When I debug the code, if I type "test test" (without the quotes) into the comments text box, then click the save button and use the immediate window to view the variable values as I step through, here is what I get: ?txtComments.Text "test test" ?txtComments.Text.Trim "test test" ?txtComments.Text(4) " "c ?txtComments.Text.Trim()(4) " "c Anyone have a clue as to what's going on here?

  Read the article

• cryptic error message: Length of Bind host variable exceeds MaxLength

  - by janetsmith

  Hi, I've encountered a cryptic error message thrown by Sybase IQ server. com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -1001019: Function not supported on varchars longer than 255 Length of Bind host variable exceeds MaxLength , -- (df_Heap.cxx 2145) at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636) at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996) at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69) at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204) at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187) at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1642) at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1625) at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:91) at ibs.dao.CM3RM1DAO.updateToTable(CM3RM1DAO.java:197) at ibs.dao.CM3RM1DAO.isXMLProcessed(CM3RM1DAO.java:88) at ibs.xml.parser.XMLParser.parsingXMLIntoBO(XMLParser.java:2125) at ibs.common.util.MainClass.main(MainClass.java:74) We have several columns (DESCRIPTION etc.) which are of type varchar(4000). However I can update them directly without having any error. And, I don't see any code specifying any bind variables, so I have no idea where the message comes from. This is the code (I've modified it a bit): String sql = "UPDATE TABLEX SET " + "COMPANY = ?, CUSTOMER_REFERENCE= ?, " + "STATUS = ?, CONTACT_FIRST_NAME = ?, CONTACT_LAST_NAME = ?, " + "SEVERITY = ?, PRIORITY_CODE = ?, REQUESTEDDATE = ?, " + "CLOSE_TIME = ?, LEAD_TIME = ?, CHANGE_REASON = ?, MODTIME = ? WHERE NUMBER = ?"; stmt = conn.prepareStatement(sql); for loop here { stmt.setString(...); . . stmt.executeUpdate(); } Any help is appreciated

  Read the article

• MySQL Unique hash insertion

  - by Jesse

  So, imagine a mysql table with a few simple columns, an auto increment, and a hash (varchar, UNIQUE). Is it possible to give mysql a query that will add a column, and generate a unique hash without multiple queries? Currently, the only way I can think of to achieve this is with a while, which I worry would become more and more processor intensive the more entries were in the db. Here's some pseudo-php, obviously untested, but gets the general idea across: while(!query("INSERT INTO table (hash) VALUES (".generate_hash().");")){ //found conflict, try again. } In the above example, the hash column would be UNIQUE, and so the query would fail. The problem is, say there's 500,000 entries in the db and I'm working off of a base36 hash generator, with 4 characters. The likelyhood of a conflict would be almost 1 in 3, and I definitely can't be running 160,000 queries. In fact, any more than 5 I would consider unacceptable. So, can I do this with pure SQL? I would need to generate a base62, 6 char string (like: "j8Du7X", chars a-z, A-Z, and 0-9), and either update the last_insert_id with it, or even better, generate it during the insert. I can handle basic CRUD with MySQL, but even JOINs are a little outside of my MySQL comfort zone, so excuse my ignorance if this is cake. Any ideas? I'd prefer to use either pure MySQL or PHP & MySQL, but hell, if another language can get this done cleanly, I'd build a script and AJAX it too. Thanks!

  Read the article

• How to debug MySQL/Doctrine2 Queries?

  - by jiewmeng

  I am using MySQL with Zend Framework & Doctrine 2. I think even if you don't use Doctrine 2, you will be familiar with errors like SQLSTATE[42000]: Syntax error or access violation: 1064 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 'ASC' at line 1 The problem is that I don't see the full query. Without an ORM framework, I could probably echo the sql easily, but with a framework, how can I find out what SQL its trying to execute? I narrowed the error down to $progress = $task->getProgress(); $progress is declared // Application\Models\Task /** * @OneToMany(targetEntity="TaskProgress", mappedBy="task") * @OrderBy({"seq" = "ASC"}) */ protected $progress; In MySQL, the task class looks like CREATE TABLE `tasks` ( `id` int(11) NOT NULL AUTO_INCREMENT, `owner_id` int(11) DEFAULT NULL, `assigned_id` int(11) DEFAULT NULL, `list_id` int(11) DEFAULT NULL, `name` varchar(60) NOT NULL, `seq` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `tasks_owner_id_idx` (`owner_id`), KEY `tasks_assigned_id_idx` (`assigned_id`), KEY `tasks_list_id_idx` (`list_id`), CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`), CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`assigned_id`) REFERENCES `users` (`id`), CONSTRAINT `tasks_ibfk_3` FOREIGN KEY (`list_id`) REFERENCES `lists` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1$$

  Read the article

• SQL Server insert with XML parameter - empty string not converting to null for numeric

  - by Mayo

  I have a stored procedure that takes an XML parameter and inserts the "Entity" nodes as records into a table. This works fine unless one of the numeric fields has a value of empty string in the XML. Then it throws an "error converting data type nvarchar to numeric" error. Is there a way for me to tell SQL to convert empty string to null for those numeric fields in the code below? -- @importData XML <- stored procedure param DECLARE @l_index INT EXECUTE sp_xml_preparedocument @l_index OUTPUT, @importData INSERT INTO dbo.myTable ( [field1] ,[field2] ,[field3] ) SELECT [field1] ,[field2] ,[field3] FROM OPENXML(@l_index, 'Entities/Entity', 1) WITH ( field1 int 'field1' ,field2 varchar(40) 'field2' ,field3 decimal(15, 2) 'field3' ) EXECUTE sp_xml_removedocument @l_index EDIT: And if it helps, sample XML. Error is thrown unless I comment out field3 in the code above or provide a value in field3 below. <?xml version="1.0" encoding="utf-16"?> <Entities> <Entity> <field1>2435</field1> <field2>843257-3242</field2> <field3 /> </Entity> </Entities>

  Read the article

• Database warehouse design: fact tables and dimension tables

  - by morpheous

  I am building a poor man's data warehouse using a RDBMS. I have identified the key 'attributes' to be recorded as: sex (true/false) demographic classification (A, B, C etc) place of birth date of birth weight (recorded daily): The fact that is being recorded My requirements are to be able to run 'OLAP' queries that allow me to: 'slice and dice' 'drill up/down' the data and generally, be able to view the data from different perspectives After reading up on this topic area, the general consensus seems to be that this is best implemented using dimension tables rather than normalized tables. Assuming that this assertion is true (i.e. the solution is best implemented using fact and dimension tables), I would like to seek some help in the design of these tables. 'Natural' (or obvious) dimensions are: Date dimension Geographical location Which have hierarchical attributes. However, I am struggling with how to model the following fields: sex (true/false) demographic classification (A, B, C etc) The reason I am struggling with these fields is that: They have no obvious hierarchical attributes which will aid aggregation (AFAIA) - which suggest they should be in a fact table They are mostly static or very rarely change - which suggests they should be in a dimension table. Maybe the heuristic I am using above is too crude? I will give some examples on the type of analysis I would like to carryout on the data warehouse - hopefully that will clarify things further. I would like to aggregate and analyze the data by sex and demographic classification - e.g. answer questions like: How does male and female weights compare across different demographic classifications? Which demographic classification (male AND female), show the most increase in weight this quarter. etc. Can anyone clarify whether sex and demographic classification are part of the fact table, or whether they are (as I suspect) dimension tables.? Also assuming they are dimension tables, could someone elaborate on the table structures (i.e. the fields)? The 'obvious' schema: CREATE TABLE sex_type (is_male int); CREATE TABLE demographic_category (id int, name varchar(4)); may not be the correct one.

  Read the article

• JPA ManyToMany referencing issue

  - by dharga

  I have three tables. AvailableOptions and PlanTypeRef with a ManyToMany association table called AvailOptionPlanTypeAssoc. The trimmed down schemas look like this CREATE TABLE [dbo].[AvailableOptions]( [SourceApplication] [char](8) NOT NULL, [OptionId] [int] IDENTITY(1,1) NOT NULL, ... ) CREATE TABLE [dbo].[AvailOptionPlanTypeAssoc]( [SourceApplication] [char](8) NOT NULL, [OptionId] [int] NOT NULL, [PlanTypeCd] [char](2) NOT NULL, ) CREATE TABLE [dbo].[PlanTypeRef]( [PlanTypeCd] [char](2) NOT NULL, [PlanTypeDesc] [varchar](32) NOT NULL, ) And the Java code looks like this. //AvailableOption.java @ManyToMany(cascade={CascadeType.ALL}, fetch=FetchType.EAGER) @JoinTable( name = "AvailOptionPlanTypeAssoc", joinColumns = { @JoinColumn(name = "OptionId"), @JoinColumn(name="SourceApplication")}, inverseJoinColumns=@JoinColumn(name="PlanTypeCd")) List<PlanType> planTypes = new ArrayList<PlanType>(); //PlanType.java @ManyToMany @JoinTable( name = "AvailOptionPlanTypeAssoc", joinColumns = { @JoinColumn(name = "PlanTypeCd")}, inverseJoinColumns={@JoinColumn(name="OptionId"), @JoinColumn(name="SourceApplication")}) List<AvailableOption> options = new ArrayList<AvailableOption>(); The problem arises when making a select on AvailableOptions it joins back onto itself. Note the following SQL code from the backtrace. The second inner join should be on PlanTypeRef. SELECT t0.OptionId, t0.SourceApplication, t2.PlanTypeCd, t2.EffectiveDate, t2.PlanTypeDesc, t2.SysLstTrxDtm, t2.SysLstUpdtUserId, t2.TermDate FROM dbo.AvailableOptions t0 INNER JOIN dbo.AvailOptionPlanTypeAssoc t1 ON t0.OptionId = t1.OptionId AND t0.SourceApplication = t1.SourceApplication INNER JOIN dbo.AvailableOptions t2 ON t1.PlanTypeCd = t2.PlanTypeCd WHERE (t0.SourceApplication = ? AND t0.OptionType = ?) ORDER BY t0.OptionId ASC, t0.SourceApplication ASC [params=(String) testApp, (String) junit0]}

  Read the article

• Query returns too few rows

  - by Tareq

  setup: mysql> create table product_stock( product_id integer, qty integer, branch_id integer); Query OK, 0 rows affected (0.17 sec) mysql> create table product( product_id integer, product_name varchar(255)); Query OK, 0 rows affected (0.11 sec) mysql> insert into product(product_id, product_name) values(1, 'Apsana White DX Pencil'); Query OK, 1 row affected (0.05 sec) mysql> insert into product(product_id, product_name) values(2, 'Diamond Glass Marking Pencil'); Query OK, 1 row affected (0.03 sec) mysql> insert into product(product_id, product_name) values(3, 'Apsana Black Pencil'); Query OK, 1 row affected (0.03 sec) mysql> insert into product_stock(product_id, qty, branch_id) values(1, 100, 1); Query OK, 1 row affected (0.03 sec) mysql> insert into product_stock(product_id, qty, branch_id) values(1, 50, 2); Query OK, 1 row affected (0.03 sec) mysql> insert into product_stock(product_id, qty, branch_id) values(2, 80, 1); Query OK, 1 row affected (0.03 sec) my query: mysql> SELECT IFNULL(SUM(s.qty),0) AS stock, product_name FROM product_stock s RIGHT JOIN product p ON s.product_id=p.product_id WHERE branch_id=1 GROUP BY product_name ORDER BY product_name; returns: +-------+-------------------------------+ | stock | product_name | +-------+-------------------------------+ | 100 | Apsana White DX Pencil | | 80 | Diamond Glass Marking Pencil | +-------+-------------------------------+ 1 row in set (0.00 sec) But I want to have the following result: +-------+------------------------------+ | stock | product_name | +-------+------------------------------+ | 0 | Apsana Black Pencil | | 100 | Apsana White DX Pencil | | 80 | Diamond Glass Marking Pencil | +-------+------------------------------+ To get this result what mysql query should I run?

  Read the article

• Character Encoding: â??

  - by akaphenom

  I am trying to piece together the mysterious string of characters â?? I am seeing quite a bit of in our database - I am fairly sure this is a result of conversion between character encodings, but I am not completely positive. The users are able to enter text (or cut and paste) into a Ext-Js rich text editor. The data is posted to a severlet which persists it to the database, and when I view it in the database i see those strange characters... is there any way to decode these back to their original meaning, if I was able to discover the correct encoding - or is there a loss of bits or bytes that has occured through the conversion process? Users are cutting and pasting from multiple versions of MS Word and PDF. Does the encoding follow where the user copied from? Thank you website is UTF-8 We are using ms sql server 2005; SELECT serverproperty('Collation') -- Server default collation. Latin1_General_CI_AS SELECT databasepropertyex('xxxx', 'Collation') -- Database default SQL_Latin1_General_CP1_CI_AS and the column: Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation text varchar no -1 yes no yes SQL_Latin1_General_CP1_CI_AS The non-Unicode equivalents of the nchar, nvarchar, and ntext data types in SQL Server 2000 are listed below. When Unicode data is inserted into one of these non-Unicode data type columns through a command string (otherwise known as a "language event"), SQL Server converts the data to the data type using the code page associated with the collation of the column. When a character cannot be represented on a code page, it is replaced by a question mark (?), indicating the data has been lost. Appearance of unexpected characters or question marks in your data indicates your data has been converted from Unicode to non-Unicode at some layer, and this conversion resulted in lost characters. So this may be the root cause of the problem... and not an easy one to solve on our end.

  Read the article

• Cascading Deletes in SQL Sever 2008 not working.

  - by Vaccano

  I have the following table setup. Bag | +-> BagID (Guid) +-> BagNumber (Int) BagCommentRelation | +-> BagID (Int) +-> CommentID (Guid) BagComment | +-> CommentID (Guid) +-> Text (varchar(200)) BagCommentRelation has Foreign Keys to Bag and BagComment. So, I turned on cascading deletes for both those Foreign Keys, but when I delete a bag, it does not delete the Comment row. Do need to break out a trigger for this? Or am I missing something? (I am using SQL Server 2008) Note: Posting requested SQL. This is the defintion of the BagCommentRelation table. (I had the type of the bagID wrong (I thought it was a guid but it is an int).) CREATE TABLE [dbo].[Bag_CommentRelation]( [Id] [int] IDENTITY(1,1) NOT NULL, [BagId] [int] NOT NULL, [Sequence] [int] NOT NULL, [CommentId] [int] NOT NULL, CONSTRAINT [PK_Bag_CommentRelation] PRIMARY KEY CLUSTERED ( [BagId] ASC, [Sequence] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Bag_CommentRelation] WITH CHECK ADD CONSTRAINT [FK_Bag_CommentRelation_Bag] FOREIGN KEY([BagId]) REFERENCES [dbo].[Bag] ([Id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Bag_CommentRelation] CHECK CONSTRAINT [FK_Bag_CommentRelation_Bag] GO ALTER TABLE [dbo].[Bag_CommentRelation] WITH CHECK ADD CONSTRAINT [FK_Bag_CommentRelation_Comment] FOREIGN KEY([CommentId]) REFERENCES [dbo].[Comment] ([CommentId]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Bag_CommentRelation] CHECK CONSTRAINT [FK_Bag_CommentRelation_Comment] GO The row in this table deletes but the row in the comment table does not.

  Read the article

• SQLiteDataAdapter Fill exception C# ADO.NET

  - by Lirik

  I'm trying to use the OleDb CSV parser to load some data from a CSV file and insert it into a SQLite database, but I get an exception with the OleDbAdapter.Fill method and it's frustrating: An unhandled exception of type 'System.Data.ConstraintException' occurred in System.Data.dll Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. Here is the source code: public void InsertData(String csvFileName, String tableName) { String dir = Path.GetDirectoryName(csvFileName); String name = Path.GetFileName(csvFileName); using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + @";Extended Properties=""Text;HDR=No;FMT=Delimited""")) { conn.Open(); using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + name, conn)) { QuoteDataSet ds = new QuoteDataSet(); adapter.Fill(ds, tableName); // <-- Exception here InsertData(ds, tableName); // <-- Inserts the data into the my SQLite db } } } class Program { static void Main(string[] args) { SQLiteDatabase target = new SQLiteDatabase(); string csvFileName = "D:\\Innovations\\Finch\\dev\\DataFeed\\YahooTagsInfo.csv"; string tableName = "Tags"; target.InsertData(csvFileName, tableName); Console.ReadKey(); } } The "YahooTagsInfo.csv" file looks like this: tagId,tagName,description,colName,dataType,realTime 1,s,Symbol,symbol,VARCHAR,FALSE 2,c8,After Hours Change,afterhours,DOUBLE,TRUE 3,g3,Annualized Gain,annualizedGain,DOUBLE,FALSE 4,a,Ask,ask,DOUBLE,FALSE 5,a5,Ask Size,askSize,DOUBLE,FALSE 6,a2,Average Daily Volume,avgDailyVolume,DOUBLE,FALSE 7,b,Bid,bid,DOUBLE,FALSE 8,b6,Bid Size,bidSize,DOUBLE,FALSE 9,b4,Book Value,bookValue,DOUBLE,FALSE I've tried the following: Removing the first line in the CSV file so it doesn't confuse it for real data. Changing the TRUE/FALSE realTime flag to 1/0. I've tried 1 and 2 together (i.e. removed the first line and changed the flag). None of these things helped... One constraint is that the tagId is supposed to be unique. Here is what the table look like in design view: Can anybody help me figure out what is the problem here?

  Read the article

• Multi-base conversion - using all combinations for URL shortener

  - by Guffa

  I am making an URL shortener, and I am struggling with the optimal way of encoding a number (id) into a character string. I am using the characters 0-9,A-Z,a-z so it will basically be a base-62 encoding. That is pretty basic, but it doesn't make use of all possible codes. The codes that it would produce would be: 0, 1, ... y, z, 10, 11, ... zy, zz, 100, 101, ... Notice that the codes 00 to 0z is not used, the same for 000 to 0zz, and so on. I would like to use all the codes, like this: 0, 1, ... y, z, 00, 01, ... zy, zz, 000, 001, ... It would be some combination of base-62 and base-63, with different bases depending on the position... Using base-62 is easy, for example: create procedure tiny_GetCode @UrlId int as set nocount on declare @Code varchar(10) set @Code = '' while (@UrlId > 0 or len(@Code) = 0) begin set @Code = substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', @UrlId % 62 + 1, 1) + @Code set @UrlId = @UrlId / 62 end select @Code But I haven't yet managed to make a multi-base conversion out of it, to make use of all the codes.

  Read the article

• mysql match against russain

  - by Devenv

  Hey, Trying to solve this for a very long time now... SELECT MATCH(name) AGAINST('????????') (russian) doesn't work, but SELECT MATCH(name) AGAINST('abraxas') (english) work perfectly. I know it's something with character-set, but I tried all kind of settings and it didn't work. For now it's latin-1. LIKE works This is the show variables charset related: character_set_client - latin1 character_set_connection - latin1 character_set_database - latin1 character_set_filesystem - binary character_set_results - latin1 character_set_server - latin1 character_set_system - utf8 character_sets_dir - /usr/share/mysql/charsets/ collation_connection - latin1_swedish_ci collation_database - latin1_swedish_ci collation_server - latin1_swedish_ci chunk of /etc/my.cnf default-character-set=latin1 skip-character-set-client-handshake chunk of the dump: /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; DROP TABLE IF EXISTS `scenes_raw`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `scenes_raw` ( `scene_name` varchar(40) DEFAULT NULL, ...blabla... ) ENGINE=MyISAM AUTO_INCREMENT=901 DEFAULT CHARSET=utf8; (I did tests without skip-character-set-client-handshake too) SHOW TABLE STATUS WHERE Name = 'scenes_raw'\G Name: scenes_raw Engine: MyISAM Version: 10 Row_format: Dynamic Index_length: 23552 Collation: utf8_general_ci Checksum: NULL Create_options:

  Read the article

• Autocomplet extender control in ajax (asp.net)?

  - by Surya sasidhar

  hi, i am using autocomplete extender in my application but it is not working. this is my code.. <form id="form1" runat="server"> <asp:scriptmanager ID="Scriptmanager1" runat="server"></asp:scriptmanager> <br /> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <div> <cc1:AutoCompleteExtender TargetControlID="TextBox1" MinimumPrefixLength="1" ServiceMethod="GetVideoTitles" CompletionSetCount="10" ServicePath="Myservices.asmx" ID="AutoCompleteExtender1" runat="server"> </cc1:AutoCompleteExtender> </div> </form> this is webservice method public string[] GetVideoTitles(string prefixText) { SqlConnection con = new SqlConnection(@"Data Source=SERVER5\SQLserver2005;Initial Catalog=tpvnew;User ID=xx;Password=525"); con.Open(); SqlCommand cmd = new SqlCommand("video_videotitles", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@prefixText", SqlDbType.VarChar, 50); cmd.Parameters["@prefixText"].Value = prefixText; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); string[] items = new string[dt.Rows.Count]; int i = 0; foreach (DataRow dr in dt.Rows) { items.SetValue(dr["videotitle"].ToString(), i); i++; } return items; }

  Read the article

• SQL database problems with addressbook table design

  - by Sebastian Hoitz

  Hello! I am writing a addressbook module for my software right now. I have the database set up so far that it supports a very flexible address-book configuration. I can create n-entries for every type I want. Type means here data like 'email', 'address', 'telephone' etc. I have a table named 'contact_profiles'. This only has two columns: id Primary key date_created DATETIME And then there is a table called contact_attributes. This one is a little more complex: id PK #profile (Foreign key to contact_profiles.id) type VARCHAR describing the type of the entry (name, email, phone, fax, website, ...) I should probably change this to a SET later. value Text (containing the value for the attribute). I can now link to these profiles, for example from my user's table. But from here I run into problems. At the moment I would have to create a JOIN for each value that I want to retrieve. Is there a possibility to somehow create a View, that gives me a result with the type's as columns? So right now I would get something like #profile type value 1 email [email protected] 1 name Sebastian Hoitz 1 website domain.tld But it would be nice to get a result like this: #profile email name website 1 [email protected] Sebastian Hoitz domain.tld The reason I do not want to create the table layout like this initially is, that there might always be things to add and I want to be able to have multiple attributes of the same type. So do you know if there is any possibility to convert this dynamically? If you need a better description please let me know. Thank you!

  Read the article

• Database warehoue design: fact tables and dimension tables

  - by morpheous

  I am building a poor man's data warehouse using a RDBMS. I have identified the key 'attributes' to be recorded as: sex (true/false) demographic classification (A, B, C etc) place of birth date of birth weight (recorded daily): The fact that is being recorded My requirements are to be able to run 'OLAP' queries that allow me to: 'slice and dice' 'drill up/down' the data and generally, be able to view the data from different perspectives After reading up on this topic area, the general consensus seems to be that this is best implemented using dimension tables rather than normalized tables. Assuming that this assertion is true (i.e. the solution is best implemented using fact and dimension tables), I would like to see some help in the design of these tables. 'Natural' (or obvious) dimensions are: Date dimension Geographical location Which have hierarchical attributes. However, I am struggling with how to model the following fields: sex (true/false) demographic classification (A, B, C etc) The reason I am struggling with these fields is that: They have no obvious hierarchical attributes which will aid aggregation (AFAIA) - which suggest they should be in a fact table They are mostly static or very rarely change - which suggests they should be in a dimension table. Maybe the heuristic I am using above is too crude? I will give some examples on the type of analysis I would like to carryout on the data warehouse - hopefully that will clarify things further. I would like to aggregate and analyze the data by sex and demographic classification - e.g. answer questions like: How does male and female weights compare across different demographic classifications? Which demographic classification (male AND female), show the most increase in weight this quarter. etc. Can anyone clarify whether sex and demographic classification are part of the fact table, or whether they are (as I suspect) dimension tables.? Also assuming they are dimension tables, could someone elaborate on the table structures (i.e. the fields)? The 'obvious' schema: CREATE TABLE sex_type (is_male int); CREATE TABLE demographic_category (id int, name varchar(4)); may not be the correct one.

  Read the article

• SQL Server Bulk insert of CSV file with inconsistent quotes

  - by mattstuehler

  Is it possible to BULK INSERT (SQL Server) a CSV file in which the fields are only OCCASSIONALLY surrounded by quotes? Specifically, quotes only surround those fields that contain a ",". In other words, I have data that looks like this (the first row contain headers): id, company, rep, employees 729216,INGRAM MICRO INC.,"Stuart, Becky",523 729235,"GREAT PLAINS ENERGY, INC.","Nelson, Beena",114 721177,GEORGE WESTON BAKERIES INC,"Hogan, Meg",253 Because the quotes aren't consistent, I can't use '","' as a delimiter, and I don't know how to create a format file that accounts for this. I tried using ',' as a delimter and loading it into a temporary table where every column is a varchar, then using some kludgy processing to strip out the quotes, but that doesn't work either, because the fields that contain ',' are split into multiple columns. Unfortunately, I don't have the ability to manipulate the CSV file beforehand. Is this hopeless? Many thanks in advance for any advice. By the way, i saw this post SQL bulk import from csv, but in that case, EVERY field was consistently wrapped in quotes. So, in that case, he could use ',' as a delimiter, then strip out the quotes afterwards.

  Read the article

• SQL deadlock on delete then bulk insert

  - by StarLite

  I have an issue with a deadlock in SQL Server that I haven't been able to resolve. Basically I have a large number of concurrent connections (from many machines) that are executing transactions where they first delete a range of entries and then re-insert entries within the same range with a bulk insert. Essentially, the transaction looks like this BEGIN TRANSACTION T1 DELETE FROM [TableName] WITH( XLOCK HOLDLOCK ) WHERE [Id]=@Id AND [SubId]=@SubId INSERT BULK [TableName] ( [Id] Int , [SubId] Int , [Text] VarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS ) WITH(CHECK_CONSTRAINTS, FIRE_TRIGGERS) COMMIT TRANSACTION T1 The bulk insert only inserts items matching the Id and SubId of the deletion in the same transaction. Furthermore, these Id and SubId entries should never overlap. When I have enough concurrent transaction of this form, I start to see a significant number of deadlocks between these statements. I added the locking hints XLOCK HOLDLOCK to attempt to deal with the issue, but they don't seem to be helpling. The canonical deadlock graph for this error shows: Connection 1: Holds RangeX-X on PK_TableName Holds IX Page lock on the table Requesting X Page lock on the table Connection 2: Holds IX Page lock on the table Requests RangeX-X lock on the table What do I need to do in order to ensure that these deadlocks don't occur. I have been doing some reading on the RangeX-X locks and I'm not sure I fully understand what is going on with these. Do I have any options short of locking the entire table here?

  Read the article

• Mysql latin1 turkish data and delphi 2010 utf8

  - by sabri.arslan

  Hello, I have tables collating latin1_general_ci and have turkish character values. And i can use this data on delphi 7+zeos with no problem. but i want to upgrade my delphi to 2010 version but zeos too slow as i saw. so i want to use odbc+ado or dbexpress solution. dbexpress solution works fine , display my data as entered and write as entered table without any change to column charset. but dbexpress has problems as i saw. for example when i select * from table which has column types as varchar,decimal,int,tinyint,text give av errors on xp systems. vista and 7 does not give any error and work fine(not fully tested). ado solution(dbgo) works fine but its not show my data as entered.its want everything be utf. but i don't want to convert my data to utf before test everything. how can i see my data as entered and write client side utf and store latin1(as zeos or dbexpress do). i was tried many other options. eg. mysql side collation and charset parameters. sorry for my bad english. i hope someone understand me. thanks.

  Read the article

• Trouble with an depreciated constrictor visual basic visual studio 2010

  - by VBPRIML

  My goal is to print labels with barcodes and a date stamp from an entry to a zebra TLP 2844 when the user clicks the ok button/hits enter. i found what i think might be the code for this from zebras site and have been integrating it into my program but part of it is depreciated and i cant quite figure out how to update it. below is what i have so far. The printer is attached via USB and the program will also store the entered numbers in a database but i have that part done. any help would be greatly Appreciated.   Public Class ScanForm      Inherits System.Windows.Forms.Form    Public Const GENERIC_WRITE = &H40000000    Public Const OPEN_EXISTING = 3    Public Const FILE_SHARE_WRITE = &H2      Dim LPTPORT As String    Dim hPort As Integer      Public Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" (ByVal lpFileName As String,                                                                           ByVal dwDesiredAccess As Integer,                                                                           ByVal dwShareMode As Integer, <MarshalAs(UnmanagedType.Struct)> ByRef lpSecurityAttributes As SECURITY_ATTRIBUTES,                                                                           ByVal dwCreationDisposition As Integer, ByVal dwFlagsAndAttributes As Integer,                                                                           ByVal hTemplateFile As Integer) As Integer          Public Declare Function CloseHandle Lib "kernel32" Alias "CloseHandle" (ByVal hObject As Integer) As Integer      Dim retval As Integer           <StructLayout(LayoutKind.Sequential)> Public Structure SECURITY_ATTRIBUTES          Private nLength As Integer        Private lpSecurityDescriptor As Integer        Private bInheritHandle As Integer      End Structure            Private Sub OKButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OKButton.Click          Dim TrNum        Dim TrDate        Dim SA As SECURITY_ATTRIBUTES        Dim outFile As FileStream, hPortP As IntPtr          LPTPORT = "USB001"        TrNum = Me.ScannedBarcodeText.Text()        TrDate = Now()          hPort = CreateFile(LPTPORT, GENERIC_WRITE, FILE_SHARE_WRITE, SA, OPEN_EXISTING, 0, 0)          hPortP = New IntPtr(hPort) 'convert Integer to IntPtr          outFile = New FileStream(hPortP, FileAccess.Write) 'Create FileStream using Handle        Dim fileWriter As New StreamWriter(outFile)          fileWriter.WriteLine(" ")        fileWriter.WriteLine("N")        fileWriter.Write("A50,50,0,4,1,1,N,")        fileWriter.Write(Chr(34))        fileWriter.Write(TrNum) 'prints the tracking number variable        fileWriter.Write(Chr(34))        fileWriter.Write(Chr(13))        fileWriter.Write(Chr(10))        fileWriter.Write("A50,100,0,4,1,1,N,")        fileWriter.Write(Chr(34))        fileWriter.Write(TrDate) 'prints the date variable        fileWriter.Write(Chr(34))        fileWriter.Write(Chr(13))        fileWriter.Write(Chr(10))        fileWriter.WriteLine("P1")        fileWriter.Flush()        fileWriter.Close()        outFile.Close()        retval = CloseHandle(hPort)          'Add entry to database        Using connection As New SqlClient.SqlConnection("Data Source=MNGD-LABS-APP02;Initial Catalog=ScannedDB;Integrated Security=True;Pooling=False;Encrypt=False"), _        cmd As New SqlClient.SqlCommand("INSERT INTO [ScannedDBTable] (TrackingNumber, Date) VALUES (@TrackingNumber, @Date)", connection)            cmd.Parameters.Add("@TrackingNumber", SqlDbType.VarChar, 50).Value = TrNum            cmd.Parameters.Add("@Date", SqlDbType.DateTime, 8).Value = TrDate            connection.Open()            cmd.ExecuteNonQuery()            connection.Close()        End Using          'Prepare data for next entry        ScannedBarcodeText.Clear()        Me.ScannedBarcodeText.Focus()      End Sub

  Read the article

• Re-using aggregate level formulas in SQL - any good tactics?

  - by Cade Roux

  Imagine this case, but with a lot more component buckets and a lot more intermediates and outputs. Many of the intermediates are calculated at the detail level, but a few things are calculated at the aggregate level: DECLARE @Profitability AS TABLE ( Cust INT NOT NULL ,Category VARCHAR(10) NOT NULL ,Income DECIMAL(10, 2) NOT NULL ,Expense DECIMAL(10, 2) NOT NULL ) ; INSERT INTO @Profitability VALUES ( 1, 'Software', 100, 50 ) ; INSERT INTO @Profitability VALUES ( 2, 'Software', 100, 20 ) ; INSERT INTO @Profitability VALUES ( 3, 'Software', 100, 60 ) ; INSERT INTO @Profitability VALUES ( 4, 'Software', 500, 400 ) ; INSERT INTO @Profitability VALUES ( 5, 'Hardware', 1000, 550 ) ; INSERT INTO @Profitability VALUES ( 6, 'Hardware', 1000, 250 ) ; INSERT INTO @Profitability VALUES ( 7, 'Hardware', 1000, 700 ) ; INSERT INTO @Profitability VALUES ( 8, 'Hardware', 5000, 4500 ) ; SELECT Cust ,Profit = SUM(Income - Expense) ,Margin = SUM(Income - Expense) / SUM(Income) FROM @Profitability GROUP BY Cust SELECT Category ,Profit = SUM(Income - Expense) ,Margin = SUM(Income - Expense) / SUM(Income) FROM @Profitability GROUP BY Category SELECT Profit = SUM(Income - Expense) ,Margin = SUM(Income - Expense) / SUM(Income) FROM @Profitability Notice how the same formulae have to be used at the different aggregation levels. This results in code duplication. I have thought of using UDFs (either scalar or table valued with an OUTER APPLY, since many of the final results may share intermediates which have to be calculated at the aggregate level), but in my experience the scalar and multi-statement table-valued UDFs perform very poorly. Also thought about using more dynamic SQL and applying the formulas by name, basically. Any other tricks, techniques or tactics to keeping these kinds of formulae which need to be applied at different levels in sync and/or organized?

  Read the article

• How to use Common Table Expression and check no duplication in sqlserver

  - by vodkhang

  I have a table references to itself. User table: id, username, managerid and managerid links back to id Now, I want to get all the managers including direct manager, manager of direct manager, so on and so forth... The problem is that I do not want to have a unstop recursive sql. So, I want to check if an id alreay in a list, I will not include it anymore. Here is my sql for that: with --the relation for all the subparts of ozsolar including itself all_managers (id, username, managerid, idlist) as ( --seed that is the ozsola part select u1.id, u1.username, u1.managerid, ' ' from users u1, users u2 where u1.id = u2.managerid and u2.id = 6 UNION ALL select u.id, u.username, u.managerid, idlist + ' ' + u.id from all_managers a, users u where a.managerid = u.id and charindex(cast(u.id as nvarchar(5)), idlist) != 0 ) --select the total number of subparts and group by subpart select id, username from all_managers; The problem is that in this line: select u1.id, u1.username, u1.managerid, ' ' The sqlserver complains with me that I can not put ' ' as the initialized for idlist. nvarchar(40) does not work as well. I do not know how to declare it inside a common table expression like this one. Usually, in db2, I can just put varchar(40)

  Read the article

• Is there a way to effect user defined data types in MySQL?

  - by Dancrumb

  I have a database which stores (among other things), the following pieces of information: Hardware IDs BIGINTs Storage Capacities BIGINTs Hardware Names VARCHARs World Wide Port Names VARCHARs I'd like to be able to capture a more refined definition of these datatypes. For instance, the hardware IDs have no numerical significance, so I don't care how they are formatted when displayed. The Storage Capacities, however, are cardinal numbers and, at a user's request, I'd like to present them with thousands and decimal separators, e.g. 123,456.789. Thus, I'd like to refine BIGINT into, say ID_NUMBER and CARDINAL. The same with Hardware Names, which are simple text and WWPNs, which are hexstrings, e.g. 24:68:AC:E0. Thus, I'd like to refine VARCHAR into ENGLISH_WORD and HEXSTRING. The specific datatypes I made up are just for illustrative purposes. I'd like to keep all this information in one place and I'm wondering if anybody knows of a good way to hold this all in my MySQL table definitions. I could use the Comment field of the table definition, but that smells fishy to me. One approach would be to define the data structure elsewhere and use that definition to generate my CREATE TABLEs, but that would be a major rework of the code that I currently have, so I'm looking for alternatives. Any suggestions? The application language in use is Perl, if that helps.

  Read the article

< Previous Page | 59 60 61 62 63 64 65 66 67 68 69 70  | Next Page >