Search Results

Search found 25640 results on 1026 pages for 'alter table'.

Page 288/1026 | < Previous Page | 284 285 286 287 288 289 290 291 292 293 294 295  | Next Page >

  • SQL to get rows (not groups) that match an aggregate

    - by xulochavez
    Given table USER (name, city, age), what's the best way to get the user details of oldest user per city? I have seen the following example SQL used in Oracle which I think it works select name, city, age from USER, (select city as maxCity, max(age) as maxAge from USER group by city) where city=maxCity and age=maxAge So in essence: use a nested query to select the grouping key and aggregate for it, then use it as another table in the main query and join with the grouping key and the aggregate value for each key. Is this the standard SQL way of doing it? Is it any quicker than using a temporary table, or is in fact using a temporary table interanlly anyway?

    Read the article

  • SQL - Add up all row-values of one column in a singletable

    - by ThE_-_BliZZarD
    Hello Everybody, I've got a question regarding a SQL-select-query: The table contains several columns, one of which is an Integer-column called "size" - the task I'm trying to perform is query the table for the sum of all rows (their values), or to be more exact get a artifical column in my ResultSet called "overallSize" which contains the sum of all "size"-values in the table. Preferable it would be possible to use a WHERE-clause to add only certain values ("WHERE bla = 5" or something similar). The DB-engine is HSQLDB (HyperSQL), which is compliant to SQL2008. Thank you in advance :)

    Read the article

  • How to connect two query results?

    - by nijansen
    I want to retrieve all ids within a certain timespan. The timestamps however, are stored in a different table: Table A has column my_id Table B has columns my_id, timestamp I would want something like SELECT id, time FROM (SELECT my_id AS id FROM A) q1, (SELECT timestamp AS time FROM B WHERE my_id = id) q2 ; But how can I get the value of id within a different subquery? Is there an elegant solution for this problem?

    Read the article

  • Issue in alternate Row color using each() method of JQuery

    - by user1323981
    I have a table as under <table > <tr> <th scope="col">EmpId</th><th scope="col">EmpName</th> </tr> <tr> <td>1</td><td>ABC</td> </tr> <tr> <td>2</td><td>DEF</td> </tr> </table> I want to set the alternate row color of only the "td" elements of the table and not "th" by using only each() function. I have tried with <style type="text/css"> tr.even { background-color: green; } tr.odd { background-color: yellow; } </style> $(document).ready(function () { $('table > tbody').each(function () { $('tr:odd', this).addClass('odd').removeClass('even'); $('tr:even', this).addClass('even').removeClass('odd'); }); }); Though this works but it accepts also "th" element. How to avoid that? Please help Thanks

    Read the article

  • strange situation of UITableView when I want to scroll it

    - by ivanlw
    I'm building an easy app for chatting, each time I type in a sentence, the sentences is added into an array(which used to load the table view in cellForRowAtIndexPath delegate method), then I reload the table view. At last I use the following code to scroll the table view to the bottom if ([self.chatList numberOfRowsInSection:0] != 0) { NSUInteger rowCount = [self.chatArray count]; NSIndexPath* indexPath = [NSIndexPath indexPathForRow:rowCount-1 inSection:0]; [self.chatList scrollToRowAtIndexPath:indexPath atScrollPosition:UITableViewScrollPositionTop animated:YES]; } however, the table view sometimes performs well, sometimes only scrolls to the second to the last line……

    Read the article

  • How should I solve this MySql problem (PHP) ? (Beginner)

    - by Camran
    I have several tables in a MySql database. I have a classifieds website, and at the bottom I display the users last visited classifieds. I do this by storing the ID:s of the ads to an array in the cookie. Now, my db is made up like this kindof: Main Table: // Stores global information, ie these fields have to be filled out in every record, never be blank ID Price category Seller Item Table: // Stores descriptive info about whats for sale ID AD_ID (FK) //This is the same as ID in the MAIN TABLE Color Size Mileage etc My problem is that I need to know what category the ad is in, in order to query mysql for the right information I think. So I need two variables, but the cookie only has one (ID) stored. Offcourse I could make two queries, first one just matching the ID to the main_table and fetch the category from the Main_table. Then make the second query and fetch all other info from the right table. Here is an example if the category was Vehicles: SELECT * FROM main_table, vehicles_table, WHERE main_table.id=$id_from_cookie AND main_table.ad_id=vehicles_table.ad_id As you can see above, I need the category to write in what table to check, right? But I think there must be a smarter way, like fetching them in one single query using only one variable (id from cookie)? How should I do this? Understand? Let me know if you need more input... Thanks

    Read the article

  • Access database Need to prevent from approving overlapping OT.Second Try with modified request Not a programmer [on hold]

    - by user2512764
    Employees Signups on company Website for advance overtime line. Access table already has overtime signups which does not require user to add the time but it requires only to add location as approved. Since this table has field Employee name, Date, start time and End time and location, All the fields has the data except for location. In the data base I have created a form based on this table. Since the table already have most of the information User only has to add location in the form field in order to approve overtime. Once user approves an overtime line for example: User approves overtime for employee name 'John' which starts on 7/1/2013 at 0400-0800, location is successfully added. When user tries to add location for John again which might has the start time for 7/1/2013 at 0600=0900. Again we are not entering Start time, End time and date it is already in the table. we are only entering location as approval. Soon user enters the location for John in the form field, since there is a conflict with previously overtime line which has already been approved. program needs to check employee name, date and time in previously approved (Added location) overtime line and The location in current record needs to be deleted and go to next record. I hope I have explained it in understandable format. Thank You,

    Read the article

  • Best way to update record X when Y is inserted

    - by Saif Bechan
    I have a huge table that is mainly used for backup and administrative purposes. The only records that matters is the last inserted record. On every hit to order by time inserted is just too slow. I want keep a separate table with the last inserted id. In PHP I now insert, get last inserted id, and update the other table. Is there a more efficient way to do this.

    Read the article

  • How to style email body in php

    - by Vinay
    I want to style mail body. I have tried the below methods to style mail body. But all of them didn't work 1) Used external style sheet style.css td{padding:10px;} mail.php <link rel="stylesheet" href="style.css"></link><table><td>....</td></table> 2) Defined Internal Style Sheet: mail.php <style type="text/css"> td{ padding-bottom:8px; } </style> <table><td>....</td></table> I know, Inline style works by doing <td style='padding-bottom:8px'>, But i have got many tables, doing the inline style is not a good idea, Is there any work around so that no need to define style for each element

    Read the article

  • Write a JavaScript that accepts a number from the user using the “prompt” function

    - by A sw A
    Write a JavaScript that accepts a number from the user using the “prompt” function. Then it draws a table in the HTML document that has the user specified number of rows and columns. In each table data, it displays the result of the math operation (row raised to the power of column). For example, if the user enters the number 3, the JavaScript should draw the following table: 1 1 1 2 4 8 3 9 27

    Read the article

  • What is the best practise for relational database tables in mysql?

    - by George
    Hi, I know, there is a lot of info on mysql out there. But I was not really able to find an answer to this specific and actually simple question: Let's say I have two tables: USERS (with many fields, e.g. name, street, email, etc.) and GROUPS (also with many fields) The relation is (I guess?) 1:n, that is ONE user can be a member of MANY groups. What I dis, is create another table, named USERS_GROUPS_REL. This table has only two fields: us_id (unique key of table USERS) and gr_id (unique key of table GROUPS) In PHP I do a query with join. Is this "best practice" or is there a better way? Thankful for any hint!

    Read the article

  • How to retrieve from two tables with same foriegn key repeated more than once?

    - by Sarenya
    How to display the datas of tables, that are linked by a primay key and foriegn key, where the foriegn key of the data repeats? For ex. I have two tables, ParentTable and Childtable. The primarykey of the Parent table acts as the foriegn key of the Child table. There are more than one record with same ParentId in Child table. How to retrieve them and display in a single Grid or List or any type of view?

    Read the article

  • SQL query for selecting most recent entries

    - by Mr_Skid_Marks
    A table in my database has a column, DATE_ADDED (stored in seconds). I want to extract all rows with the most recent date (aka largest value for DATE_ADDED). The only solution I have come up with is to SELECT all the rows in ASC (ascending) order, grab the last entry from the table, check the date on this, and perform another SELECT on the table but this time only for the discovered DATE_ADDED. Is it possibly to simplify this series of queries into a single one? My thought is I should be able to do a SELECT on all of the largest values in the table, but I am struggling to come up with a proper query.

    Read the article

  • weird index behavior

    - by TasostheGreat
    I have set up my table with an index only on done_status(done_status =INT), when I use EXPLAIN SELECT * FROM reminder WHERE done_status=2 i get this back id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE reminder ALL done_status NULL NULL NULL 5 Using where but when I give this command EXPLAIN SELECT * FROM reminder WHERE done_status=1 that's what I get back: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE reminder ref done_status done_status 4 const 2 first time it shows me it uses 5 rows second time 2 rows I don't think the index works, if I understood it right first time it should give me 3 rows. What do I do wrong? SHOW INDEX FROM reminder: Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment reminder 1 done_status 1 done_status A 5 NULL NULL BTREE

    Read the article

  • ??GoldenGate Replicat?HANDLECOLLISIONS??

    - by Liu Maclean(???)
    HANDLECOLLISIONS?????goldengate????????REPLICAT??,???????????????????,???????????????????????????,??????????????????????????reperror????????discard??,????????????????,??????(????error mapping????,???????discard??),??????????????;?????????????????,????????? ??HANDLECOLLISIONS?????: target??delete??(missing delete),??????????discardfile target??update??(missing update) ????????=» update???INSERT ,???????????? ?????????=» ??????????discardfile ????????????target??,???replicat???UPDATE?????????????? ??1 target??delete??(missing delete) : C:\Users\ML>sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 18 13:38:03 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn sender/oracle Connected. SQL> create table handlec(t1 int primary key,t2 int); Table created. SQL> insert into handlec values(1,2); 1 row created. SQL> insert into handlec values(3,2); 1 row created. SQL> insert into handlec values(4,2); 1 row created. SQL> commit; Commit complete. SQL> select * from handlec; T1 T2 ---------- ---------- 1 2 3 2 4 2 target : SQL> conn receiver/oracle Connected. SQL> create table handlec(t1 int primary key,t2 int); Table created. SQL> insert into handlec values(1,2); 1 row created. SQL> commit; SQL> select * from handlec; T1 T2 ---------- ---------- 1 2 SQL> GGSCI (XIANGBLI-CN) 1> alter extract load2 , begin now EXTRACT altered. GGSCI (XIANGBLI-CN) 4> alter replicat rep2, begin now REPLICAT altered. GGSCI (XIANGBLI-CN) 13> add trandata sender.* Logging of supplemental redo data enabled for table SENDER.HANDLEC. Logging of supplemental redo log data is already enabled for table SENDER.TV. GGSCI (XIANGBLI-CN) 14> start mgr MGR is already running. GGSCI (XIANGBLI-CN) 15> start er * Sending START request to MANAGER ... EXTRACT LOAD2 starting Sending START request to MANAGER ... REPLICAT REP2 starting GGSCI (XIANGBLI-CN) 16> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING LOAD2 00:00:00 00:00:01 REPLICAT RUNNING REP2 00:00:00 00:00:08 ***SOURCE?????TARGET????? SQL> delete handlec where t1=3; 1 row deleted. SQL> commit; Commit complete. ??SQL error 1403??,REPLICAT ABORT 2012-09-18 13:45:48 WARNING OGG-01004 Aborted grouped transaction on 'RECEIVER.HANDLEC', Database error 1403 (OCI Error ORA-01403: no data found, SQL ). 2012-09-18 13:45:48 WARNING OGG-01003 Repositioning to rba 1091 in seqno 3. 2012-09-18 13:45:48 WARNING OGG-01154 SQL error 1403 mapping SENDER.HANDLEC to RECEIVER.HANDLEC OCI Error ORA-01403: no data found, SQL . 2012-09-18 13:45:48 WARNING OGG-01003 Repositioning to rba 1091 in seqno 3. Source Context : SourceModule : [er.errors] SourceID : [er/errors.cpp] SourceFunction : [take_rep_err_action] SourceLine : [623] ThreadBacktrace : [8] elements : [D:\ogg\V34342-01\gglog.dll(??1CContextItem@@UEAA@XZ+0x3272) [0x000000018010BDD2]] : [D:\ogg\V34342-01\gglog.dll(?_MSG_ERR_MAP_TO_TANDEM_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@AEBV?$CQualDBObjName@$00@ggapp@gglib@ggs@@1W4MessageDisposition@CMessageFactory@@@Z+0x138) [0x00000001800AD508]] : [D:\ogg\V34342-01\replicat.exe(ERCALLBACK+0x6e1e) [0x0000000140099D5E]] : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x4411) [0x00000001400C9BE1]] : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x289cd) [0x00000001400EE19D]] : [D:\ogg\V34342-01\replicat.exe(CommonLexerNewSSD+0x9440) [0x00000001402AE980]] : [C:\windows\system32\kernel32.dll(BaseThreadInitThunk+0xd) [0x000000007733652D]] : [C:\windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x000000007746C521]] 2012-09-18 13:45:48 ERROR OGG-01296 Error mapping from SENDER.HANDLEC to RECEIVER.HANDLEC. *********************************************************************** * ** Run Time Statistics ** * *********************************************************************** Last record for the last committed transaction is the following: ___________________________________________________________________ Trail name : D:\ogg\V34342-01\ex\ze000003 Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 9 (x0009) IO Time : 2012-09-18 13:45:38.000000 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 44 AuditPos : 3337232 Continued : N (x00) RecCount : 1 (x01) 2012-09-18 13:45:38.000000 Delete Len 9 RBA 1091 Name: SENDER.HANDLEC ___________________________________________________________________ Reading D:\ogg\V34342-01\ex\ze000003, current RBA 1091, 0 records Report at 2012-09-18 13:45:48 (activity since 2012-09-18 13:45:48) From Table SENDER.HANDLEC to RECEIVER.HANDLEC: # inserts: 0 # updates: 0 # deletes: 0 # discards: 1 Last log location read: FILE: D:\ogg\V34342-01\ex\ze000003 SEQNO: 3 RBA: 1091 TIMESTAMP: 2012-09-18 13:45:38.000000 EOF: NO READERR: 0 2012-09-18 13:45:48 ERROR OGG-01668 PROCESS ABENDING. 2012-09-18 13:45:48 INFO OGG-01237 Trace file D:\ogg\V34342-01\REP_TRACE1.TRC closed. 2012-09-18 13:45:48 INFO OGG-01237 Trace file D:\ogg\V34342-01\REP_TRACE2.TRC closed. CACHE OBJECT MANAGER statistics CACHE MANAGER VM USAGE vm current = 0 vm anon queues = 0 vm anon in use = 0 vm file = 0 vm used max = 0 ==> CACHE BALANCED CACHE CONFIGURATION cache size = 2G cache force paging = 3.41G buffer min = 64K buffer highwater = 8M pageout eligible size = 8M ================================================================================ ??skiptransaction???????? GGSCI (XIANGBLI-CN) 18> start rep2 skiptransaction Sending START request to MANAGER ... REPLICAT REP2 starting ??2 target??update??(missing update),???????? : ???????, ??source????????? SQL> update handlec set t1=5 where t1=4; 1 row updated. SQL> commit; Commit complete. ???target ????(miss update)??????? Database error 1403+OGG-01296 2012-09-18 13:49:30 WARNING OGG-01004 Aborted grouped transaction on 'RECEIVER.HANDLEC', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE "RECEIVER"."HANDLEC" SET "T1" = :a1 WHERE "T1" = :b0>). 2012-09-18 13:49:30 WARNING OGG-01003 Repositioning to rba 1218 in seqno 3. 2012-09-18 13:49:30 WARNING OGG-01003 Repositioning to rba 1218 in seqno 3. Source Context : SourceModule : [er.errors] SourceID : [er/errors.cpp] SourceFunction : [take_rep_err_action] SourceLine : [623] ThreadBacktrace : [8] elements : [D:\ogg\V34342-01\gglog.dll(??1CContextItem@@UEAA@XZ+0x3272) [0x000000018010BDD2]] : [D:\ogg\V34342-01\gglog.dll(?_MSG_ERR_MAP_TO_TANDEM_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@AEBV?$CQualDBObjName@$00@ggapp@gglib@ggs@@1W4MessageDisposition@CMessageFactory@@@Z+0x138) [0x00000001800AD508]] : [D:\ogg\V34342-01\replicat.exe(ERCALLBACK+0x6e1e) [0x0000000140099D5E]] : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x4411) [0x00000001400C9BE1]] : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x289cd) [0x00000001400EE19D]] : [D:\ogg\V34342-01\replicat.exe(CommonLexerNewSSD+0x9440) [0x00000001402AE980]] : [C:\windows\system32\kernel32.dll(BaseThreadInitThunk+0xd) [0x000000007733652D]] : [C:\windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x000000007746C521]] 2012-09-18 13:49:30 ERROR OGG-01296 Error mapping from SENDER.HANDLEC to RECEIVER.HANDLEC. ??HANDLECOLLISIONS?,rep??????????discard?? GGSCI (XIANGBLI-CN) 23> view params rep2 replicat rep2 userid receiver , password oracle trace ./rep_trace1.trc trace2 ./rep_trace2.trc ASSUMETARGETDEFS HANDLECOLLISIONS map sender.*, target receiver.*; GGSCI (XIANGBLI-CN) 18> start rep2 SQL> select * from handlec; T1 T2 ---------- ---------- 1 2 5 ????T1=5 T2 NULL?????? ,??update?????????????,??replicat??????????????update????????????????,?????T2 ?NULL ,????????????EXTRACT??PKUPDATE??? ????????FETCHOPTIONS FETCHPKUPDATECOLS ????????EXTRACT?????,???EXTRACT? ????extract???????????? ??????: SQL> conn receiver/oracle Connected. SQL> select * from handlec; T1 T2 ---------- ---------- 1 2 10 100 5 20 200 SQL> delete handlec where t1=5; 1 row deleted. SQL> commit; Commit complete. SQL> select * from handlec; T1 T2 ---------- ---------- 1 2 10 100 20 200 SQL> conn sender/oracle Connected. SQL> update handlec set t1=t1+1000 where t1=5; 1 row updated. SQL> commit; Commit complete. SQL> conn receiver/oracle Connected. SQL> SQL> SQL> select * from handlec; T1 T2 ---------- ---------- 1 2 10 100 20 200 1005 2 ???????FETCHOPTIONS FETCHPKUPDATECOLS??????redo image???trail?,????primary key?????HANDLECOLLISIONS????target??????????? ??3 ????????????target??,???replicat???UPDATE??????????????: *** TARGET SQL> conn receiver/oracle Connected. SQL> select * from handlec; T1 T2 ---------- ---------- 1 2 10 9 5 target????? t1=10 t2=9??? ,????source???(10,100)??? >>SOURCE SQL> insert into handlec values(10,100); 1 row created. SQL> commit; >>TARGET SQL> select * from handlec; T1 T2 ---------- ---------- 1 2 10 100 5 ???????source?insert??,???target???????????????HANDLECOLLISIONS?REPLICAT???UPDATE??????COLUMNS ?? HANDLECOLLISIONS?????goldengate????????REPLICAT??,???????????????????,???????????????????????????,??????????????????????????reperror????????discard??,????????????????,??????,??????????????;?????????????????,????????? ??HANDLECOLLISIONS?????: target??delete??(missing delete),??????????discardfile target??update??(missing update) ????????=» update???INSERT ,???????????? ?????????=» ??????????discardfile ????????????target??,???replicat???UPDATE?????????????? ?:???????????Insert/Delete??,????????????????Replicat?????abend,????? ???????????,??target??HANDLECOLLISIONS??update??,?????INSERT??????,???????????????,FETCHOPTIONS FETCHPKUPDATECOLS??????redo image???trail?,????primary key?????HANDLECOLLISIONS????target??????????? ??????send ??????HANDLECOLLISIONS GGSCI (XIANGBLI-CN) 29> send rep2, NOHANDLECOLLISIONS Sending NOHANDLECOLLISIONS request to REPLICAT REP2 ... REP2 NOHANDLECOLLISIONS set for 1 tables and 0 wildcard entries

    Read the article

  • MySQL Memory usage

    - by Rob Stevenson-Leggett
    Our MySQL server seems to be using a lot of memory. I've tried looking for slow queries and queries with no index and have halved the peak CPU usage and Apache memory usage but the MySQL memory stays constantly at 2.2GB (~51% of available memory on the server). Here's the graph from Plesk. Running top in the SSH window shows the same figures. Does anyone have any ideas on why the memory usage is constant like this and not peaks and troughs with usage of the app? Here's the output of the MySQL Tuning Primer script: -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.0.77-log x86_64 Uptime = 1 days 14 hrs 4 min 21 sec Avg. qps = 22 Total Questions = 3059456 Threads Connected = 13 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is enabled. Current long_query_time = 1 sec. You have 6 out of 3059477 that take longer than 1 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 0 Current threads_cached = 0 Current threads_per_sec = 2 Historic threads_per_sec = 0 Threads created per/sec are overrunning threads cached You should raise thread_cache_size MAX CONNECTIONS Current max_connections = 100 Current threads_connected = 14 Historic max_used_connections = 20 The number of used connections is 20% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 6 M Current InnoDB data space = 18 M Current InnoDB buffer pool free = 0 % Current innodb_buffer_pool_size = 8 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 2.07 G Configured Max Per-thread Buffers : 274 M Configured Max Global Buffers : 2.01 G Configured Max Memory Limit : 2.28 G Physical Memory : 3.84 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 4 M Current key_buffer_size = 7 M Key cache miss rate is 1 : 40 Key buffer free ratio = 81 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is supported but not enabled Perhaps you should set the query_cache_size SORT OPERATIONS Current sort_buffer_size = 2 M Current read_rnd_buffer_size = 256 K Sort buffer seems to be fine JOINS Current join_buffer_size = 132.00 K You have had 16 queries where a join could not use an index properly You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass. Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found. OPEN FILES LIMIT Current open_files_limit = 1024 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_cache value = 64 tables You have a total of 426 tables You have 64 open tables. Current table_cache hit rate is 1% , while 100% of your table cache is in use You should probably increase your table_cache TEMP TABLES Current max_heap_table_size = 16 M Current tmp_table_size = 32 M Of 15134 temp tables, 9% were created on disk Effective in-memory tmp_table_size is limited to max_heap_table_size. Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 2915 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 142213 Your table locking seems to be fine The app is a facebook game with about 50-100 concurrent users. Thanks, Rob

    Read the article

  • Adding Extra Hard Drives Debian Fdisk

    - by Belgin Fish
    well I just got a new server and it's a little different than what I'm use to, when I run cfdisk I get WARNING: GPT (GUID Partition Table) detected on '/dev/sda'! The util fdisk doesn't support GPT. Use GNU Parted. Disk /dev/sda: 3000.6 GB, 3000592982016 bytes 255 heads, 63 sectors/track, 364801 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 4096 bytes I/O size (minimum/optimal): 4096 bytes / 4096 bytes Disk identifier: 0x00000000 Device Boot Start End Blocks Id System /dev/sda1 1 267350 2147483647+ ee GPT Partition 1 does not start on physical sector boundary. WARNING: GPT (GUID Partition Table) detected on '/dev/sdb'! The util fdisk doesn't support GPT. Use GNU Parted. Disk /dev/sdb: 3000.6 GB, 3000592982016 bytes 255 heads, 63 sectors/track, 364801 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 4096 bytes I/O size (minimum/optimal): 4096 bytes / 4096 bytes Disk identifier: 0x00000000 Device Boot Start End Blocks Id System /dev/sdb1 1 267350 2147483647+ ee GPT Partition 1 does not start on physical sector boundary. WARNING: GPT (GUID Partition Table) detected on '/dev/sdc'! The util fdisk doesn't support GPT. Use GNU Parted. Disk /dev/sdc: 3000.6 GB, 3000592982016 bytes 255 heads, 63 sectors/track, 364801 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 4096 bytes I/O size (minimum/optimal): 4096 bytes / 4096 bytes Disk identifier: 0x00000000 Device Boot Start End Blocks Id System /dev/sdc1 1 267350 2147483647+ ee GPT Partition 1 does not start on physical sector boundary. WARNING: GPT (GUID Partition Table) detected on '/dev/sdd'! The util fdisk doesn't support GPT. Use GNU Parted. Disk /dev/sdd: 3000.6 GB, 3000592982016 bytes 255 heads, 63 sectors/track, 364801 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 4096 bytes I/O size (minimum/optimal): 4096 bytes / 4096 bytes Disk identifier: 0x00000000 Device Boot Start End Blocks Id System /dev/sdd1 1 267350 2147483647+ ee GPT Partition 1 does not start on physical sector boundary. WARNING: GPT (GUID Partition Table) detected on '/dev/sdf'! The util fdisk doesn't support GPT. Use GNU Parted. Disk /dev/sdf: 3000.6 GB, 3000592982016 bytes 255 heads, 63 sectors/track, 364801 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 4096 bytes I/O size (minimum/optimal): 4096 bytes / 4096 bytes Disk identifier: 0x00000000 Device Boot Start End Blocks Id System /dev/sdf1 1 267350 2147483647+ ee GPT Partition 1 does not start on physical sector boundary. WARNING: GPT (GUID Partition Table) detected on '/dev/sde'! The util fdisk doesn't support GPT. Use GNU Parted. Disk /dev/sde: 3000.6 GB, 3000592982016 bytes 255 heads, 63 sectors/track, 364801 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Sector size (logical/physical): 512 bytes / 4096 bytes I/O size (minimum/optimal): 4096 bytes / 4096 bytes Disk identifier: 0x00000000 Device Boot Start End Blocks Id System /dev/sde1 1 267350 2147483647+ ee GPT Partition 1 does not start on physical sector boundary. Usually it tells me which ones arn't partitioned and stuff, and I only have 6 drives in my server and there's 6 showing up here so I'm only assuming the first ones already mounted and formatted correctly? I'm not really sure if anyone would help me out here. Basically I just want to format and mount these drives :)

    Read the article

  • Login failed for user 'sa' because the account is currently locked out. The system administrator can

    - by cabhilash
    Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. (Microsoft SQL Server, Error: 18486) SQL server has local password policies. If policy is enabled which locks down the account after X number of failed attempts then the account is automatically locked down.This error with 'sa' account is very common. sa is default administartor login available with SQL server. So there are chances that an ousider has tried to bruteforce your system. (This can cause even if a legitimate tries to access the account with wrong password.Sometimes a user would have changed the password without informing others. So the other users would try to lo) You can unlock the account with the following options (use another admin account or connect via windows authentication) Alter account & unlock ALTER LOGIN sa WITH PASSWORD='password' UNLOCK Use another account Almost everyone is aware of the sa account. This can be the potential security risk. Even if you provide strong password hackers can lock the account by providing the wrong password. ( You can provide extra security by installing firewall or changing the default port but these measures are not always practical). As a best practice you can disable the sa account and use another account with same privileges.ALTER LOGIN sa DISABLE You can edit the lock-ot options using gpedit.msc( in command prompt type gpedit.msc and press enter). Navigate to Account Lokout policy as shown in the figure The Following options are available Account lockout threshold This security setting determines the number of failed logon attempts that causes a user account to be locked out. A locked-out account cannot be used until it is reset by an administrator or until the lockout duration for the account has expired. You can set a value between 0 and 999 failed logon attempts. If you set the value to 0, the account will never be locked out. Failed password attempts against workstations or member servers that have been locked using either CTRL+ALT+DELETE or password-protected screen savers count as failed logon attempts. Account lockout duration This security setting determines the number of minutes a locked-out account remains locked out before automatically becoming unlocked. The available range is from 0 minutes through 99,999 minutes. If you set the account lockout duration to 0, the account will be locked out until an administrator explicitly unlocks it. If an account lockout threshold is defined, the account lockout duration must be greater than or equal to the reset time. Default: None, because this policy setting only has meaning when an Account lockout threshold is specified. Reset account lockout counter after This security setting determines the number of minutes that must elapse after a failed logon attempt before the failed logon attempt counter is reset to 0 bad logon attempts. The available range is 1 minute to 99,999 minutes. If an account lockout threshold is defined, this reset time must be less than or equal to the Account lockout duration. Default: None, because this policy setting only has meaning when an Account lockout threshold is specified.When creating SQL user you can set CHECK_POLICY=on which will enforce the windows password policy on the account. The following policies will be applied Define the Enforce password history policy setting so that several previous passwords are remembered. With this policy setting, users cannot use the same password when their password expires.  Define the Maximum password age policy setting so that passwords expire as often as necessary for your environment, typically, every 30 to 90 days. With this policy setting, if an attacker cracks a password, the attacker only has access to the network until the password expires.  Define the Minimum password age policy setting so that passwords cannot be changed until they are more than a certain number of days old. This policy setting works in combination with the Enforce password historypolicy setting. If a minimum password age is defined, users cannot repeatedly change their passwords to get around the Enforce password history policy setting and then use their original password. Users must wait the specified number of days to change their passwords.  Define a Minimum password length policy setting so that passwords must consist of at least a specified number of characters. Long passwords--seven or more characters--are usually stronger than short ones. With this policy setting, users cannot use blank passwords, and they have to create passwords that are a certain number of characters long.  Enable the Password must meet complexity requirements policy setting. This policy setting checks all new passwords to ensure that they meet basic strong password requirements.  Password must meet the following complexity requirement, when they are changed or created: Not contain the user's entire Account Name or entire Full Name. The Account Name and Full Name are parsed for delimiters: commas, periods, dashes or hyphens, underscores, spaces, pound signs, and tabs. If any of these delimiters are found, the Account Name or Full Name are split and all sections are verified not to be included in the password. There is no check for any character or any three characters in succession. Contain characters from three of the following five categories:  English uppercase characters (A through Z) English lowercase characters (a through z) Base 10 digits (0 through 9) Non-alphabetic characters (for example, !, $, #, %) A catch-all category of any Unicode character that does not fall under the previous four categories. This fifth category can be regionally specific.

    Read the article

  • Database Owner Conundrum

    - by Johnm
    Have you ever restored a database from a production environment on Server A into a development environment on Server B and had some items, such as Service Broker, mysteriously cease functioning? You might want to consider reviewing the database owner property of the database. The Scenario Recently, I was developing some messaging functionality that utilized the Service Broker feature of SQL Server in a development environment. Within the instance of the development environment resided two databases: One was a restored version of a production database that we will call "RestoreDB". The second database was a brand new database that has yet to exist in the production environment that we will call "DevDB". The goal is to setup a communication path between RestoreDB and DevDB that will later be implemented into the production database. After implementing all of the Service Broker objects that are required to communicate within a database as well as between two databases on the same instance I found myself a bit confounded. My testing was showing that the communication was successful when it was occurring internally within DevDB; but the communication between RestoreDB and DevDB did not appear to be working. Profiler to the rescue After carefully reviewing my code for any misspellings, missing commas or any other minor items that might be a syntactical cause of failure, I decided to launch Profiler to aid in the troubleshooting. After simulating the cross database messaging, I noticed the following error appearing in Profiler: An exception occurred while enqueueing a message in the target queue. Error: 33009, State: 2. The database owner SID recorded in the master database differs from the database owner SID recorded in database '[Database Name Here]'. You should correct this situation by resetting the owner of database '[Database Name Here]' using the ALTER AUTHORIZATION statement. Now, this error message is a helpful one. Not only does it identify the issue in plain language, it also provides a potential solution. An execution of the following query that utilizes the catalog view sys.transmission_queue revealed the same error message for each communication attempt: SELECT     * FROM        sys.transmission_queue; Seeing the situation as a learning opportunity I dove a bit deeper. Reviewing the database properties  The owner of a specific database can be easily viewed by right-clicking the database in SQL Server Management Studio and selecting the "properties" option. The owner is listed on the "General" page of the properties screen. In my scenario, the database in the production server was created by Frank the DBA; therefore his server login appeared as the owner: "ServerName\Frank". While this is interesting information, it certainly doesn't tell me much in regard to the SID (security identifier) and its existence, or lack thereof, in the master database as the error suggested. I pulled together the following query to gather more interesting information: SELECT     a.name     , a.owner_sid     , b.sid     , b.name     , b.type_desc FROM        master.sys.databases a     LEFT OUTER JOIN master.sys.server_principals b         ON a.owner_sid = b.sid WHERE     a.name not in ('master','tempdb','model','msdb'); This query also helped identify how many other user databases in the instance were experiencing the same issue. In this scenario, I saw that there were no matching SIDs in server_principals to the owner SID for my database. What login should be used as the database owner instead of Frank's? The system stored procedure sp_helplogins will provide a list of the valid logins that can be used. Here is an example of its use, revealing all available logins: EXEC sp_helplogins;  Fixing a hole The error message stated that the recommended solution was to execute the ALTER AUTHORIZATION statement. The full statement for this scenario would appear as follows: ALTER AUTHORIZATION ON DATABASE:: [Database Name Here] TO [Login Name]; Another option is to execute the following statement using the sp_changedbowner system stored procedure; but please keep in mind that this stored procedure has been deprecated and will likely disappear in future versions of SQL Server: EXEC dbo.sp_changedbowner @loginname = [Login Name]; .And They Lived Happily Ever After Upon changing the database owner to an existing login and simulating the inner and cross database messaging the errors have ceased. More importantly, all messages sent through this feature now successfully complete their journey. I have added the ownership change to my restoration script for the development environment.

    Read the article

  • Database Rebuild

    - by Robert May
    I promised I’d have a simpler mechanism for rebuilding the database.  Below is a complete MSBuild targets file for rebuilding the database from scratch.  I don’t know if I’ve explained the rational for this.  The reason why you’d WANT to do this is so that each developer has a clean version of the database on their local machine.  This also includes the continuous integration environment.  Basically, you can do whatever you want to the database without fear, and in a minute or two, have a completely rebuilt database structure. DBDeploy (including the KTSC build task for dbdeploy) is used in this script to do change tracking on the database itself.  The MSBuild ExtensionPack is used in this target file.  You can get an MSBuild DBDeploy task here. There are two database scripts that you’ll see below.  First is the task for creating an admin (dbo) user in the system.  This script looks like the following: USE [master] GO If not Exists (select Name from sys.sql_logins where name = '$(User)') BEGIN CREATE LOGIN [$(User)] WITH PASSWORD=N'$(Password)', DEFAULT_DATABASE=[$(DatabaseName)], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF END GO EXEC master..sp_addsrvrolemember @loginame = N'$(User)', @rolename = N'sysadmin' GO USE [$(DatabaseName)] GO CREATE USER [$(User)] FOR LOGIN [$(User)] GO ALTER USER [$(User)] WITH DEFAULT_SCHEMA=[dbo] GO EXEC sp_addrolemember N'db_owner', N'$(User)' GO The second creates the changelog table.  This script can also be found in the dbdeploy.net install\scripts directory. CREATE TABLE changelog ( change_number INTEGER NOT NULL, delta_set VARCHAR(10) NOT NULL, start_dt DATETIME NOT NULL, complete_dt DATETIME NULL, applied_by VARCHAR(100) NOT NULL, description VARCHAR(500) NOT NULL ) GO ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number, delta_set) GO Finally, Here’s the targets file. <Projectxmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0" DefaultTargets="Update">   <PropertyGroup>     <DatabaseName>TestDatabase</DatabaseName>     <Server>localhost</Server>     <ScriptDirectory>.\Scripts</ScriptDirectory>     <RebuildDirectory>.\Rebuild</RebuildDirectory>     <TestDataDirectory>.\TestData</TestDataDirectory>     <DbDeploy>.\DBDeploy</DbDeploy>     <User>TestUser</User>     <Password>TestPassword</Password>     <BCP>bcp</BCP>     <BCPOptions>-S$(Server) -U$(User) -P$(Password) -N -E -k</BCPOptions>     <OutputFileName>dbDeploy-output.sql</OutputFileName>     <UndoFileName>dbDeploy-output-undo.sql</UndoFileName>     <LastChangeToApply>99999</LastChangeToApply>   </PropertyGroup>     <ImportProject="$(MSBuildExtensionsPath)\ExtensionPack\4.0\MSBuild.ExtensionPack.tasks"/>   <UsingTask TaskName="Ktsc.Build.DBDeploy" AssemblyFile="$(DbDeploy)\Ktsc.Build.dll"/>   <ItemGroup>     <VariableInclude="DatabaseName">       <Value>$(DatabaseName)</Value>     </Variable>     <VariableInclude="Server">       <Value>$(Server)</Value>     </Variable>     <VariableInclude="User">       <Value>$(User)</Value>     </Variable>     <VariableInclude="Password">       <Value>$(Password)</Value>     </Variable>   </ItemGroup>     <TargetName="Rebuild">     <!--Take the database offline to disconnect any users. Requires that the current user is an admin of the sql server machine.-->     <MSBuild.ExtensionPack.SqlServer.SqlCmd Variables="@(Variable)" Database="$(DatabaseName)" TaskAction="Execute" CommandLineQuery ="ALTER DATABASE $(DatabaseName) SET OFFLINE WITH ROLLBACK IMMEDIATE"/>         <!--Bring it back online.  If you don't, the database files won't be deleted.-->     <MSBuild.ExtensionPack.Sql2008.DatabaseTaskAction="SetOnline" DatabaseItem="$(DatabaseName)"/>     <!--Delete the database, removing the existing files.-->     <MSBuild.ExtensionPack.Sql2008.DatabaseTaskAction="Delete" DatabaseItem="$(DatabaseName)"/>     <!--Create the new database in the default database path location.-->     <MSBuild.ExtensionPack.Sql2008.DatabaseTaskAction="Create" DatabaseItem="$(DatabaseName)" Force="True"/>         <!--Create admin user-->     <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="(local)" Database="$(DatabaseName)" InputFiles="$(RebuildDirectory)\0002 Create Admin User.sql" Variables="@(Variable)" />     <!--Create the dbdeploy changelog.-->     <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="(local)" Database="$(DatabaseName)" LogOn="$(User)" Password="$(Password)" InputFiles="$(RebuildDirectory)\0003 Create Changelog.sql" Variables="@(Variable)" />     <CallTarget Targets="Update;ImportData"/>     </Target>    <TargetName="Update" DependsOnTargets="CreateUpdateScript">     <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="(local)" Database="$(DatabaseName)" LogOn="$(User)" Password="$(Password)" InputFiles="$(OutputFileName)" Variables="@(Variable)" />   </Target>   <TargetName="CreateUpdateScript">     <ktsc.Build.DBDeploy DbType="mssql"                                        DbConnection="User=$(User);Password=$(Password);Data Source=$(Server);Initial Catalog=$(DatabaseName);"                                        Dir="$(ScriptDirectory)"                                        OutputFile="..\$(OutputFileName)"                                        UndoOutputFile="..\$(UndoFileName)"                                        LastChangeToApply="$(LastChangeToApply)"/>   </Target>     <TargetName="ImportData">     <ItemGroup>       <TestData Include="$(TestDataDirectory)\*.dat"/>     </ItemGroup>     <ExecCommand="$(BCP) $(DatabaseName).dbo.%(TestData.Filename) in&quot;%(TestData.Identity)&quot;$(BCPOptions)"/>   </Target> </Project> Technorati Tags: MSBuild

    Read the article

  • User Input That Involves A ' ' Causes A Substring Out Of Range Error

    - by Greenhouse Gases
    Hi Stackoverflow people. You have already helped me quite a bit but near the end of writing this program I have somewhat of a bug. You see in order to read in city names with a space in from a text file I use a '/' that is then replaced by the program for a ' ' (and when the serializer runs the opposite happens for next time the program is run). The problem is when a user inputs a name too add, search for, or delete that contains a space, for instance 'New York' I get a Debug Assertion Error with a substring out of range expression. I have a feeling it's to do with my correctCase function, or setElementsNull that looks at the string until it experiences a null element in the array, however ' ' is not null so I'm not sure how to fix this and I'm going a bit insane. Any help would be much appreciated. Here is my code: // U08221.cpp : main project file. #include "stdafx.h" #include <_iostream> #include <_string> #include <_fstream> #include <_cmath> using namespace std; class locationNode { public: string nodeCityName; double nodeLati; double nodeLongi; locationNode* Next; locationNode(string nameOf, double lat, double lon) { this->nodeCityName = nameOf; this->nodeLati = lat; this->nodeLongi = lon; this->Next = NULL; } locationNode() // NULL constructor { } void swapProps(locationNode *node2) { locationNode place; place.nodeCityName = this->nodeCityName; place.nodeLati = this->nodeLati; place.nodeLongi = this->nodeLongi; this->nodeCityName = node2->nodeCityName; this->nodeLati = node2->nodeLati; this->nodeLongi = node2->nodeLongi; node2->nodeCityName = place.nodeCityName; node2->nodeLati = place.nodeLati; node2->nodeLongi = place.nodeLongi; } void modify(string name) { this->nodeCityName = name; } void modify(double latlon, int mod) { switch(mod) { case 2: this->nodeLati = latlon; break; case 3: this->nodeLongi = latlon; break; } } void correctCase() // Correct upper and lower case letters of input { int MAX_SIZE = 35; int firstLetVal = this->nodeCityName[0], letVal; int n = 1; // variable for name index from second letter onwards if((this->nodeCityName[0] >90) && (this->nodeCityName[0] < 123)) // First letter is lower case { firstLetVal = firstLetVal - 32; // Capitalise first letter this->nodeCityName[0] = firstLetVal; } while(this->nodeCityName[n] != NULL) { if((this->nodeCityName[n] >= 65) && (this->nodeCityName[n] <= 90)) { if(this->nodeCityName[n - 1] != 32) { letVal = this->nodeCityName[n] + 32; this->nodeCityName[n] = letVal; } } n++; } } }; Here is the main part of the program: // U08221.cpp : main project file. #include "stdafx.h" #include "Locations2.h" #include <_iostream> #include <_string> #include <_fstream> #include <_cmath> using namespace std; #define pi 3.14159265358979323846264338327950288 #define radius 6371 #define gig 1073741824 //size of a gigabyte in bytes int n = 0,x, locationCount = 0, MAX_SIZE = 35 , g = 0, i = 0, modKey = 0, xx; string cityNameInput, alter; char targetCity[35], skipKey = ' '; double lat1, lon1, lat2, lon2, dist, dummy, modVal, result; bool acceptedInput = false, match = false, nodeExists = false;// note: addLocation(), set to true to enable user input as opposed to txt file locationNode *temp, *temp2, *example, *seek, *bridge, *start_ptr = NULL; class Menu { int junction; public: /* Convert decimal degrees to radians */ public: void setElementsNull(char cityParam[]) { int y=0; while(cityParam[y] != NULL) { y++; } while(y < MAX_SIZE) { cityParam[y] = NULL; y++; } } void correctCase(string name) // Correct upper and lower case letters of input { int MAX_SIZE = 35; int firstLetVal = name[0], letVal; int n = 1; // variable for name index from second letter onwards if((name[0] >90) && (name[0] < 123)) // First letter is lower case { firstLetVal = firstLetVal - 32; // Capitalise first letter name[0] = firstLetVal; } while(name[n] != NULL) { if((name[n] >= 65) && (name[n] <= 90)) { letVal = name[n] + 32; name[n] = letVal; } n++; } for(n = 0; targetCity[n] != NULL; n++) { targetCity[n] = name[n]; } } bool nodeExistTest(char targetCity[]) // see if entry is present in the database { match = false; seek = start_ptr; int letters = 0, letters2 = 0, x = 0, y = 0; while(targetCity[y] != NULL) { letters2++; y++; } while(x <= locationCount) // locationCount is number of entries currently in list { y=0, letters = 0; while(seek->nodeCityName[y] != NULL) // count letters in the current name { letters++; y++; } if(letters == letters2) // same amount of letters in the name { y = 0; while(y <= letters) // compare each letter against one another { if(targetCity[y] == seek->nodeCityName[y]) { match = true; y++; } else { match = false; y = letters + 1; // no match, terminate comparison } } } if(match) { x = locationCount + 1; //found match so terminate loop } else{ if(seek->Next != NULL) { bridge = seek; seek = seek->Next; x++; } else { x = locationCount + 1; // end of list so terminate loop } } } return match; } double deg2rad(double deg) { return (deg * pi / 180); } /* Convert radians to decimal degrees */ double rad2deg(double rad) { return (rad * 180 / pi); } /* Do the calculation */ double distance(double lat1, double lon1, double lat2, double lon2, double dist) { dist = sin(deg2rad(lat1)) * sin(deg2rad(lat2)) + cos(deg2rad(lat1)) * cos(deg2rad(lat2)) * cos(deg2rad(lon1 - lon2)); dist = acos(dist); dist = rad2deg(dist); dist = (radius * pi * dist) / 180; return dist; } void serialise() { // Serialize to format that can be written to text file fstream outfile; outfile.open("locations.txt",ios::out); temp = start_ptr; do { for(xx = 0; temp->nodeCityName[xx] != NULL; xx++) { if(temp->nodeCityName[xx] == 32) { temp->nodeCityName[xx] = 47; } } outfile << endl << temp->nodeCityName<< " "; outfile<<temp->nodeLati<< " "; outfile<<temp->nodeLongi; temp = temp->Next; }while(temp != NULL); outfile.close(); } void sortList() // do this { int changes = 1; locationNode *node1, *node2; while(changes > 0) // while changes are still being made to the list execute { node1 = start_ptr; node2 = node1->Next; changes = 0; do { xx = 1; if(node1->nodeCityName[0] > node2->nodeCityName[0]) //compare first letter of name with next in list { node1->swapProps(node2); // should come after the next in the list changes++; } else if(node1->nodeCityName[0] == node2->nodeCityName[0]) // if same first letter { while(node1->nodeCityName[xx] == node2->nodeCityName[xx]) // check next letter of name { if((node1->nodeCityName[xx + 1] != NULL) && (node2->nodeCityName[xx + 1] != NULL)) // check next letter until not the same { xx++; } else break; } if(node1->nodeCityName[xx] > node2->nodeCityName[xx]) { node1->swapProps(node2); // should come after the next in the list changes++; } } node1 = node2; node2 = node2->Next; // move to next pair in list } while(node2 != NULL); } } void initialise() { cout << "Populating List..."; ifstream inputFile; inputFile.open ("locations.txt", ios::in); char inputName[35] = " "; double inputLati = 0, inputLongi = 0; //temp = new locationNode(inputName, inputLati, inputLongi); do { inputFile.get(inputName, 35, ' '); inputFile >> inputLati; inputFile >> inputLongi; if(inputName[0] == 10 || 13) //remove linefeed from input { for(int i = 0; inputName[i] != NULL; i++) { inputName[i] = inputName[i + 1]; } } for(xx = 0; inputName[xx] != NULL; xx++) { if(inputName[xx] == 47) // if it is a '/' { inputName[xx] = 32; // replace it for a space } } temp = new locationNode(inputName, inputLati, inputLongi); if(start_ptr == NULL){ // if list is currently empty, start_ptr will point to this node start_ptr = temp; } else { temp2 = start_ptr; // We know this is not NULL - list not empty! while (temp2->Next != NULL) { temp2 = temp2->Next; // Move to next link in chain until reach end of list } temp2->Next = temp; } ++locationCount; // increment counter for number of records in list } while(!inputFile.eof()); cout << "Successful!" << endl << "List contains: " << locationCount << " entries" << endl; inputFile.close(); cout << endl << "*******************************************************************" << endl << "DISTANCE CALCULATOR v2.0\tAuthors: Darius Hodaei, Joe Clifton" << endl; } void menuInput() { char menuChoice = ' '; while(menuChoice != 'Q') { // Menu if(skipKey != 'X') // This is set by case 'S' below if a searched term does not exist but wants to be added { cout << endl << "*******************************************************************" << endl; cout << "Please enter a choice for the menu..." << endl << endl; cout << "(P) To print out the list" << endl << "(O) To order the list alphabetically" << endl << "(A) To add a location" << endl << "(D) To delete a record" << endl << "(C) To calculate distance between two points" << endl << "(S) To search for a location in the list" << endl << "(M) To check memory usage" << endl << "(U) To update a record" << endl << "(Q) To quit" << endl; cout << endl << "*******************************************************************" << endl; cin >> menuChoice; if(menuChoice >= 97) { menuChoice = menuChoice - 32; // Turn the lower case letter into an upper case letter } } skipKey = ' '; //Reset skipKey so that it does not skip the menu switch(menuChoice) { case 'P': temp = start_ptr; // set temp to the start of the list do { if (temp == NULL) { cout << "You have reached the end of the database" << endl; } else { // Display details for what temp points to at that stage cout << "Location : " << temp->nodeCityName << endl; cout << "Latitude : " << temp->nodeLati << endl; cout << "Longitude : " << temp->nodeLongi << endl; cout << endl; // Move on to next locationNode if one exists temp = temp->Next; } } while (temp != NULL); break; case 'O': { sortList(); // pass by reference??? cout << "List reordered alphabetically" << endl; } break; case 'A': char cityName[35]; double lati, longi; cout << endl << "Enter the name of the location: "; cin >> cityName; for(xx = 0; cityName[xx] != NULL; xx++) { if(cityName[xx] == 47) // if it is a '/' { cityName[xx] = 32; // replace it for a space } } if(!nodeExistTest(cityName)) { cout << endl << "Please enter the latitude value for this location: "; cin >> lati; cout << endl << "Please enter the longitude value for this location: "; cin >> longi; cout << endl; temp = new locationNode(cityName, lati, longi); temp->correctCase(); //start_ptr allignment if(start_ptr == NULL){ // if list is currently empty, start_ptr will point to this node start_ptr = temp; } else { temp2 = start_ptr; // We know this is not NULL - list not empty! while (temp2->Next != NULL) { temp2 = temp2->Next; // Move to next link in chain until reach end of list } temp2->Next = temp; } ++locationCount; // increment counter for number of records in list cout << "Location sucessfully added to the database! There are " << locationCount << " location(s) stored" << endl; } else { cout << "Node is already present in the list and so cannot be added again" << endl; } break; case 'D': { junction = 0; locationNode *place; cout << "Enter the name of the city you wish to remove" << endl; cin >> targetCity; setElementsNull(targetCity); correctCase(targetCity); for(xx = 0; targetCity[xx] != NULL; xx++) { if(targetCity[xx] == 47) { targetCity[xx] = 32; } } if(nodeExistTest(targetCity)) //if this node does exist { if(seek == start_ptr) // if it is the first in the list { junction = 1; } if(seek->Next == NULL) // if it is last in the list { junction = 2; } switch(junction) // will alter list accordingly dependant on where the searched for link is { case 1: start_ptr = start_ptr->Next; delete seek; --locationCount; break; case 2: place = seek; seek = bridge; seek->Next = NULL; delete place; --locationCount; break; default: bridge->Next = seek->Next; delete seek; --locationCount; break; } cout << endl << "Link deleted. There are now " << locationCount << " locations." << endl; } else { cout << "That entry does not currently exist" << endl << endl << endl; } } break; case 'C': { char city1[35], city2[35]; cout << "Enter the first city name" << endl; cin >> city1; setElementsNull(city1); correctCase(targetCity); if(nodeExistTest(city1)) { lat1 = seek->nodeLati; lon1 = seek->nodeLongi; cout << "Lati = " << seek->nodeLati << endl << "Longi = " << seek->nodeLongi << endl << endl; } cout << "Enter the second city name" << endl; cin >> city2; setElementsNull(city2); correctCase(targetCity); if(nodeExistTest(city2)) { lat2 = seek->nodeLati; lon2 = seek->nodeLongi; cout << "Lati = " << seek->nodeLati << endl << "Longi = " << seek->nodeLongi << endl << endl; } result = distance (lat1, lon1, lat2, lon2, dist); cout << "The distance between these two locations is " << result << " kilometres." << endl; } break; case 'S': { char choice; cout << "Enter search term..." << endl; cin >> targetCity; setElementsNull(targetCity); correctCase(targetCity); if(nodeExistTest(targetCity)) { cout << "Latitude: " << seek->nodeLati << endl << "Longitude: " << seek->nodeLongi << endl; } else { cout << "Sorry, that city is not currently present in the list." << endl << "Would you like to add this city now Y/N?" << endl; cin >> choice; /*while(choice != ('Y' || 'N')) { cout << "Please enter a valid choice..." << endl; cin >> choice; }*/ switch(choice) { case 'Y': skipKey = 'X'; menuChoice = 'A'; break; case 'N': break; default : cout << "Invalid choice" << endl; break; } } break; } case 'M': { cout << "Locations currently stored: " << locationCount << endl << "Memory used for this: " << (sizeof(start_ptr) * locationCount) << " bytes" << endl << endl << "You can store " << ((gig - (sizeof(start_ptr) * locationCount)) / sizeof(start_ptr)) << " more locations" << endl ; break; } case 'U': { cout << "Enter the name of the Location you would like to update: "; cin >> targetCity; setElementsNull(targetCity); correctCase(targetCity); if(nodeExistTest(targetCity)) { cout << "Select (1) to alter City Name, (2) to alter Longitude, (3) to alter Latitude" << endl; cin >> modKey; switch(modKey) { case 1: cout << "Enter the new name: "; cin >> alter; cout << endl; seek->modify(alter); break; case 2: cout << "Enter the new latitude: "; cin >> modVal; cout << endl; seek->modify(modVal, modKey); break; case 3: cout << "Enter the new longitude: "; cin >> modVal; cout << endl; seek->modify(modVal, modKey); break; default: break; } } else cout << "Location not found" << endl; break; } } } } }; int main(array<System::String ^> ^args) { Menu mm; //mm.initialise(); mm.menuInput(); mm.serialise(); }

    Read the article

  • Using jQuery and OData to Insert a Database Record

    - by Stephen Walther
    In my previous blog entry, I explored two ways of inserting a database record using jQuery. We added a new Movie to the Movie database table by using a generic handler and by using a WCF service. In this blog entry, I want to take a brief look at how you can insert a database record using OData. Introduction to OData The Open Data Protocol (OData) was developed by Microsoft to be an open standard for communicating data across the Internet. Because the protocol is compatible with standards such as REST and JSON, the protocol is particularly well suited for Ajax. OData has undergone several name changes. It was previously referred to as Astoria and ADO.NET Data Services. OData is used by Sharepoint Server 2010, Azure Storage Services, Excel 2010, SQL Server 2008, and project code name “Dallas.” Because OData is being adopted as the public interface of so many important Microsoft technologies, it is a good protocol to learn. You can learn more about OData by visiting the following websites: http://www.odata.org http://msdn.microsoft.com/en-us/data/bb931106.aspx When using the .NET framework, you can easily expose database data through the OData protocol by creating a WCF Data Service. In this blog entry, I will create a WCF Data Service that exposes the Movie database table. Create the Database and Data Model The MoviesDB database is a simple database that contains the following Movies table: You need to create a data model to represent the MoviesDB database. In this blog entry, I use the ADO.NET Entity Framework to create my data model. However, WCF Data Services and OData are not tied to any particular OR/M framework such as the ADO.NET Entity Framework. For details on creating the Entity Framework data model for the MoviesDB database, see the previous blog entry. Create a WCF Data Service You create a new WCF Service by selecting the menu option Project, Add New Item and selecting the WCF Data Service item template (see Figure 1). Name the new WCF Data Service MovieService.svc. Figure 1 – Adding a WCF Data Service Listing 1 contains the default code that you get when you create a new WCF Data Service. There are two things that you need to modify. Listing 1 – New WCF Data Service File using System; using System.Collections.Generic; using System.Data.Services; using System.Data.Services.Common; using System.Linq; using System.ServiceModel.Web; using System.Web; namespace WebApplication1 { public class MovieService : DataService< /* TODO: put your data source class name here */ > { // This method is called only once to initialize service-wide policies. public static void InitializeService(DataServiceConfiguration config) { // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc. // Examples: // config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead); // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All); config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2; } } } First, you need to replace the comment /* TODO: put your data source class name here */ with a class that represents the data that you want to expose from the service. In our case, we need to replace the comment with a reference to the MoviesDBEntities class generated by the Entity Framework. Next, you need to configure the security for the WCF Data Service. By default, you cannot query or modify the movie data. We need to update the Entity Set Access Rule to enable us to insert a new database record. The updated MovieService.svc is contained in Listing 2: Listing 2 – MovieService.svc using System.Data.Services; using System.Data.Services.Common; namespace WebApplication1 { public class MovieService : DataService<MoviesDBEntities> { public static void InitializeService(DataServiceConfiguration config) { config.SetEntitySetAccessRule("Movies", EntitySetRights.AllWrite); config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2; } } } That’s all we have to do. We can now insert a new Movie into the Movies database table by posting a new Movie to the following URL: /MovieService.svc/Movies The request must be a POST request. The Movie must be represented as JSON. Using jQuery with OData The HTML page in Listing 3 illustrates how you can use jQuery to insert a new Movie into the Movies database table using the OData protocol. Listing 3 – Default.htm <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>jQuery OData Insert</title> <script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.js" type="text/javascript"></script> <script src="Scripts/json2.js" type="text/javascript"></script> </head> <body> <form> <label>Title:</label> <input id="title" /> <br /> <label>Director:</label> <input id="director" /> </form> <button id="btnAdd">Add Movie</button> <script type="text/javascript"> $("#btnAdd").click(function () { // Convert the form into an object var data = { Title: $("#title").val(), Director: $("#director").val() }; // JSONify the data var data = JSON.stringify(data); // Post it $.ajax({ type: "POST", contentType: "application/json; charset=utf-8", url: "MovieService.svc/Movies", data: data, dataType: "json", success: insertCallback }); }); function insertCallback(result) { // unwrap result var newMovie = result["d"]; // Show primary key alert("Movie added with primary key " + newMovie.Id); } </script> </body> </html> jQuery does not include a JSON serializer. Therefore, we need to include the JSON2 library to serialize the new Movie that we wish to create. The Movie is serialized by calling the JSON.stringify() method: var data = JSON.stringify(data); You can download the JSON2 library from the following website: http://www.json.org/js.html The jQuery ajax() method is called to insert the new Movie. Notice that both the contentType and dataType are set to use JSON. The jQuery ajax() method is used to perform a POST operation against the URL MovieService.svc/Movies. Because the POST payload contains a JSON representation of a new Movie, a new Movie is added to the database table of Movies. When the POST completes successfully, the insertCallback() method is called. The new Movie is passed to this method. The method simply displays the primary key of the new Movie: Summary The OData protocol (and its enabling technology named WCF Data Services) works very nicely with Ajax. By creating a WCF Data Service, you can quickly expose your database data to an Ajax application by taking advantage of open standards such as REST, JSON, and OData. In the next blog entry, I want to take a closer look at how the OData protocol supports different methods of querying data.

    Read the article

< Previous Page | 284 285 286 287 288 289 290 291 292 293 294 295  | Next Page >