Database

Eddy Vluggen20-Feb-11 5:03

Eddy Vluggen

20-Feb-11 5:03

AhmedMasum wrote:
Suppose some one purchased a ticket online. [Here the value of a column is pending]
If the person does not pay the cash money within 24 hours than the ticket will be automatically cancelled. [The value of the status column will be cancelled after 24 hours]

It's not pending, it's reserved from that moment forward. Otherwise you could run into the problem that there's more pending than there's actually available.

AhmedMasum wrote:
How can I do automatic update of a table after 24 hours???

I'd run through it once an hour, processing the valid payments (sending out a ticket to an address) and the invalid ones.

I are Troll Suspicious | :suss:

PIEBALDconsult21-Feb-11 8:21

PIEBALDconsult

21-Feb-11 8:21

I wouldn't; I'd design the system so that that isn't required. The Pending column should be a date column that is set to the time when it will expire if not Purchased. Then, when seeking available tickets, look for any where the Pending field is null or less than the current time and Purchased (another date field) is also null.

Or name them Ordered and Purchased or something, but I wouldn't put them in a single Status column, Status could be calculated from a number of fields, including Shipped.

AhmedMasum22-Feb-11 1:28

AhmedMasum

22-Feb-11 1:28

Thanks for your nice suggestion!!!.
My above case is just an example.
Actually I need something that will do insert/update/delete on a table every week or after a specific period of time.
Is there any type of trigger can do this type of operation??

Cannot use mysql in linux fedora14. Do we have to install it or the bydefault coming is fine.

PIEBALDconsult22-Feb-11 10:37

PIEBALDconsult

22-Feb-11 10:37

Use a Windows Service (for very frequent actions) or a Windows Scheduled Task (for less-frequent actions).

cmaheshwari1619-Feb-11 2:30

cmaheshwari16

19-Feb-11 2:30

Hi

I have not installed mysql on my own, its the default one that is coming with fedora14 at /usr/local/mysql

I am giving you some screenshots, please check if you can find out something.
*********************************************************************************************************************************************************

The first issue is while I login using command
mysql -u root -p
it asks for password I enter, root123
then gives the error
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

let me give you the full flow:

I am using fedora 14
_________________________________________________________________________________________________________
in root I fire the below commands:
[starting the mysql]

[root@chandan init.d]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]

now from my local account I try to login to sql,

[chadan@chandan ~]$ mysql -u root -p
Enter password:

[i give password as root123

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

But when I login using command

[chadan@chandan ~]$ mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.51 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

[here I am able to login to server but when I try to create database then it gives error]

mysql> create database dummy;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'dummy'

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+

**********************************************************************************************************************************

NOW THE SECOND ISSUE WHEN I START THE SERVER IN DIFFERENT WAY.

[root@chandan init.d]# mysqld_safe --skip-grant-tables &

[1] 29006
[root@chandan init.d]# 110219 18:33:25 mysqld_safe Logging to '/var/log/mysqld.log'.
110219 18:33:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Now from my account I try to login

[chadan@chandan ~]$ mysql -u root -p

Enter password: fsklfjfljf
here whatever password I give I am able to login

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

now when I check mysql database
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.01 sec)

mysql> select host,user,password,Insert_priv,update_priv from user;
+---------------------- +------+---------- +-------------+-------------+
| host | user | password | Insert_priv | update_priv |
+---------------------- +------+---------- +-------------+-------------+
| localhost | root | root123 | Y | Y |
| username.corp.org.in | root | root123 | Y | Y |
| 127.0.0.1 | root | root123 | Y | Y |
| localhost | | | N | N |
| username.corp.org.in | | N | N |
+----------------------+------+----------+-------------+-------------+
5 rows in set (0.00 sec)

