Re: Maintain max x records - Database Discussion Boards

Re: Maintain max x records

Eduard Keilholz30-Nov-10 20:16

Eduard Keilholz

30-Nov-10 20:16

Thanks David!

I don't need to guarantee a max number of records.

This[^] post explains what I want my software to do

.: I love it when a plan comes together :.
http://www.zonderpunt.nl

Re: Maintain max x records

Eduard Keilholz30-Nov-10 2:43

Eduard Keilholz

30-Nov-10 2:43

Thanks for the reply, but my question was not how to accomplish the removal but wether my solution using a stored procedure was OK.

.: I love it when a plan comes together :.
http://www.zonderpunt.nl

Re: Maintain max x records

Mycroft Holmes30-Nov-10 11:25

Mycroft Holmes

30-Nov-10 11:25

I do wonder about the business reason to enforce this record limit, that would drive the decision to use a proc or a scheduled job.

Never underestimate the power of human stupidity
RAH

Re: Maintain max x records

Eduard Keilholz30-Nov-10 20:14

Eduard Keilholz

30-Nov-10 20:14

Hey!

I have 8 servers which I want to report their performance. I use performance counters to aquire system information. Depending on the counter's category and name, I want to counter to store it's value every second, or (if less important) with a lower interval (say a minute).

I want to keep the 100 latest inserted records per performance counter to report the machine's performance. Since some counters store their value each second the database table may grow rapidly. I expect about 160 performance counters running at the same time measuring performance of 8 different servers.

So there's no need to guarantee a max number of records, I can select the latest x records when reporting and a job may clean up the 'old' data. I also may want to switch to keeping the latest 1000 records or something but I don't know about that yet.

I think the job is the best performing solution for this...

Thanks guys!

.: I love it when a plan comes together :.
http://www.zonderpunt.nl

Re: Maintain max x records

Spectre_0011-Dec-10 1:41

Spectre_001

1-Dec-10 1:41

Unfortunately, the very act of recording performance data has an impact on system performance. The best approach imho for performance reporting, if possible, is to periodically do performance analysis on the business data recorded by the system during the normal operation.

Kevin Rucker, Application Programmer
QSS Group, Inc.
United States Coast Guard OSC
Kevin.D.Rucker@uscg.mil

"Programming is an art form that fights back." -- Chad Hower

Re: Maintain max x records

Pete O'Hanlon30-Nov-10 4:55

Pete O'Hanlon

30-Nov-10 4:55

You could always do this via a trigger on the table - as long as you have some form of feature to sort on to identify older posts.

I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx

Re: Maintain max x records

PIEBALDconsult30-Nov-10 17:57

PIEBALDconsult

30-Nov-10 17:57

I just remembered a third-party system I had to work with a few years ago. There was a particular table that always held 8000 records -- they got reused.

Re: Maintain max x records

Dwayne J. Baldwin30-Nov-10 20:40

Dwayne J. Baldwin

30-Nov-10 20:40

The neatest way would be to pre-allocate (insert) 100 (or 500) records in your table. Your stored proc should simply update the oldest record using an implicit autocommit transaction. The data type used to determine the oldest record depends on the possible update frequency.

This guarantees your original request of maintaining max x records at all times.

Dwayne J. Baldwin

Re: Maintain max x records

Member 40648382-Dec-10 0:31

Member 4064838

2-Dec-10 0:31

Check out RRDtool
http://oss.oetiker.ch/rrdtool/[^]

It's exactly what you need, it's a round robin database (i.e. when it has reached the end, it overwrites the first entries) and it is specifically designed for graphing sensors from various sources (server load, network traffic, temperature sensors, etc).

Good luck!

Re: Maintain max x records

Eduard Keilholz2-Dec-10 1:05

Eduard Keilholz

2-Dec-10 1:05

Cool, i'll take a peak, thanks a lot!

.: I love it when a plan comes together :.
http://www.zonderpunt.nl

best way to return is_exists

Jassim Rahma29-Nov-10 22:35

Jassim Rahma

29-Nov-10 22:35

I am using C# with mySQL.

I want to know what's the best way to return is_exists value? I use to that with RETURN 999 in SQL Server and handle the value in SqlParameter.ReturnValue

ca you please guide..

Thanks

default blob

Jassim Rahma29-Nov-10 22:30

Jassim Rahma

