Search Results

Search found 10101 results on 405 pages for 'temporary tables'.

Page 351/405 | < Previous Page | 347 348 349 350 351 352 353 354 355 356 357 358  | Next Page >

• Help a CRUD programmer think about an "approval workflow"

  - by gerdemb

  I've been working on a web application that is basically a CRUD application (Create, Read, Update, Delete). Recently, I've started working on what I'm calling an "approval workflow". Basically, a request is generated for a material and then sent for approval to a manager. Depending on what is requested, different people need to approve the request or perhaps send it back to the requester for modification. The approvers need to keep track of what to approve what has been approved and the requesters need to see the status of their requests. As a "CRUD" developer, I'm having a hard-time wrapping my head around how to design this. What database tables should I have? How do I keep track of the state of the request? How should I notify users of actions that have happened to their requests? Is their a design pattern that could help me with this? Should I be drawing state-machines in my code? I think this is a generic programing question, but if it makes any difference I'm using Django with MySQL.

  Read the article

• Type or namespace could not be found

  - by Jason Shultz

  I'm using LINQ to SQL to connect my database to my home page. I created my datacontext (named businessModel.dbml) In it I have two tables named Category and Business. In home controller I reference the model and attempt to return to the view the table: var dataContext = new businessModelDataContext(); var business = from b in dataContext.Businesses select b; ViewData["WelcomeMessage"] = "Welcome to Jerome, Arizona!"; ViewData["MottoMessage"] = "Largest Ghost Town in America!"; return View(business); and in the view I have this: <%@ Import Namespace="WelcomeToJerome.Models" %> and <% foreach (business b in (IEnumerable)ViewData.Model) { %> <li><%= b.Title %></li> <% } %> Yet, in the view business is cursed with the red underline and say's that The type or namespace name 'business' could not be found (are you missing a using directive or an assembly reference?) What am I doing wrong? This has had me stumped all afternoon. link to all the code in pastebin: http://pastebin.com/es4RnS2q

  Read the article

• Wildcards in T-SQL LIKE vs. ASP.net parameters

  - by Vinzcent

  In my SQL statement I use wildcards. But when I try to select something, it never select something. While when I execute the query in Microsoft SQL Server Management Studio, it works fine. What am I doing wrong? Click handler protected void btnTitelAuteur_Click(object sender, EventArgs e) { cvalTitelAuteur.Enabled = true; cvalTitelAuteur.Validate(); if (Page.IsValid) { objdsSelectedBooks.SelectMethod = "getBooksByTitleAuthor"; objdsSelectedBooks.SelectParameters.Clear(); objdsSelectedBooks.SelectParameters.Add(new Parameter("title", DbType.String)); objdsSelectedBooks.SelectParameters.Add(new Parameter("author", DbType.String)); objdsSelectedBooks.Select(); gvSelectedBooks.DataBind(); pnlZoeken.Visible = false; pnlKiezen.Visible = true; } } In my Data Access Layer public static DataTable getBooksByTitleAuthor(string title, string author) { string sql = "SELECT 'AUTHOR' = tblAuthors.FIRSTNAME + ' ' + tblAuthors.LASTNAME, tblBooks.*, tblGenres.GENRE " + "FROM tblAuthors INNER JOIN tblBooks ON tblAuthors.AUTHOR_ID = tblBooks.AUTHOR_ID INNER JOIN tblGenres ON tblBooks.GENRE_ID = tblGenres.GENRE_ID " +"WHERE (tblBooks.TITLE LIKE '%@title%');"; SqlDataAdapter da = new SqlDataAdapter(sql, GetConnectionString()); da.SelectCommand.Parameters.Add("@title", SqlDbType.Text); da.SelectCommand.Parameters["@title"].Value = title; DataSet ds = new DataSet(); da.Fill(ds, "Books"); return ds.Tables["Books"]; }

  Read the article