[here you can see that the password for root user is root123,
now here if i try to create database i am able to do that

mysql> create database dummy;

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dummy |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

I dont know how to solve this also I tried to create another user but it denied

mysql> CREATE USER 'seen' IDENTIFIED BY 'seen123';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Re: Cannot use mysql in linux fedora14. Do we have to install it or the bydefault coming is fine.

ScottM121-Feb-11 18:57

ScottM1

21-Feb-11 18:57

Check this[^], looks like it could be that the root password could be incorrect. The MySQL root password could be different to your OS root password.
It could also be that there is a port conflict which is why it works when you start it in a different way.

how to change a color of particular word in the row in ssrs reports

vinu.111119-Feb-11 0:51

vinu.1111

19-Feb-11 0:51

hi..

i've created an report in which i want to change the color of
the particular word i've four words which i want to change the
color
john,steve,mike,and danial..
for these name where ever they appear in the row the color should change
is it possible..

??

Re: how to change a color of particular word in the row in ssrs reports

Wendelius19-Feb-11 1:17

SQL Express Redistribution

19-Feb-11 1:17

Hi,

You can use BackgroundColor-property to change the color and define an expression how the color is set. What comes to the color itself, you can for example resolve the color in the formula or add an extra field to your query which defines the color, like:

SELECT ...
       CASE
          WHEN LOWER(table.NameField) LIKE '%john%' THEN 'Red'
          WHEN LOWER(table.NameField) LIKE '%steve%' THEN 'Blue'
          ...
          ELSE 'White'
       END AS BackgroundColor,
       ...
FROM ...

Now each row should have a color field which you can use in the expression for the BackgroundColor-property. If you want to do this in more elegant way you can create a table for the name/color pairs and use that table for lookups and possibly create a small T-SQL function for the logic.

The need to optimize rises from a bad design.My articles[^]

εїзεїзεїз18-Feb-11 22:00

18-Feb-11 22:00

I have a Software which is having SQL Server Database, Most of the users don't want to buy SQL Server, So as Microsoft has SQL Express, Can i bundled SQL Express in my Setup CD,
means can i redistribute it ?

SOFTDEV

Luck in life always exists in the form of an abstract class that cannot be instantiated directly and needs to be inherited by hard work and dedication.

Wendelius18-Feb-11 22:15

18-Feb-11 22:15

Yes you can but you have to register for redistrubution rights. See: Register for SQL Server 2008 Express Redistribution Rights[^]

The need to optimize rises from a bad design.My articles[^]

εїзεїзεїз19-Feb-11 18:29

19-Feb-11 18:29

Thanks

SOFTDEV

Luck in life always exists in the form of an abstract class that cannot be instantiated directly and needs to be inherited by hard work and dedication.

Wendelius20-Feb-11 7:05

20-Feb-11 7:05

No problem Smile | :)

The need to optimize rises from a bad design.My articles[^]

PIEBALDconsult19-Feb-11 5:24

PIEBALDconsult

19-Feb-11 5:24

Just put instructions in the documentation.

Wendelius19-Feb-11 6:58

19-Feb-11 6:58

Good advice Smile | :)

The need to optimize rises from a bad design.My articles[^]

εїзεїзεїз19-Feb-11 18:29

19-Feb-11 18:29

Thanks

SOFTDEV

Luck in life always exists in the form of an abstract class that cannot be instantiated directly and needs to be inherited by hard work and dedication.

εїзεїзεїз19-Feb-11 18:30

Insert Record into Access

19-Feb-11 18:30

Thanks.

SOFTDEV

Luck in life always exists in the form of an abstract class that cannot be instantiated directly and needs to be inherited by hard work and dedication.

Brad Fackrell18-Feb-11 4:52

Brad Fackrell

18-Feb-11 4:52

I’m trying to insert a new record into an MS Access (2002-2003) database using Perl. My instructor's example has me getting the last record ID and using that to generate the next ID.

Two things:
1- When I do this the way the instructor illustrates (based on existing ID) using this code:

if($db->Sql("SELECT MAX(ID) lastID FROM Test"))<br />
{<br />
print "SQL Error: " . $db->Error() . "\n";<br />
$db->Close();<br />
exit;<br />
}

Can anybody tell me why I'm getting this error:

SQL Error: [-3100] [1] [0] "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'MAX(ID) lastID'."

2- If my ID field is set to “AutoNumber” shouldn’t I be able to simply insert and new record (with no ID)? I’ve attempted that with no success.

Luc Pattyn18-Feb-11 5:23

