Search Results

Search found 35019 results on 1401 pages for 'sql documentation'.

Page 654/1401 | < Previous Page | 650 651 652 653 654 655 656 657 658 659 660 661  | Next Page >

  • CakePHP adding columns to a table

    - by vette982
    I have a Profile model/controller in my cake app as well as an index.ctp view in /views/profiles. Now, when I go to add a column to my table that is already filled with data, and then add the corresponding code to the view to pick up this column's data, it just gives me an empty result. My model: <?php class Profile extends AppModel { var $name = 'Profile'; } ?> My controller: <?php class ProfilesController extends AppController { var $name = 'Profiles'; function index() { $this->set('profiles', $this->Profile->find('all')); } } ?> My views printing (stripped down): <?php foreach ($profiles as $profile): ?> <?php echo $profile['Profile']['id']; ?> <?php echo $profile['Profile']['username']; ?> <?php echo $profile['Profile']['created']; ?> <?php echo $profile['Profile']['thumbnail'];?> <?php echo $profile['Profile']['account'];?> <?php endforeach; ?> Basically, the columns id, username, column, thumbnail always have been printing fine, but when I add a column called accountit returns no information (nothing prints, but no errors). Any suggestions?

    Read the article

  • Inserting variables into a query string - it won't work!

    - by Jonesy
    Basically i have a query string that when i hardcode in the catalogue value its fine. when I try adding it via a variable it just doesn't pick it up. This works: Dim WaspConnection As New SqlConnection("Data Source=JURA;Initial Catalog=WaspTrackAsset_NROI;User id=" & ConfigurationManager.AppSettings("WASPDBUserName") & ";Password='" & ConfigurationManager.AppSettings("WASPDBPassword").ToString & "';") This doesn't: Public Sub GetWASPAcr() connection.Open() Dim dt As New DataTable() Dim username As String = HttpContext.Current.User.Identity.Name Dim sqlCmd As New SqlCommand("SELECT WASPDatabase FROM dbo.aspnet_Users WHERE UserName = '" & username & "'", connection) Dim sqlDa As New SqlDataAdapter(sqlCmd) sqlDa.Fill(dt) If dt.Rows.Count > 0 Then For i As Integer = 0 To dt.Rows.Count - 1 If dt.Rows(i)("WASPDatabase") Is DBNull.Value Then WASP = "" Else WASP = "WaspTrackAsset_" + dt.Rows(i)("WASPDatabase") End If Next End If connection.Close() End Sub Dim WaspConnection As New SqlConnection("Data Source=JURA;Initial Catalog=" & WASP & ";User id=" & ConfigurationManager.AppSettings("WASPDBUserName") & ";Password='" & ConfigurationManager.AppSettings("WASPDBPassword").ToString & "';") When I debug the catalog is empty in the query string but the WASP variable holds the value "WaspTrackAsset_NROI" Any idea's why? Cheers, jonesy

    Read the article

  • What is wrong with this connection string?

    - by Hakan
    Can any one help me with this connection string. I can't manage how to fix. Dim constring As String Dim con As SqlCeConnection Dim cmd As SqlCeCommand constring = "(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + \\database.sdf;Password=pswrd;File Mode=shared read" con = New SqlCeConnection() con.Open() Thanks

    Read the article

  • Select a row having a column with max value - On a date range

    - by Abhi
    Excuse me for posting a similar question. Please consider this: date value 18/5/2010, 1 pm 40 18/5/2010, 2 pm 20 18/5/2010, 3 pm 60 18/5/2010, 4 pm 30 18/5/2010, 5 pm 60 18/5/2010, 6 pm 25 19/5/2010, 6 pm 300 19/5/2010, 6 pm 450 19/5/2010, 6 pm 375 20/5/2010, 6 pm 250 20/5/2010, 6 pm 310 The query is to get the date and value for each day such that the value obtained for that day is max. If the max value is repeated on that day, the lowest time stamp is selected. The result should be like: 18/5/2010, 3 pm 60 19/5/2010, 6 pm 450 20/5/2010, 6 pm 310 The query should take in a date range like the one given below and find results for that range in the above fashion: where date = to_date('26/03/2010','DD/MM/YYYY') AND date < to_date('27/03/2010','DD/MM/YYYY')

    Read the article

  • Optimizing MySQL statement with lot of count(row) an sum(row+row2)...

    - by Zombies
    I need to use InnoDB storage engine on a table with about 1mil or so records in it at any given time. It has records being inserted to it at a very fast rate, which are then dropped within a few days, maybe a week. The ping table has about a million rows, whereas the website table only about 10,000. My statement is this: select url from website ws, ping pi where ws.idproxy = pi.idproxy and pi.entrytime > curdate() - 3 and contentping+tcpping is not null group by url having sum(contentping+tcpping)/(count(*)-count(errortype)) < 500 and count(*) > 3 and count(errortype)/count(*) < .15 order by sum(contentping+tcpping)/(count(*)-count(errortype)) asc; I added an index on entrytime, yet no dice. Can anyone throw me a bone as to what I should consider to look into for basic optimization of this query. The result set is only like 200 rows, so I'm not getting killed there.

    Read the article

  • Storing users in a database

    - by EMcKenna
    Im wondering whats the best way of storing different types of users in my database. I am writing an application that has 4 main user types (admin, school, teacher, student). At the moment I have a table for each of these but i'm not sure thats the best way of storing user information. For instance... Allowing students to PM other student is simple (store sender and receiver student_id) but enabling teachers to PM students requires another table (sender teacher_id, sender student_id). Should all users be stored in one users table with a user_type field? If so, the teacher / student specific information will still have to be stored in another table. users user_id, password_hash, user_type students user_id, student_specific_stuff... teachers user_id, teacher_specific_stuff... How do I stop a user who has a user_type = student from being accidentally being entered into the teachers table (as both have a user_id) Just want to make sure I get the database correct before i go any further. Thanks...

    Read the article

  • triggers for generating userID based on user's information.

    - by nectar
    I have complaint table 1. tblProfile with columns userId | name | age | address | mobileno | 2. tblUserId with columns userId | role | status now when user fills the form I want to insert one row in tblProfile, before inserting a new row I want to create userId by combining starting letters of name and mobile no and then insert into tblprofile with userId after this I want to insert that UserId into tblUserId table. for this I have to use two triggers one is before insert trigger and another is after insert trigger.but I dont know how to capture user information to create userId and how to pass that Id to second trigger.

    Read the article

  • How can I combine result and subquery for IN comparison (mysql)

    - by user325804
    In order for a school project i need to create the following situation within one mysql query. The situation is as such, that a child's tags and a parent's tags need to be combined into one, and compared to a site's tags, depending on a few extra simple equals to lines. For this to happen I only see the option that the result of a subquery is combined with a sub query within that query, as such: SELECT tag.*, (SELECT group_concat(t1.id, ',', (SELECT group_concat(tag.id) FROM adcampaign INNER JOIN adcampaign_tag ON adcampaign.id = adcampaign_tag.adcampaign_id INNER JOIN tag ON adcampaign_tag.tag_id = tag.id WHERE adcampaign.id = 1)) FROM ad, ad_tag, tag AS t1 WHERE ad.id = ad_tag.ad_id AND ad_tag.tag_id = t1.id AND ad.adcampaign_id = 1 AND ad.agecategory_id = 1 AND ad.adsize_id = 1 AND ad.adtype_id = 1) as tags FROM tag WHERE tag.id IN tags But the IN comparison only returns the first result because now the tags aren't a list but a concanated string. Anyone got any suggestion on this? I really need a way to combine it into one array

    Read the article

  • Forum board example schema in YAML format - modify for Nested set?

    - by takeshin
    I have created a forum board app, based on YAML schema found in 'real world examples' of Doctrine Manual, which looks similar to this: --- Forum_Category: columns: root_category_id: integer(10) parent_category_id: integer(10) name: string(50) description: string(99999) relations: Subcategory: class: Forum_Category local: parent_category_id foreign: id Rootcategory: class: Forum_Category local: root_category_id foreign: id Forum_Board: columns: category_id: integer(10) name: string(100) description: string(5000) relations: Category: class: Forum_Category local: category_id foreign: id Threads: class: Forum_Thread local: id foreign: board_id Forum_Entry: columns: author: string(50) topic: string(100) message: string(99999) parent_entry_id: integer(10) thread_id: integer(10) date: integer(10) relations: Parent: class: Forum_Entry local: parent_entry_id foreign: id Thread: class: Forum_Thread local: thread_id foreign: id Forum_Thread: columns: board_id: integer(10) updated: integer(10) closed: integer(1) relations: Board: class: Forum_Board local: board_id foreign: id Entries: class: Forum_Entry local: id foreign: thread_id How to modify this schema, to use NestedSet (Tree structure of threads and entries)?

    Read the article

  • MySQL Count If using 4 tables or Perl

    - by user1726133
    Hi I have a relatively convoluted query that relies on 4 different tables, unfortunately I do not have control of this data, but I do have to query it. I ran this simpler query and it works using just table 1 and table 2 SELECT actor, receiver, count(IF(t2.group1 = "anxiety behavior", 1,0)) AS 'anxiety' FROM ethogram_edited_obs_behaviors t1 JOIN ethogram_behaviors t2 on t1.behavior = t2.behavior_code GROUP BY actor; Below are the 4 tables I need and the query I tried that didn't work Table 1 | Table 2 | Table 3 | Table 4 Actor | Behavior | Behavior | type of Behavior | subject | sex | subject |subject_code er frown | frown anxiety behavior | Eric M | Eric | er Here is the query that is failing SELECT actor, count(IF(t2.group1 = "anxiety behavior", 1,0) AND(t3.sex = "M", 1,0)) AS 'anxiety', FROM ethogram_edited_obs_behaviors t1 JOIN ethogram_behaviors t2 on t1.behavior = t2.behavior_code JOIN subject_code t3 on t1.actor = t3.behavior_code1 JOIN subjects t4 on t3.subject = t4.yerkes_code GROUP BY actor; Any help would be much appreciated!! Thanks :) P.S. if this is easier to do in Perl tips also much appreciated

    Read the article

  • mysqli_stmt_bind_param SQL Injection

    - by profitphp
    Is there still an injection risk when using prepared statements and mysqli_stmt_bind_param? For example: $malicious_input = 'bob"; drop table users'; mysqli_stmt_bind_param($stmt, 's', $malicious_input); Behind the scenes does mysqli_stmt_bind_param pass this query string to mysql: SET @username = "bob"; drop table users"; Or does it perform the SET command through the API, or use some type of protection to keep this from happening?

    Read the article

  • Reuse select query in a procedure in Oracle

    - by Jer
    How would I store the result of a select statement so I can reuse the results with an in clause for other queries? Here's some pseudo code: declare ids <type?>; begin ids := select id from table_with_ids; select * from table1 where id in (ids); select * from table2 where id in (ids); end; ... or will the optimizer do this for me if I simply put the sub-query in both select statements?

    Read the article

  • Error in computed Field of select Query

    - by Shehzad Bilal
    This Query is giving me an error of #1054 - Unknown column 'totalamount' in 'where clause' SELECT (amount1 + amount2) as totalamount FROM `Donation` WHERE totalamount > 1000 I know i can resolve this error by using group by clause and replace my where condition with having clause. But is there any other solution beside using having clause. If group by is the only solution then I want to know why I have to use group by clause even I havent use any aggregate function thanks.

    Read the article

  • PHP 'smart' search engine to search Mysql tables advice

    - by Anonymous12345
    I am creating a search engine for my php based website. I need to search a mysql table. Thing is, the search engine must be pretty 'smart', so that users can easily find their items (it's a classifieds website). I have currently set up a FULLTEXT search with this piece of code: MATCH (headline) AGAINST ($querystring) But this isn't enough... For instance, lets say the field headline contains something like Bmw 330ci. If I search for 330, I wont get any results. The ending ('ci') is just one of many endings in car models which must be taken into account when searching the table. Or what if the headline field is bmw330? Also no results, because it only matches full words. Or also, what if the headline is bmw 330, and I search for bmw 520, still with FULLTEXT I will get the bmw 330 as a result, even though I searched for bmw 520... Not good! How should I solve this problem?

    Read the article

  • Search a string to find which records in table are inside said string

    - by Improfane
    Hello, Say I have a string. Then I have a number of unique tokens or keywords, potentially a large number in a database. I want to search and find out which of these database strings are inside the string I provide (and get the IDs of them). Is there a way of using a query to search the provided string or must it be taken to application space? Am I right in thinking that this is not a 'full text search'? Would the best method be to insert it into the database to make it a full text search?

    Read the article

  • What are the rules governing how a bind variable can be used in Postgres and where is this defined?

    - by Craig Miles
    I can have a table and function defined as: CREATE TABLE mytable ( mycol integer ); INSERT INTO mytable VALUES (1); CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol = l_myvar; RETURN l_myrow; END; $$ LANGUAGE plpgsql; In this case l_myvar acts as a bind variable for the value passed when I call: SELECT * FROM myfunction(1); and returns the row where mycol = 1 If I redefine the function as: CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol IN (l_myvar); RETURN l_myrow; END; $$ LANGUAGE plpgsql; SELECT * FROM myfunction(1); still returns the row where mycol = 1 However, if I now change the function definition to allow me to pass an integer array and try to this array in the IN clause, I get an error: CREATE OR REPLACE FUNCTION myfunction (l_myvar integer[]) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol IN (array_to_string(l_myvar, ',')); RETURN l_myrow; END; $$ LANGUAGE plpgsql; Analysis reveals that although: SELECT array_to_string(ARRAY[1, 2], ','); returns 1,2 as expected SELECT * FROM myfunction(ARRAY[1, 2]); returns the error operator does not exist: integer = text at the line: WHERE mycol IN (array_to_string(l_myvar, ',')); If I execute: SELECT * FROM mytable WHERE mycol IN (1,2); I get the expected result. Given that array_to_string(l_myvar, ',') evaluates to 1,2 as shown, why arent these statements equivalent. From the error message it is something to do with datatypes, but doesnt the IN(variable) construct appear to be behaving differently from the = variable construct? What are the rules here? I know that I could build a statement to EXECUTE, treating everything as a string, to achieve what I want to do, so I am not looking for that as a solution. I do want to understand though what is going on in this example. Is there a modification to this approach to make it work, the particular example being to pass in an array of values to build a dynamic IN clause without resorting to EXECUTE? Thanks in advance Craig

    Read the article

  • Getting a ReturnValue from cmd.Parameters in c#?

    - by mark smith
    Hi, I have just finished converted a vb.net app to c# and one of the lines is to get @ReturnValue from the parameter. I ended up having to CAST a lot of things.. Is there not a easier way here is what i have int rc = ((System.Data.SqlTypes.SqlInt32)(((System.Data.SqlClient.SqlParameter)(cmd.Parameters["@ReturnValue"])).SqlValue)).Value; In vb.net it was as simple as doing this Dim rc As Integer = Convert.ToInt32(cmd.Parameters("@ReturnValue").Value) Alot easier :-) But the problem with C# is the property Value isn't available unless I Cast to SqlParameter and i also need to cast to Sqltypes.SqlInt32 - i can't just do a standard Convert.ToInt32

    Read the article

  • Linq2Sql: query - subquery optimisation

    - by Budda
    I have the following query: IList<InfrStadium> stadiums = (from sector in DbContext.sectors where sector.Type=typeValue select new InfrStadium(sector.TeamId) ).ToList(); and InfrStadium class constructor: private InfrStadium(int teamId) { IList<Sector> teamSectors = (from sector in DbContext.sectors where sector.TeamId==teamId select sector) .ToList<>(); ... work with data } Current implementation perform 1+n queries, where n - number of records fetched the 1st time. I want to optimize that. And another one I would love to do using 'group' operator in way like this: IList<InfrStadium> stadiums = (from sector in DbContext.sectors group sector by sector.TeamId into team_sectors select new InfrStadium(team_sectors.Key, team_sectors) ).ToList(); with appropriate constructor: private InfrStadium(int iTeamId, IEnumerable<InfrStadiumSector> eSectors) { IList<Sector> teamSectors = eSectors.ToList(); ... work with data } But attempt to launch query causes the following error: Expression of type 'System.Int32' cannot be used for constructor parameter of type 'System.Collections.Generic.IEnumerable`1[InfrStadiumSector]' Question 1: Could you please explain, what is wrong here, I don't understand why 'team_sectors' is applied as 'System.Int32'? I've tried to change query a little (replace IEnumerable with IQueryeable): IList<InfrStadium> stadiums = (from sector in DbContext.sectors group sector by sector.TeamId into team_sectors select new InfrStadium(team_sectors.Key, team_sectors.AsQueryable()) ).ToList(); with appropriate constructor: private InfrStadium(int iTeamId, IQueryeable<InfrStadiumSector> eSectors) { IList<Sector> teamSectors = eSectors.ToList(); ... work with data } In this case I've received another but similar error: Expression of type 'System.Int32' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable1[InfrStadiumSector]' of method 'System.Linq.IQueryable1[InfrStadiumSector] AsQueryableInfrStadiumSector' Question 2: Actually, the same question: can't understand at all what is going on here... P.S. I have another to optimize query idea (describe here: Linq2Sql: query optimisation) but I would love to find a solution with 1 request to DB).

    Read the article

  • mysql count rows and grop them by month

    - by user2661296
    I have a table called cc_calls and there I have many call records I want to count them and group them in months I have a timestamp called starttime and I can use that row to extract the month, also limit the count for 12 months the results should be like: Month Count January 768768 February 876786 March 987979 April 765765 May 898797 June 876876 July 786575 August 765765 September 689787 October 765879 November 897989 December 876876 Can anyone guide me or show me the mysql query that I need to get this result.

    Read the article

  • Auto increment with a Unit Of Work

    - by Derick
    Context I'm building a persistence layer to abstract different types of databases that I'll be needing. On the relational part I have mySQL, Oracle and PostgreSQL. Let's take the following simplified MySQL tables: CREATE TABLE Contact ( ID varchar(15), NAME varchar(30) ); CREATE TABLE Address ( ID varchar(15), CONTACT_ID varchar(15), NAME varchar(50) ); I use code to generate system specific alpha numeric unique ID's fitting 15 chars in this case. Thus, if I insert a Contact record with it's Addresses I have my generated Contact.ID and Address.CONTACT_IDs before committing. I've created a Unit of Work (amongst others) as per Martin Fowler's patterns to add transaction support. I'm using a key based Identity Map in the UoW to track the changed records in memory. It works like a charm for the scenario above, all pretty standard stuff so far. The question scenario comes in when I have a database that is not under my control and the ID fields are auto-increment (or in Oracle sequences). In this case I do not have the db generated Contact.ID beforehand, so when I create my Address I do not have a value for Address.CONTACT_ID. The transaction has not been started on the DB session since all is kept in the Identity Map in memory. Question: What is a good approach to address this? (Avoiding unnecessary db round trips) Some ideas: Retrieve the last ID: I can do a call to the database to retrieve the last Id like: SELECT Auto_increment FROM information_schema.tables WHERE table_name='Contact'; But this is MySQL specific and probably something similar can be done for the other databases. If do this then would need to do the 1st insert, get the ID and then update the children (Address.CONTACT_IDs) – all in the current transaction context.

    Read the article

  • Add LINQ Auto-Generated Value Marker [Column(IsDbGenerated=true)] in Buddy Class

    - by Alex
    Hello, is it possible to decorate a field of a LINQ generated class with [Column(IsDbGenerated=true)] using a buddy class (which is linked to the LINQ class via [MetadataType(typeof(BuddyMetadata))]) ? My goal is to be able to clear and repopulate the LINQ ORM designer without having to set the "Auto Generate Value" property manually every time to re-establish the fact that certain columns are autogenerated. Thanks!

    Read the article

  • Copying 6000 tables and data from sqlserver to oracle ==> fastest method?

    - by nazer555
    i need to copy the tables and data (about 5 yrs data, 6200 tables) stored in sqlserver, i am using datastage and odbc connection to connect and datstage automatically creates the table with data, but its taking 2-3 hours per table as tables are very large(0.5 gig, 300+columns and about 400k rows). How can i achieve this the fastes as at this rate i am able to only copy 5 tables per day but within 30 days i need move over these 6000 tables.

    Read the article

  • How to Generate XML from Database

    - by Nisarg Mehta
    Hi , I am fetching data from two tables CARRIER_IFTA ,IFTA_NAME. My Select Query is like below.. SELECT t1.IFTA_LICENSE_NUMBER,t1.IFTA_BASE_STATE,t2.NAME_TYPE,t2.NAME from CARRIER_IFTA t1 inner join IFTA_NAME t2 on t1.IFTA_LICENSE_NUMBER=t2.IFTA_LICENSE_NUMBER My Data is coming in this way... IFTA_LICENSE_NUMBER IFTA_BASE_STATE NAME_TYPE NAME -------------------------------------------------------- 630908333 US LG XYZ 630908333 US MG PQR 730908344 US LG ABC Now using XSLT I want to generate XML like this <T0019> <IFTA_ACCOUNT> <IFTA_LICENSE_NUMBER>630908333</IFTA_LICENSE_NUMBER> <IFTA_BASE_STATE>US</IFTA_BASE_STATE> <IFTA_NAME> <NAME_TYPE>LG<NAME_TYPE> <NAME>XYZ</NAME> </IFTA_NAME> <IFTA_NAME> <NAME_TYPE>MG<NAME_TYPE> <NAME>PQR</NAME> <IFTA_NAME> </IFTA_ACCOUNT> <IFTA_ACCOUNT> <IFTA_LICENSE_NUMBER>730908344</IFTA_LICENSE_NUMBER> <IFTA_BASE_STATE>US</IFTA_BASE_STATE> <IFTA_NAME> <NAME_TYPE>LG<NAME_TYPE> <NAME>ABC</NAME> </IFTA_NAME> </IFTA_ACCOUNT> </T0019> i have used below xslt but it is not giveng me desire result ... <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0"> <xsl:template match="/ROWSET"> <xsl:element name="T0019"> <xsl:apply-templates select="IFTAACCOUNT"/> </xsl:element> </xsl:template> <xsl:template match="IFTAACCOUNT"> <xsl:element name="IFTAACCOUNT"> <xsl:apply-templates select="IFTA_CARRIER_ID_NUMBER"/> </xsl:element> </xsl:template> <xsl:template match="IFTA_LICENSE_NUMBER"> <xsl:element name="IFTA_LICENSE_NUMBER"> <xsl:apply-templates /> </xsl:element> </xsl:template> <xsl:template match="IFTA_BASE_STATE"> <xsl:element name="IFTA_BASE_STATE"> <xsl:apply-templates /> </xsl:element> </xsl:template> <xsl:template match="IRP_NAME"> <IRP_NAME> <xsl:apply-templates select="NAME"/> <xsl:apply-templates select="NAME_TYPE"/> </IRP_NAME> </xsl:template> <xsl:template match="NAME"> <xsl:element name="NAME"> <xsl:value-of select="." /> </xsl:element> </xsl:template> <xsl:template match="NAME_TYPE"> <xsl:element name="NAME_TYPE"> <xsl:apply-templates /> </xsl:element> </xsl:template> </xsl:stylesheet> but it is not giving desire result ... Please help me ... Thanks in Advance...

    Read the article

< Previous Page | 650 651 652 653 654 655 656 657 658 659 660 661  | Next Page >