• Use Django ORM as standalone [closed]

  - by KeyboardInterrupt

  Possible Duplicates: Use only some parts of Django? Using only the DB part of Django I want to use the Django ORM as standalone. Despite an hour of searching Google, I'm still left with several questions: Does it require me to set up my Python project with a setting.py, /myApp/ directory, and modules.py file? Can I create a new models.py and run syncdb to have it automatically setup the tables and relationships or can I only use models from existing Django projects? There seems to be a lot of questions regarding PYTHONPATH. If you're not calling existing models is this needed? I guess the easiest thing would be for someone to just post a basic template or walkthrough of the process, clarifying the organization of the files e.g.: db/ __init__.py settings.py myScript.py orm/ __init__.py models.py And the basic essentials: # settings.py from django.conf import settings settings.configure( DATABASE_ENGINE = "postgresql_psycopg2", DATABASE_HOST = "localhost", DATABASE_NAME = "dbName", DATABASE_USER = "user", DATABASE_PASSWORD = "pass", DATABASE_PORT = "5432" ) # orm/models.py # ... # myScript.py # import models.. And whether you need to run something like: django-admin.py inspectdb ... (Oh, I'm running Windows if that changes anything regarding command-line arguments.).

  Read the article

• How can I automatically generate sql update scripts when some data is updated ?

  - by Brann

  I'd like to automatically generate an update script each time a value is modified in my database. In other words, if a stored procedure, or a query, or whatever updates column a with value b in table c (which as a pk column (i,j...k), I want to generate this : update c set a=b where i=... and j=... and k=... and store it somewhere (for example as a raw string in a table). To complicate things, I want the script to be generated only if the update has been made by a specific user. Good news is that I've got a primary key defined for all my tables. I can see how to do this using a trigger, but I would need to generate specific triggers for each table, and to update them each and every-time my schema changes. I guess there are some built-in ways to do this as SQL server sometimes need to store this kind of things (while using transactional replication for example), but couldn't find anything so far ... any ideas ? I'm also interested in ways to automatically generate triggers (probably using triggers - meta triggers, huh? - since I will need to update triggers automatically when the schema change)

  Read the article

• ASP.Net forms authentication - multiple providers

  - by Chris Klepeis

  I have an ASP.Net 4.0 application, and within it is a folder called "Forum", setup as a sub application in IIS 7. This forum package implements a custom provider for .net membership. The forum is running in .net 3.5. I'd like to setup the main site so that when users login, it logs them into both my site and the forum site. Both the main site and the forum have separate .Net membership tables. How can I specify which provider to use with formsauthentication? right now I have FormsAuthentication.SetAuthCookie(...); this, however, just uses my default provider and does nothing with the provider for the forum I tried setting the forum app and my web app to have the same cookie name, as well as setting the machinekey on each: <machineKey validationKey="AutoGenerate" validation="SHA1" /> no dice. I googled and didnt really come up with any example of how to use multiple providers like I want to. I updated my web.config to have both provideers but this is useless if I cannot specify in my code which one to use.

  Read the article

• Nhibernate: Stop it from joining to a table that is not needed

  - by Aaron

  I have two tables (tbArea, tbPost) that relate to the following classes. class Area { int ID string Name ... } class Post { int ID string Title Area Area ... } These two classes map up with Fluent Nhibernate. Below is the post mapping. public class PostMapping : ClassMap<Post> { public PostMapping() { Cache.NonStrictReadWrite(); this.Table("tbPost"); Id(x => x.ID) .Column("PostID") .GeneratedBy .Identity(); References(x => x.Area) .ForeignKey("AreaID") .Column("AreaID"); ... } } Any time I perform a query on the Post table "where AreaID = 1(any AreaId)", nhibernate will join to the area table. (What Nhibernate generates for a query) SELECT post fields , area fields (automatically added) FROM tbPost p LEFT JOIN tbArea a on p.areaid = a.areaid where p.areaid = 1 I have tried setting Area to LazyLoad, to Fetch.Select, ReadOnly, and any other setting on the reference and still it will always join to Area. I am trying to optimize the backend database queries, and since I don't need the area object loaded just filtered I would like to eliminate the unnecessary join to Area each time I Query post. What configurations do I need to change or mappings to get area to still be related to post in my objects, but not query it when I filter on AreaID?

  Read the article

• database structure

  - by jindalsyogesh

  I have a table named ActivityRecording. This table currently has 500,000 records. I need to add a lot of new inputs that relates to activityrecording table. The relation of activityrecording with these new input fields is 1 to 0,1. So, what's going to happen on screen is when user fills the ActivityRecording data, he will then be taken to a new page and this page will show a form based on the user's input (from a dropdown named service) in activityrecording. There will 6 different kinds of form (each form will have 7-8 inputs which includes textareas of size 5kb, textboxes and checkboxes). So, for one activityrecording user will fill one out of 6 forms. There are two ways I know (there could be more), I can design the data structure: Add all the inputs from all these 6 forms into the activityrecording table. So, columns belonging to 5 of these forms will be null in this table, only columns belonging to one of the forms will have values The other way would be add 6 new tables (one for each form) and add 6 foreign key columns to activityrecording table. So, out of 6 foreign keys, 5 will be null and one will actually point to a table Which approach is a better data structure design? Please take into consideration that number of rows in this table are 500,000 and are expected to grow at a faster rate now.

  Read the article