29-Nov-10 22:30

I have a blob field in mySQL database to store images.

Is it possible to assign a default blob so a specific image will be the default?

how?

Re: default blob

David Skelly30-Nov-10 1:45

David Skelly

30-Nov-10 1:45

BLOB columns cannot have a default value in MySQL as far as I am aware.

mysql problem

mylogics28-Nov-10 23:16

mylogics

28-Nov-10 23:16

hi am using stored procedure to submit a registration form.
i have checked lot of time but get the same error can yuo plz guide me.
Message = "Key cannot be null.\r\nParameter name: key"

Re: mysql problem

Jörgen Andersson28-Nov-10 23:49

Jörgen Andersson

28-Nov-10 23:49

The message can't be much clearer than that. You're trying to insert a null value into a key column.

To help you further we need more info, such as the table structure, the stored procedure and how it's used.

"When did ignorance become a point of view" - Dilbert

Re: mysql problem

RaviRanjanKr29-Nov-10 0:07

RaviRanjanKr

29-Nov-10 0:07

mylogics wrote:
i have checked lot of time but get the same error can yuo plz guide me.

I am not getting your problem, please be clear about your question.
Provide code which throws exception and what exception you are getting.

we can justice with your question when you did first
Smile | :)

Re: mysql problem [modified]

mylogics29-Nov-10 0:46

mylogics

29-Nov-10 0:46

i have a stored procedure named insertreg:

DELIMITER $$DROP PROCEDURE IF EXISTS `naukri4u`.`insertreg`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertreg`(emailaddress varchar(50),firstname varchar(50),lastname varchar(50),username varchar(50),password varchar(50),conformpassword varchar(50),mobile varchar(50),currentloc varchar(50),prefferedjobloc varchar(50),gender varchar(50),totalexp varchar(50),industry varchar(50),function varchar(50),keyskills varchar(50),highestqual varchar(50),yearofpass varchar(50),institute varchar(50),resumetitle varchar(50),resume varchar(50))
BEGIN
insert into registration(emailaddress,firstname,lastname,username,password,conformpassword,mobile,currentloc,prefferedjobloc,gender,totalexp,industry,function,keyskills,highestqual,yearofpass,institute,resumetitle,resume)
values(emailaddress,firstname,lastname,username,password,conformpassword,mobile,currentloc,prefferedjobloc,gender,totalexp,industry,function,keyskills,highestqual,yearofpass,institute,resumetitle,resume);
END$$DELIMITER ;

i want to submit a regisration form using this stored proc.this registration table have primary key reg_id which auto increments so
i have not taken it in my query.the code i have written is:

public DataSet insert(Common.com_registration objcom_reg)
   {
       registration reg = new registration();
       try
       {
                       DbParameter dbparama0 = DAC.Parameter("emailaddress", objcom_reg.Emailaddress, DbType.String, ParameterDirection.Input);
           DbParameter dbparama1 = DAC.Parameter("firstname", objcom_reg.Firstname, DbType.String, ParameterDirection.Input);
           DbParameter dbparama2 = DAC.Parameter("lastname", objcom_reg.Lastname, DbType.String, ParameterDirection.Input);
           DbParameter dbparama3 = DAC.Parameter("username", objcom_reg.Username, DbType.String, ParameterDirection.Input);
           DbParameter dbparama4 = DAC.Parameter("password", objcom_reg.Password, DbType.String, ParameterDirection.Input);
           DbParameter dbparama5 = DAC.Parameter("conformpassword", objcom_reg.Conformpassword, DbType.String, ParameterDirection.Input);
           DbParameter dbparama6 = DAC.Parameter("mobile", objcom_reg.Mobile, DbType.String, ParameterDirection.Input);
           DbParameter dbparama7 = DAC.Parameter("currentloc", objcom_reg.Currentlocation, DbType.String, ParameterDirection.Input);
           DbParameter dbparama8 = DAC.Parameter("prefferedjobloc", objcom_reg.Prefferedjobloc, DbType.String, ParameterDirection.Input);
           DbParameter dbparama9 = DAC.Parameter("gender", objcom_reg.Gender, DbType.String, ParameterDirection.Input);
           DbParameter dbparama10 = DAC.Parameter("totalexp", objcom_reg.Totalexp, DbType.String, ParameterDirection.Input);
           DbParameter dbparama11= DAC.Parameter("industry", objcom_reg.Industry, DbType.String, ParameterDirection.Input);
           DbParameter dbparama12= DAC.Parameter("function", objcom_reg.Function, DbType.String, ParameterDirection.Input);
           DbParameter dbparama13= DAC.Parameter("keyskills", objcom_reg.Keyskills, DbType.String, ParameterDirection.Input);
           DbParameter dbparama14= DAC.Parameter("highestqual", objcom_reg.Highestqual, DbType.String, ParameterDirection.Input);
           DbParameter dbparama15= DAC.Parameter("yearofpass", objcom_reg.Yearofpassing, DbType.String, ParameterDirection.Input);
           DbParameter dbparama16= DAC.Parameter("institute", objcom_reg.Institute, DbType.String, ParameterDirection.Input);
           DbParameter dbparama17= DAC.Parameter("resumetitle", objcom_reg.Resumetitle, DbType.String, ParameterDirection.Input);
           DbParameter dbparama18= DAC.Parameter("resume", objcom_reg.Resume, DbType.String, ParameterDirection.Input);
           DbParameter[] dbparamcoll = new DbParameter[] {dbparama0,dbparama1,dbparama2,dbparama3,dbparama4,dbparama5,dbparama6,dbparama7,dbparama8,dbparama9,dbparama10,dbparama11,
           dbparama12,dbparama13,dbparama14,dbparama15,dbparama16,dbparama17,dbparama18};

           DbParameter dbParam0 = DAC.Parameter("v_userid", objcom_reg.Emailaddress, DbType.String, ParameterDirection.Input);
           DbParameter dbParam1 = DAC.Parameter("v_headline", objcom_reg.Emailaddress, DbType.String, ParameterDirection.Input);
           DbParameter[] dbParamColl = new DbParameter[] { dbParam0, dbParam1 };


           return DAC.SelectDS("insertreg", dbparamcoll);

       }
       catch(Exception exp)
       {
           throw exp;
       }

i am passing a object which is initialized with all the values from my registration form.i am calling this insert function
at my registratin form.hope now u r clear with my prob.

modified on Monday, November 29, 2010 6:59 AM

Re: mysql problem

Mycroft Holmes29-Nov-10 0:53

Mycroft Holmes

29-Nov-10 0:53

Put a break point in the code and check each objcom_reg. value, see if a string value is not initialised and is therefore null.

Never underestimate the power of human stupidity
RAH

Re: mysql problem

mylogics29-Nov-10 0:58

mylogics

29-Nov-10 0:58

i have chechked several time sthe code is correct.evwerythinworks finebut the error comes
at line:

return DAC.SelectDS("insertreg", dbparamcoll);

afterb this line the control skip to exception block.

Re: mysql problem

Luc Pattyn29-Nov-10 2:54

Luc Pattyn

29-Nov-10 2:54

and what are dbparama2,dbparama3,dbparama4,dbparama5,dbparama6,dbparama7,dbparama8,dbparama9,dbparama10,dbparama11,dbparama12,dbparama13,dbparama14,dbparama15,dbparama16,dbparama17,dbparama18?

Confused | :confused:

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Re: mysql problem

Mycroft Holmes29-Nov-10 3:33

Mycroft Holmes

29-Nov-10 3:33

Hasn't really worked out the array thingy yet!

Never underestimate the power of human stupidity
RAH

Re: mysql problem [modified]

Jörgen Andersson29-Nov-10 4:44

Jörgen Andersson

29-Nov-10 4:44

<edit>~~You are setting dbparamcoll twice but with different parameters. The first setting is never used.~~</edit>
Ok, it's official, I'm blind like a bat.

"When did ignorance become a point of view" - Dilbert

modified on Monday, November 29, 2010 11:18 AM

Re: mysql problem

Luc Pattyn29-Nov-10 5:03

Luc Pattyn

29-Nov-10 5:03

actually there are two arrays with different casing, and it is the second one that isn't used.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

Re: mysql problem

Jörgen Andersson29-Nov-10 5:16

Jörgen Andersson

29-Nov-10 5:16

Crap, That's a sign that I need to go home now.

"When did ignorance become a point of view" - Dilbert

Re: mysql problem

RyanEK29-Nov-10 14:47

RyanEK

29-Nov-10 14:47

I suspect your insertreg procedure is not providing a value for a key column.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.