I'm having trouble thinking this out. I'm trying to create a service fetcher.
I have a separate text file that contains the list of services the main program can use as a name and location. I use a filereader in java to open and parse through this file and create service objects and adding these services to an ArrayList<Service> services, (services.add(new Service(name, location) ).
So now, after the array list is created, the Service Fetcher is passed an integer from the session manager and I'm using this integer (required by design I was given) to check the index of the array list and when it finds the appropriate service, it uses the location for that object to execute that class.
Right now, Open account and Account Management are empty files, but Admin Management has it's own main method. So my question is, how do I actually get it to execute that module? Is it something like
Process P = Runtime.getRuntime().exec( services.get( //this integer/index ) );
Problem is I need the time, and I can't use that as a default value because it's not a static or whatever excuse they've been using for 5 years.
T-SQL (SQL Server)
CREATE TABLE MyTable (
id INT IDENTITY(1,1) PRIMARY KEY,
entered DATETIME NOT NULL DEFAULT GETDATE()
);
MySQL
CREATE TABLE MyTable (
id INT AUTO_INCREMENT PRIMARY KEY,
entered DATETIME NOT NULL DEFAULT /* can't use functions here for some reason */,
PRIMARY KEY(id)
);
I guess MySQL thinks it's a unique snowflake ? I don't know, I can't find a good solution to this. I'm assuming I need a trigger of some variety. No idea how to accomplish this in MySQL.
bowen on
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
I wish I had my textbook on me. It probably has the answer, but I loaned it to my partner.
My jQuery Mobile phone app is the funnest thing I've ever learned, but can anyone tell me what's wrong with this PDO insert? I have the phone app successfully using the same php files all all but my scheduler thingy.
CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
FOR EACH ROW SET NEW.dateAdded = NOW();
That is by far one of the most ridiculous things I have seen ... course I haven't used mySQL in almost a decade.
CREATE TABLE MyTable (
id INT AUTO_INCREMENT PRIMARY KEY,
entered DATETIME NOT NULL DEFAULT /* can't use functions here for some reason */,
PRIMARY KEY(id)
);
CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `MyTable`
FOR EACH ROW SET NEW.entered= NOW();
Would that be the applicable code modified for my specific example? Problem was I can't use timestamp mainly because I needed like 5-6 datetime variables and our old system defaults these to "NOW" when a semi-complete entry was added.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
it looks right to me? I don't know crap about mySQL. After you mentioned you can't use function defaults, I couldn't resist finding out what the SOP was for it. Now if you have a SQL Server question, I could definitely help.
Someone mentioned fun tools previously -- Fiddler is great.
it looks right to me? I don't know crap about mySQL. After you mentioned you can't use function defaults, I couldn't resist finding out what the SOP was for it. Now if you have a SQL Server question, I could definitely help.
Someone mentioned fun tools previously -- Fiddler is great.
Yeah, you can't default any date/time columns in MySQL except the timestamp.
Why do you need to though? Usually isn't necessary to have it implicit when it isn't a "timestamp" and all.
0
Options
Monkey Ball WarriorA collection of mediocre hatsSeattle, WARegistered Userregular
edited April 2012
So I'm taking database system design right now, which means we're all learning SQL. Yesterday we had a "lab" where we all went to the computer lab, she gave us a script that filled in a database, and asked us to create SQL queries on the data. The thing is she doesn't care which database we use: Her main suggestion was to use either MySQL or MS-SQL, which are both installed on the crappy computers in the lab, and she spent a great deal of time helping people get one of those up and running.
I just SSH'ed home and used my linux box'es PostgreSQL install. Other than one unsupported command in the script everything worked fine.
It helps I've done a little SQL during my internship last summer, but I'm rusty.
Monkey Ball Warrior on
"I resent the entire notion of a body as an ante and then raise you a generalized dissatisfaction with physicality itself" -- Tycho
0
Options
GnomeTankWhat the what?Portland, OregonRegistered Userregular
Just be careful you don't get yourself in a bind later in the course when/if they start dealing with features that MySQL/MSSQL have, but Postgre doesn't...last time I used Postgre it was missing things like UDT's and functions (it had stored procs, but not functions).
Monkey Ball WarriorA collection of mediocre hatsSeattle, WARegistered Userregular
edited April 2012
I doesn't look like either stored procs or UDTs are in the schedule. That being said, she mentioned that she has a postgres box somewhere so if I do anything postgres specific, say on the project or whatever, then she will be able to deal with it.
Also if it comes down to it I'll just install mysql instead, but I'm going to try to avoid it.
Edit: If I'm reading this right, I guess they've added user-defined functions and types.
Monkey Ball Warrior on
"I resent the entire notion of a body as an ante and then raise you a generalized dissatisfaction with physicality itself" -- Tycho
Yeah, you can't default any date/time columns in MySQL except the timestamp.
Why do you need to though? Usually isn't necessary to have it implicit when it isn't a "timestamp" and all.
I have a few datetime fields that by default need to get the current date and if the user wants to change them they can. But I also have the same field in a few other tables and I'd like to know a better way than timestamp (and the obvious flaw that it only goes up to 2037).
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
Yeah, you can't default any date/time columns in MySQL except the timestamp.
Why do you need to though? Usually isn't necessary to have it implicit when it isn't a "timestamp" and all.
I have a few datetime fields that by default need to get the current date and if the user wants to change them they can. But I also have the same field in a few other tables and I'd like to know a better way than timestamp (and the obvious flaw that it only goes up to 2037).
Is that just hardware specific (will it go away if you have 64-bit ints), or is that specific to the MySQL implementation?
I'm assuming it's MySQL implementation. Their help docs make no distinction between their 64 bit system and 32 bit.
I guess datetime goes from 1000-01-01 00:00:00 to 9999-12-31 23:59:59 whereas timestamp goes from 1970-01-01 00:00:01 to 2038-01-19 03:14:07. Kind of makes timestamps impractical if you plan your system to have a life of 20+ years (real possibility in the health system world). The system we have right now has been in place for about.. 15ish and the only reason it's being changed is because of the EHR certification medicare/medicaid wants.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
Yeah, you can't default any date/time columns in MySQL except the timestamp.
Why do you need to though? Usually isn't necessary to have it implicit when it isn't a "timestamp" and all.
I have a few datetime fields that by default need to get the current date and if the user wants to change them they can. But I also have the same field in a few other tables and I'd like to know a better way than timestamp (and the obvious flaw that it only goes up to 2037).
A timestamp is just that. "Capture the current time of this operation."
If that is not what you are doing (i.e. arbitrary date for a birthdate, an appointment) then you should not have the business logic default in your schema.
Just explicitly set it and move on. :rotate: A trigger just makes it harder for people to follow and is a needless dependency.
Problem is by default the dates will be today. There are rare exceptions where this isn't the case. I'd rather the database make the call and record the date in those instances. They can and sometimes are changed by lab results and users. Mostly if a doctor is really really bad at being on time (we have one) and is doing notes and verifying results almost 2 months later. In this particular field one is when the record is created, the other is when the record is for, the third is for modification, and the final one is last addendum. I could probably leave them all null, but unfortunately some of the pieces I have control of don't like nulls and interface directly with that database. I guess I could do it manually but then I'd have to change a lot of logic which I'd rather avoid too.
Why MySQL thinks it's a unique little rose (and why almost 6 years later this hasn't changed) is beyond me, a NOW() Default would make it perform flawlessly.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
I already explained why timestamp works like it does and that is what makes multiple timestamps a little unnecessary.
Triggers for values and behaviour that are arbitrary to your database (i.e. business decisions) are a confusing place to put things. If someone changes the values in a statement and the triggers conflict, it is possibly very painful debugging ahead for others.
Time synchronization is also a factor. Timestamps being in local database time is often fine. If my application thinks it is April 4 but it is close to midnight and the database inserts April 5, bad things can happen. If your time is handled outside of the database, you shouldn't be having your database setting defaults that your application layer later will be setting. Inconsistencies in servers, OS, timezones, yadda yadda. Most developers know that time is something you don't fuck with.
note: this post is probably out of date already cause I got interrupted by two hours of meetings :rotate: BUT POSTING ANYWAY
What's the solution to needing 5 date time fields and requiring them to default to (now) on insert?
I can't use a stored procedure or functions because of limitations of outside software, APIs, and bridges. Apparently everyone but MySQL agrees this is a thing. I can casually suggest a time with my client but if a 2ndary bridge I have no control over makes the call on a certain (new) row it throws everything out of whack. Or at least that's how it was described to me by the original author.
bowen on
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
What's the solution to needing 5 date time fields and requiring them to default to (now) on insert?
I can't use a stored procedure or functions because of limitations of outside software, APIs, and bridges. Apparently everyone but MySQL agrees this is a thing. I can casually suggest a time with my client but if a 2ndary bridge I have no control over makes the call on a certain (new) row it throws everything out of whack. Or at least that's how it was described to me by the original author.
Give them explicit defaults? Seriously, this isn't something you need to be leaving to the database schema.
What's the solution to needing 5 date time fields and requiring them to default to (now) on insert?
I can't use a stored procedure or functions because of limitations of outside software, APIs, and bridges. Apparently everyone but MySQL agrees this is a thing. I can casually suggest a time with my client but if a 2ndary bridge I have no control over makes the call on a certain (new) row it throws everything out of whack. Or at least that's how it was described to me by the original author.
Give them explicit defaults? Seriously, this isn't something you need to be leaving to the database schema.
Might as well just leave them null, since you can't do much else (and would be better than just randomly assigning it 1900 or so when it may very well have been a lab value from that X date (unlikely)). The trigger appears to be working fine. I had to modify it to not change the data unless a null was given. Otherwise the default behavior would be to override any insert with a now() even if you supplied it.
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
I had to modify it to not change the data unless a null was given. Otherwise the default behavior would be to override any insert with a now() even if you supplied it.
That is exactly the kind of thing I was talking about. It makes it hell to maintain when you do these kinds of things.
There is nothing stopping you from doing it, you just should think about whether you should.
I'd be surprised if your use case actually wants the current database date.
Until such a time I can fix all the gunk in this shithole of a pipeline, I really can't change the defaulted behavior much. I have no idea what is going to break on the API end if I do this unfortunately. Which I'm actively replacing the bulk of.
bowen on
not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
ADFS -- anyone? We extract the XML and they import it, but it doesn't show up in the drop down on their portal? I don't know anything about ADFS and neither do my developers
argle blargle blargle. Refactor refactor refactor. This is good refactoring, but everything keeps having it's filthy tendrils deeper than it initially appears, making the changes more complicated than I initially think.
Posts
I have a separate text file that contains the list of services the main program can use as a name and location. I use a filereader in java to open and parse through this file and create service objects and adding these services to an ArrayList<Service> services, (services.add(new Service(name, location) ).
So now, after the array list is created, the Service Fetcher is passed an integer from the session manager and I'm using this integer (required by design I was given) to check the index of the array list and when it finds the appropriate service, it uses the location for that object to execute that class.
Services File
Example:
Open Account C:\CMSC\Project\OpenAccount.java
Admin Management C:\CMSC\Project\AdminManagement.java
Account Management C:\CMSC\Project\AccountManagement.java
Right now, Open account and Account Management are empty files, but Admin Management has it's own main method. So my question is, how do I actually get it to execute that module? Is it something like
For urahonky, I also suggest:
ლ(ಠ益ಠლ)
Don't you need NOW() or UTC_TIMESTAMP() if you want the whole datetime though?
Nintendo ID: Incindium
PSN: IncindiumX
T-SQL (SQL Server)
CREATE TABLE MyTable ( id INT IDENTITY(1,1) PRIMARY KEY, entered DATETIME NOT NULL DEFAULT GETDATE() );MySQL
CREATE TABLE MyTable ( id INT AUTO_INCREMENT PRIMARY KEY, entered DATETIME NOT NULL DEFAULT /* can't use functions here for some reason */, PRIMARY KEY(id) );I guess MySQL thinks it's a unique snowflake ? I don't know, I can't find a good solution to this. I'm assuming I need a trigger of some variety. No idea how to accomplish this in MySQL.
FOR EACH ROW SET NEW.dateAdded = NOW();
That is by far one of the most ridiculous things I have seen ... course I haven't used mySQL in almost a decade.
My jQuery Mobile phone app is the funnest thing I've ever learned, but can anyone tell me what's wrong with this PDO insert? I have the phone app successfully using the same php files all all but my scheduler thingy.
$day=$_POST['day']; $starttime=$_POST['starttime']; $mode=$_POST['mode']; $temp=$_POST['temp']; $humid=$_POST['humid']; $duration=$_POST['duration']; $fresh_air=$_POST['fresh_air']; $air_quality=$_POST['air_quality']; $STH = $DBH->prepare('INSERT INTO scheduler (day, starttime, mode, temp, humid, duration, fresh_air, air_quality VALUES (?, ?, ?, ?, ?, ?, ?, ?)'); $STH->execute(array($day, $starttime, $mode, $temp, $humid, $duration, $fresh_air, $air_quality)); $STH->execute();CREATE TABLE MyTable ( id INT AUTO_INCREMENT PRIMARY KEY, entered DATETIME NOT NULL DEFAULT /* can't use functions here for some reason */, PRIMARY KEY(id) ); CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `MyTable` FOR EACH ROW SET NEW.entered= NOW();Would that be the applicable code modified for my specific example? Problem was I can't use timestamp mainly because I needed like 5-6 datetime variables and our old system defaults these to "NOW" when a semi-complete entry was added.
Someone mentioned fun tools previously -- Fiddler is great.
LOL I love it
So far this is working. Thanks @ToddJewell .
Why do you need to though? Usually isn't necessary to have it implicit when it isn't a "timestamp" and all.
I just SSH'ed home and used my linux box'es PostgreSQL install. Other than one unsupported command in the script everything worked fine.
It helps I've done a little SQL during my internship last summer, but I'm rusty.
Also if it comes down to it I'll just install mysql instead, but I'm going to try to avoid it.
Edit: If I'm reading this right, I guess they've added user-defined functions and types.
(I think postgres has user defined types and custom functions though, but I don't know for how long it has had them)
edit: holy crap, the last time you used postgres must have been over a decade ago
I have a few datetime fields that by default need to get the current date and if the user wants to change them they can. But I also have the same field in a few other tables and I'd like to know a better way than timestamp (and the obvious flaw that it only goes up to 2037).
Is that just hardware specific (will it go away if you have 64-bit ints), or is that specific to the MySQL implementation?
I guess datetime goes from 1000-01-01 00:00:00 to 9999-12-31 23:59:59 whereas timestamp goes from 1970-01-01 00:00:01 to 2038-01-19 03:14:07. Kind of makes timestamps impractical if you plan your system to have a life of 20+ years (real possibility in the health system world). The system we have right now has been in place for about.. 15ish and the only reason it's being changed is because of the EHR certification medicare/medicaid wants.
A timestamp is just that. "Capture the current time of this operation."
If that is not what you are doing (i.e. arbitrary date for a birthdate, an appointment) then you should not have the business logic default in your schema.
Just explicitly set it and move on. :rotate: A trigger just makes it harder for people to follow and is a needless dependency.
Why MySQL thinks it's a unique little rose (and why almost 6 years later this hasn't changed) is beyond me, a NOW() Default would make it perform flawlessly.
Triggers for values and behaviour that are arbitrary to your database (i.e. business decisions) are a confusing place to put things. If someone changes the values in a statement and the triggers conflict, it is possibly very painful debugging ahead for others.
Time synchronization is also a factor. Timestamps being in local database time is often fine. If my application thinks it is April 4 but it is close to midnight and the database inserts April 5, bad things can happen. If your time is handled outside of the database, you shouldn't be having your database setting defaults that your application layer later will be setting. Inconsistencies in servers, OS, timezones, yadda yadda. Most developers know that time is something you don't fuck with.
note: this post is probably out of date already cause I got interrupted by two hours of meetings :rotate: BUT POSTING ANYWAY
Don't assume bad intentions over neglect and misunderstanding.
I can't use a stored procedure or functions because of limitations of outside software, APIs, and bridges. Apparently everyone but MySQL agrees this is a thing. I can casually suggest a time with my client but if a 2ndary bridge I have no control over makes the call on a certain (new) row it throws everything out of whack. Or at least that's how it was described to me by the original author.
Give them explicit defaults? Seriously, this isn't something you need to be leaving to the database schema.
Make them required to be not null, and set the columns = now() in your insert statement?
Do you have a data layer where you're controlling access to the database?
As of now? No. In the future, yes. At which point I am redesigning this shitheap.
Might as well just leave them null, since you can't do much else (and would be better than just randomly assigning it 1900 or so when it may very well have been a lab value from that X date (unlikely)). The trigger appears to be working fine. I had to modify it to not change the data unless a null was given. Otherwise the default behavior would be to override any insert with a now() even if you supplied it.
That is exactly the kind of thing I was talking about. It makes it hell to maintain when you do these kinds of things.
There is nothing stopping you from doing it, you just should think about whether you should.
I'd be surprised if your use case actually wants the current database date.
It just sounds like a terrible idea to me since it is a known source for bugs.