• Do you have health checks in your web app or web site?

  - by Pekka

  I have built PHP based "health check" scripts for several projects, but they were always custom-made for the occasion and not written for abstraction as an independent product. I would like to know whether such a solution exists. What I meam by "health check" is a protected web page that functions much like a suite of unit tests, but on a more operational level, showing red/yellow/green statuses for things like Are the cache directories writable? Is the PHP version correct, are required extensions installed? Is the configuration file protected from writing? Is the database server reachable? Do the key tables exist in the database? Is there enough disk space available? Is the site's front page reachable and renders fully ( = no PHP errors)? Do the project's libraries' MD5 checksums match the original ones? Do you do this - or parts of it - in your applications and web sites? Are there any standardized tools for this that bring along all the functionality to perform the tests (ideally as plugins), and just need to be configured accordingly? Is there a way to set this up using one of the Unit Testing frameworks available for PHP (preferably PHPUnit)? If so, do you know any resources / tutorials outlining how?

  Read the article

• Process-to-port mapping with SNMP and/or wmi/wmic in java

  - by Niddy888

  I'm trying to use SNMP to map outgoing ports on my host computer with the application running on the computer that is responsible for that communication. When running "netstat -ano" I get access to Protocol, Local Address (with port), Foreign Address (with port), State and PID. But I want to do this entirely without having to execute "cmd" from Java. By using SNMP OID: .1.3.6.1.2.1.25.4 (.iso.org.dod.internet.mgmt.mib-2.host.hrSWRun) I get access to PID (ex. 1704), Name (ex. cmd.exe), Path (ex. C:\Windows\system32) among others. There is an SNMP OID: .1.3.6.1.2.1.6.13 (.iso.org.dod.internet.mgmt.mib-2.tcp.tcpConnTable) that give you access to TCP connection state, local address, local port, remote address, remote port. But NO PID. So to sum up. My question again: Is there a way to "map" these tables together? Either directly in SNMP with other OID's or in conjunction with WMI / WMIC?

  Read the article

• Lookup table size reduction

  - by Ryan

  Hello: I have an application in which I have to store a couple of millions of integers, I have to store them in a Look up table, obviously I cannot store such amount of data in memory and in my requirements I am very limited I have to store the data in an embebedded system so I am very limited in the space, so I would like to ask you about recommended methods that I can use for the reduction of the look up table. I cannot use function approximation such as neural networks, the values needs to be in a table. The range of the integers is not known at the moment. When I say integers I mean a 32 bit value. Basically the idea is use some copmpression method to reduce the amount of memory but without losing many precision. This thing needs to run in hardware so the computation overhead cannot be very high. In my algorithm I have to access to one value of the table do some operations with it and after update the value. In the end what I should have is a function which I pass an index to it and then I get a value, and after I have to use another function to write a value in the table. I found one called tile coding http://www.cs.ualberta.ca/~sutton/book/8/node6.html, this one is based on several look up tables, does anyone know any other method?. Thanks.

  Read the article