Luc Pattyn

18-Feb-11 5:23

hi,

1. I think you need an as:

SELECT MAX(ID) AS lastID FROM Test

2. I should think so. And having two separate DB operations to first fetch then use MAX(ID) seems like a recipe for failure as soon as other users are also operating the DB.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

Brad Fackrell18-Feb-11 5:41

Brad Fackrell

18-Feb-11 5:41

Thanks Luc. Aliasing did the trick.

Wendelius18-Feb-11 5:42

18-Feb-11 5:42

Luc Pattyn wrote:
having two separate DB operations to first fetch then use MAX(ID) seems like a recipe for failure

Indeed it does. Using for example @@IDENTITY would be safer.

The need to optimize rises from a bad design.My articles[^]

Wendelius18-Feb-11 5:26

18-Feb-11 5:26

Hi
For the first question: You could try adding the keyword AS for the column:

if($db->Sql("SELECT MAX(ID) AS lastID FROM Test"))

And for the second question. Yes if the field is autonumbered, you don't have to mention it in an INSERT statement just like you don't have to mention any field that has a default value. However, if you're later going to add something for this row to a child table of this table you need to know the value for the foreign key in the child table.

The need to optimize rises from a bad design.My articles[^]

Hi everyone i am a new comer for this forum ,I have a small problem with format the xml to a table in SQL SERVER stored procedure [modified]

Brad Fackrell18-Feb-11 5:45

Brad Fackrell

18-Feb-11 5:45

Thanks Mika. As both you and Luc suggested, using AS worked by referencing the existing ID's. I'd much rather not do it that way but when I try to insert a record with no ID my table does not update. I'll try to figure that out latter since I can get by with Aliasing for now. Wink | ;)

Thabo@Codeproject18-Feb-11 4:52

Thabo@Codeproject

18-Feb-11 4:52

This is the senario, i am trying to copy a table and store that table into a XML variable(Not physically xml file) and read that XML variable ,at the same time also trying to retrive and insert all data to another table.The stored procedure is given below

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:     Thabo
-- Create date: 18.02.2011
-- Description:	TEST XML
-- =============================================
CREATE PROCEDURE sppassingStoredProcedue
	 DECLARE @x XML 
AS
BEGIN

SET NOCOUNT ON;
SET @x=(SELECT PramryKeyForThisTable,AutoNumberColumn,Data1,Data2,Data3 FROM tblInsertTableForAutoNumber FOR XML AUTO ,TYPE ,ELEMENTS XSINIL,ROOT ('TABLEROOT'))

INSERT INTO CopyOftblInserTableForAutonumber
SELECT
	tab.col.value('@PrimaryKeyForThisTable[1]','SMALLINT') AS PramryKeyForThisTable,
	tab.col.value('@AutoNumberColumn[1]','SMALLINT')AS AutoNumberColumn,
	tab.col.value('@Data1[1]','VARCHAR(50)') AS Data1,
	tab.col.value('@Data2[1]','VARCHAR(50)') AS Data2,
	tab.col.value('@Data3[1]','VARCHAR(50)') AS Data3
FROM @x.nodes('//TABLEROOT/tblInsertTableForAutoNumber')AS tab(Col)	
	
END

Actually the second table is the copy of the first table.It has same stucure of the first table
The table structure is,

colum name datatype
----------- --------
PramryKeyForThisTable smallint
AutoNumberColumn smallint
Data1 varchar(50)
Data2 varchar(50)
Data3 varchar(50)

I have got different different error messages every time.i hve got this error message for above cording.

Msg 156, Level 15, State 1, Procedure sppassingStoredProcedue, Line 7
Incorrect syntax near the keyword 'DECLARE'.
Msg 156, Level 15, State 1, Procedure sppassingStoredProcedue, Line 8
Incorrect syntax near the keyword 'AS'."

I couldnt figure out how to do this. Can any one pls give a good solution for this.

Thabo

-- Modified Friday, February 18, 2011 11:33 AM

Re: Hi everyone i am a new comer for this forum ,I have a small problem with format the xml to a table in SQL SERVER stored procedure

Wendelius18-Feb-11 5:21