• Reading/Writing DataTables to and from an OleDb Database LINQ

  - by jsmith

  My current project is to take information from an OleDbDatabase and .CSV files and place it all into a larger OleDbDatabase. I have currently read in all the information I need from both .CSV files, and the OleDbDatabase into DataTables.... Where it is getting hairy is writing all of the information back to another OleDbDatabase. Right now my current method is to do something like this: OleDbTransaction myTransaction = null; try { OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Database); conn.Open(); OleDbCommand command = conn.CreateCommand(); string strSQL; command.Transaction = myTransaction; strSQL = "Insert into TABLE " + "(FirstName, LastName) values ('" + FirstName + "', '" + LastName + "')"; command.CommandType = CommandType.Text; command.CommandText = strSQL; command.ExecuteNonQuery(); conn.close(); catch (Exception) { // IF invalid data is entered, rolls back the database myTransaction.Rollback(); } Of course, this is very basic and I'm using an SQL command to commit my transactions to a connection. My problem is I could do this, but I have about 200 fields that need inserted over several tables. I'm willing to do the leg work if that's the only way to go. But I feel like there is an easier method. Is there anything in LINQ that could help me out with this?

  Read the article

• Problem importing Oracle .dmp file

  - by BitFiddler

  So I have looked at all the suggested ways of importing .dmp files and non of them seem to answer this question: where does the data go once you import it? Context: I created a user like so: SQL> create user IMPORTER identified by "12345"; SQL> grant connect, unlimited tablespace, resource to IMPORTER; I then ran the 'imp' command as follows: C:\>imp system/password FROMUSER=OVIEDOE TOUSER=IMPORTER file=c:\database1.dmp Now there were 9 .dmp files, after each one it asked me for the next one and then I received the message "Import terminated successfully with warnings." The warning was: Warning: the objects were exported by OVIEDOE, not by you import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set export client uses WE8ISO8859P1 character set (possible charset conversion) IMP-00046: using FILESIZE value from export file of 2147483648 Now it says it was terminated successfully so my assumption (I am new to oracle so this may be wrong) is that the data was loaded. However, when I use SQL developer to connect to the database and look under the 'tables' node under the IMPORTER user, there is nothing there. What is going on? Did the data load? If so, where can I find it?

  Read the article

• best practice - loging events (general) and changes (database)

  - by b0x0rz

  need help with logging all activities on a site as well as database changes. requirements: * should be in database * should be easily searchable by initiator (user name / session id), event (activity type) and event parameters i can think of a database design but either it involves a lot of tables (one per event) so i can log each of the parameters of an event in a separate field OR it involves one table with generic fields (7 int numeric and 7 text types) and log everything in one table with event type field determining what parameter got written where (and hoping that i don't need more than 7 fields of a certain type, or 8 or 9 or whatever number i choose)... example of entries (the usual things): [username] login failed @datetime [username] login successful @datetime [username] changed password @datetime, estimated security of password [low/ok/high/perfect] @datetime [username] clicked result [result number] [result id] after searching for [search string] and got [number of results] @datetime [username] clicked result [result number] [result id] after searching for [search string] and got [number of results] @datetime [username] changed profile name from [old name] to [new name] @datetime [username] verified name with [credit card type] credit card @datetime datbase table [table name] purged of old entries @datetime etc... so anyone dealt with this before? any best practices / links you can share? i've seen it done with the generic solution mentioned above, but somehow that goes against what i learned from database design, but as you can see the sheer number of events that need to be trackable (each user will be able to see this info) is giving me headaches, BUT i do LOVE the one event per table solution more than the generic one. any thoughts? edit: also, is there maybe an authoritative list of such (likely) events somewhere? thnx stack overflow says: the question you're asking appears subjective and is likely to be closed. my answer: probably is subjective, but it is directly related to my issue i have with designing a database / writing my code, so i'd welcome any help. also i tried narrowing down the ideas to 2 so hopefully one of these will prevail, unless there already is an established solution for these kinds of things.

  Read the article

• ASP.net MVC Linq-To-SQL Many-To-Many Field Binding

  - by user336858

  Hi there, The short version of this question is "Is there a way to gracefully handle database insertion for an object that has a many-to-many field that has been set up in a partial class?" Apologies if it's been asked before. Example Suppose I have a typical MVC setup with the tables: Posts {PostID, ...} Categories {CategoryID, ...} A post can have more than one category, and a category can identify more than one post. Thus suppose further that I need an extra table: PostCategories {PostID, CategoryID, ...} This handles the many-to-many relationship between posts and categories. As far as I know, there's no way to do this in Linq-to-SQL right now so I have to shoehorn it in by adding a partial Post class to the project to add that functionality. Something like: public partial class Post { public IEnumerable<Category> Categories{ get { ... } set { ... } } } So I can now create a "Create" view that automatically populates a "Categories" UI item. This is where the trouble starts. So here's my question: How do you get automatic object model binding to work cleanly with an object that has a many-to-many relationship to control? The workaround that makes many-to-many relationships possible relies on the Post object having a PostID in order to be associated with CategoryID(s), which is only issued after the Post object has been submitted for validation and insertion. Bit of a Catch22 here. Any terminology, links, or tips you can provide would be tremendously helpful!

  Read the article

• Yii CGridView and dropdown filter

  - by Dmitriy Gunchenko

  I created dropdown filter, it's display, but don't worked right. As I anderstand trouble in search() method view: $this->widget('zii.widgets.grid.CGridView', array( 'dataProvider'=>$model->search(), 'filter' => $model, 'columns'=>array( array( 'name' => 'client_id', 'filter' => CHtml::listData(Client::model()->findAll(), 'client_id', 'name'), 'value'=>'$data->client->name' ), 'task' ) )); I have to tables, and they relations are shown down model: public function relations() { // NOTE: you may need to adjust the relation name and the related // class name for the relations automatically generated below. return array( 'client' => array(self::BELONGS_TO, 'Client', 'client_id'), ); } public function search() { // Warning: Please modify the following code to remove attributes that // should not be searched. $criteria=new CDbCriteria; $criteria->with = array('client'); $criteria->compare('task_id',$this->task_id); $criteria->compare('client_id',$this->client_id); $criteria->compare('name',$this->client->name); $criteria->compare('task',$this->task,true); $criteria->compare('start_date',$this->start_date,true); $criteria->compare('end_date',$this->end_date,true); $criteria->compare('complete',$this->complete); return new CActiveDataProvider($this, array( 'criteria'=>$criteria, )); }

  Read the article

• Multiple inequality conditions (range queries) in NoSQL

  - by pableu

  Hi, I have an application where I'd like to use a NoSQL database, but I still want to do range queries over two different properties, for example select all entries between times T1 and T2 where the noiselevel is smaller than X. On the other hand, I would like to use a NoSQL/Key-Value store because my data is very sparse and diverse, and I do not want to create new tables for every new datatype that I might come across. I know that you cannot use multiple inequality filters for the Google Datastore (source). I also know that this feature is coming (according to this). I know that this is also not possible in CouchDB (source). I think I also more or less understand why this is the case. Now, this makes me wonder.. Is that the case with all NoSQL databases? Can other NoSQL systems make range queries over two different properties? How about, for example, Mongo DB? I've looked in the Documentation, but the only thing I've found was the following snippet in their docu: Note that any of the operators on this page can be combined in the same query document. For example, to find all document where j is not equal to 3 and k is greater than 10, you'd query like so: db.things.find({j: {$ne: 3}, k: {$gt: 10} }); So they use greater-than and not-equal on two different properties. They don't say anything about two inequalities ;-) Any input and enlightenment is welcome :-)

  Read the article

• Oracle - UPSERT with update not executed for unmodified values

  - by Buthrakaur

  I'm using following update or insert Oracle statement at the moment: BEGIN UPDATE DSMS SET SURNAME = :SURNAME, FIRSTNAME = :FIRSTNAME, VALID = :VALID WHERE DSM = :DSM; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO DSMS (DSM, SURNAME, FIRSTNAME, VALID) VALUES (:DSM, :SURNAME, :FIRSTNAME, :VALID); END IF; END; This runs fine except that the update statement performs dummy update if the data is same as the parameter values provided. I would not mind the dummy update in normal situation, but there's a replication/synchronization system build over this table using triggers on tables to capture updated records and executing this statement frequently for many records simply means that I'd cause huge traffic in triggers and the sync system. Is there any simple method how to reformulate this code that the update statement wouldn't update record if not necessary without using following IF-EXISTS check code which I find not sleek enough and maybe also not most efficient for this task? DECLARE CNT NUMBER; BEGIN SELECT COUNT(1) INTO CNT FROM DSMS WHERE DSM = :DSM; IF SQL%FOUND THEN UPDATE DSMS SET SURNAME = :SURNAME, FIRSTNAME = :FIRSTNAME, VALID = :VALID WHERE DSM = :DSM AND (SURNAME != :SURNAME OR FIRSTNAME != :FIRSTNAME OR VALID != :VALID); ELSE INSERT INTO DSMS (DSM, SURNAME, FIRSTNAME, VALID) VALUES (:DSM, :SURNAME, :FIRSTNAME, :VALID); END IF; END;

  Read the article

• Oracle ODBC x64 - getting 0 when selecting a number(9) column

  - by MatsL

  I'm having a really weird problem with a third party web service that uses an ODBC connection to Oracle 10.2.0.3.0. I've written a .NET client that generates the same SQL as the web service so I can find out what's going on. The web service is hosted by IIS 6 that's in x64 mode so we use Oracle x64 client. The oracle client version is 10.2.0.1.0. I have a table that looks like this (I've removed some columns and names): SQL> describe tablename; Name Null? Type ----------------------------------------- -------- ---------------------------- KOD VARCHAR2(30) ORDNING NUMBER(5) AVGIFT NUMBER(9) I then in SQL*Plus issue the following statement: SELECT KOD as kod, AVGIFT as riskPoang FROM tablename Where upper(KODTYP) = 'OBJLIVSV_RISKVERKSAMTYP' ORDER BY ORDNING And I get the following result: KOD RISKPOANG ------------------------------ ---------- Hög risk 55 Mellan risk 35 Låg risk 15 Mycket låg risk 5 But when I execute the exact same SQL using the same DSN on the same machine I get this: Values Kod: Hög risk RiskPoäng: 0 Kod: Mellan risk RiskPoäng: 0 Kod: Låg risk RiskPoäng: 0 Kod: Mycket låg risk RiskPoäng: 0 If I first cast the number to varchar and then back again to number, like this: SELECT KOD as kod, to_number(to_char(AVGIFT, '99'), '9999999999') as riskPoang FROM tablename Where upper(KODTYP) = 'OBJLIVSV_RISKVERKSAMTYP' ORDER BY ORDNING I get the correct result: Values Kod: Hög risk RiskPoäng: 55 Kod: Mellan risk RiskPoäng: 35 Kod: Låg risk RiskPoäng: 15 Kod: Mycket låg risk RiskPoäng: 5 Has anyone else experiences this? It's incredibly annoying and I'm completely stuck and not sure what to do next. We have a third party web service that use these tables so I must get the original SQL-statement to work since I can't modify its code. And pointers are greatly appreciated! :-) Best regards, Mats

  Read the article

• Trouble creating a SQL query

  - by JoBu1324

  I've been thinking about how to compose this SQL query for a while now, but after thinking about it for a few hours I thought I'd ask the SO community to see if they have any ideas. Here is a mock up of the relevant portion of the tables: contracts id date ar (yes/no) term payments contract_id payment_date The object of the query is to determine, per month, how many payments we expect, vs how many payments we received. conditions for expecting a payment Expected payments begin on contracts.term months after contracts.date, if contracts.ar is "yes". Payments continue to be expected until the month after the first missed payment. There is one other complication to this: payments might be late, but they need to show up as if they were paid on the date expected. The data is all there, but I've been having trouble wrapping my head around the SQL query. I am not an SQL guru - I merely have a decent amount of experience handling simpler queries. I'd like to avoid filtering the results in code, if possible - but without your help that may be what I have to do. Expected Output Month Expected Payments Received Payments January 500 450 February 498 478 March 234 211 April 987 789 ... SQL Fiddle I've created an SQL Fiddle: http://sqlfiddle.com/#!2/a2c3f/2

  Read the article

• How to Specify Columntype in fluent nHibernate?

  - by Bipul

  I have a class CaptionItem public class CaptionItem { public virtual int SystemId { get; set; } public virtual int Version { get; set; } protected internal virtual IDictionary<string, string> CaptionValues {get; private set;} } I am using following code for nHibernate mapping Id(x => x.SystemId); Version(x => x.Version); Cache.ReadWrite().IncludeAll(); HasMany(x => x.CaptionValues) .KeyColumn("CaptionItem_Id") .AsMap<string>(idx => idx.Column("CaptionSet_Name"), elem => elem.Column("Text")) .Not.LazyLoad() .Cascade.Delete() .Table("CaptionValue") .Cache.ReadWrite().IncludeAll(); So in database two tables get created. One CaptionValue and other CaptionItem. In CaptionItem table has three columns 1. CaptionItem_Id int 2. Text nvarchar(255) 3. CaptionSet_Name nvarchar(255) Now, my question is how can I make the columnt type of Text to nvarchar(max)? Thanks in advance.

  Read the article

• World's Most Challening MySQL SQL Query (least I think so...)

  - by keruilin

  Whoever answers this question can claim credit for solving the world's most challenging SQL query, according to yours truly. Working with 3 tables: users, badges, awards. Relationships: user has many awards; award belongs to user; badge has many awards; award belongs to badge. So badge_id and user_id are foreign keys in the awards table. The business logic at work here is that every time a user wins a badge, he/she receives it as an award. A user can be awarded the same badge multiple times. Each badge is assigned a designated point value (point_value is a field in the badges table). For example, BadgeA can be worth 500 Points, BadgeB 1000 Points, and so on. As further example, let's say UserX won BadgeA 10 times and BadgeB 5 times. BadgeA being worth 500 Points, and BadgeB being worth 1000 Points, UserX has accumulated a total of 10,000 Points ((10 x 500) + (5 x 1000)). The end game here is to return a list of top 50 users who have accumulated the most badge points. Can you do it?

  Read the article

• MySQL db Audit Trail Trigger

  - by Natkeeran

  I need to track changes (audit trail) in certain tables in a MySql Db. I am trying to implement the solution suggested here. I have an AuditLog Table with the following columns: AuditLogID, TableName, RowPK, FieldName, OldValue, NewValue, TimeStamp. The mysql stored procedure is the following (this executes fine, and creates the procedure): The call to the procedure such as: CALL addLogTrigger('ProductTypes', 'ProductTypeID'); executes, but does not create any triggers (see the image). SHOW TRIGGERS returns empty set. Please let me know what could be the issue, or an alternate way to implement this. DROP PROCEDURE IF EXISTS addLogTrigger; DELIMITER $ CREATE PROCEDURE addLogTrigger(IN tableName VARCHAR(255), IN pkField VARCHAR(255)) BEGIN SELECT CONCAT( 'DELIMITER $\n', 'CREATE TRIGGER ', tableName, '_AU AFTER UPDATE ON ', tableName, ' FOR EACH ROW BEGIN ', GROUP_CONCAT( CONCAT( 'IF NOT( OLD.', column_name, ' <=> NEW.', column_name, ') THEN INSERT INTO AuditLog (', 'TableName, ', 'RowPK, ', 'FieldName, ', 'OldValue, ', 'NewValue' ') VALUES ( ''', table_name, ''', NEW.', pkField, ', ''', column_name, ''', OLD.', column_name, ', NEW.', column_name, '); END IF;' ) SEPARATOR ' ' ), ' END;$' ) FROM information_schema.columns WHERE table_schema = database() AND table_name = tableName; END$ DELIMITER ;

  Read the article

• How many users are sufficient to make a heavy load for web application

  - by galymzhan

  I have a web application, which has been suffering high load recent days. The application runs on single server which has 8-core Intel CPU and 4gb of RAM. Software: Drupal 5 (Apache 2, PHP5, MySQL5) running on Debian. After reaching 500 authenticated and 200 anonymous users (simultaneous), the application drastically decreases its performance up to total failure. The biggest load comes from authenticated users, who perform activities, causing insert/update/deletes on db. I think mysql is a bottleneck. Is it normal to slow down on such number of users? EDIT: I forgot to mention that I did some kind of profiling. I runned commands top, htop and they showed me that all memory was being used by MySQL! After some time MySQL starts to perform terribly slow, site goes down, and we have to restart/stop apache to reduce load. Administrators said that there was about 200 active mysql connections at that moment. The worst point is that we need to solve this ASAP, I can't do deep profiling analysis/code refactoring, so I'm considering 2 ways: my tables are MyIsam, I heard they use table-level locking which is very slow, is it right? could I change it to Innodb without worry? what if I take MySQL, and move it to dedicated machine with a lot of RAM?

  Read the article

• How can I make this SQL query more efficient? PHP.

  - by Alan Grant

  Hi all, I have a system whereby a user can view categories that they've subscribed to individually, and also those that are available in the region they belong in by default. So, the tables are as follows: Categories UsersCategories RegionsCategories I'm querying the db for all the categories within their region, and also all the individual categories that they've subscribed to. My query is as follows: Select * FROM (categories c) LEFT JOIN users_categories uc on uc.category_id = c.id LEFT JOIN regions_categories rc on rc.category_id = c.id WHERE (rc.region_id = ? OR uc.user_id = ?) At least I believe that's the query, I'm creating it using Cake's ORM layer, so the exact one is: $conditions = array( array( "OR" => array ( 'RegionsCategories.region_id' => $region_id, 'UsersCategories.user_id' => $user_id ) )); $this->find('all', $conditions); This turns out to be incredibly slow (sometimes around 20 seconds or so. Each table has around 5,000 rows). Is my design at fault here? How can I retrieve both the users' individual categories and those within their region all in one query without it taking ages? Thanks!

  Read the article

< Previous Page | 347 348 349 350 351 352 353 354 355 356 357 358  | Next Page >