SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

LabiaBadgerTickler: Feb 12, 2014; by Ralp

Wrong area. Sorry .

LabiaBadgerTickler fucked around with this message at 21:09 on Jun 10, 2014

# ? Jun 10, 2014 20:20

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 23:45

Cocoa Crispies: Jul 20, 2001; Vehicular Manslaughter!; Pillbug

With Postgres, is there a way to automatically null/false out a where clause that causes an error?

Ruby code:

    begin
      @tweets = Tweet.
        limit(100).
        where(<<-SQL, q: @query).
          to_tsvector('english', text) @@ to_tsquery('english', :q)
        SQL
        order(created_at: :desc, id_number: :desc)
      @tweets.count
    rescue
      @tweets = Tweet.
        limit(100).
        where(<<-SQL, q: @query).
          to_tsvector('english', text) @@ plainto_tsquery('english', :q)
        SQL
        order(created_at: :desc, id_number: :desc)
    end

Right now I'm just using Ruby error handling in case the user passes in a query that doesn't work with to_tsquery, but does work with plainto_tsquery, but it just seems dirty this way.

# ? Jun 12, 2014 16:00

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

You should really check the query with a regular expression or something, then based on that decide if you are going to use to_tsquery() or plainto_tsquery(). You can do some handling within postgres of errors I believe, but you really should handle it outside if your db.

# ? Jun 12, 2014 16:36

the: Jul 18, 2004; by Cowcaster

Is this a good place to ask stupid questions about SQL queries to a Salesforce database?

# ? Jun 12, 2014 19:53

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

the posted:

Is this a good place to ask stupid questions about SQL queries to a Salesforce database?

Yea I think this would be an appropriate thread for that. I don't know nuthin bout no SQL in Salesforce though. Now, SOQL on the other hand...

# ? Jun 12, 2014 19:56

the: Jul 18, 2004; by Cowcaster

fletcher posted:

Yea I think this would be an appropriate thread for that. I don't know nuthin bout no SQL in Salesforce though. Now, SOQL on the other hand...

Haha yes, thanks. I'm using Beatbox in Python to access the database.

So, I am trying to access the Account History. Say an Account was changed from one name to another, and both of those changes are recorded in the history with a timestamp. What query would I use to grab that information, or is that even possible?

edit: I'm reading through the SOQL help file, and what I'm really looking for is a listing of what all the field types are referring to. Like, how was I supposed to know that the Billing City is referred to as "billingCity" without seeing it in an example?

the fucked around with this message at 20:18 on Jun 12, 2014

# ? Jun 12, 2014 20:14

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

the posted:

Haha yes, thanks. I'm using Beatbox in Python to access the database.

So, I am trying to access the Account History. Say an Account was changed from one name to another, and both of those changes are recorded in the history with a timestamp. What query would I use to grab that information, or is that even possible?

Yup that's doable.

Make sure you turn on History Tracking for the Account object if you haven't already. Setup->Customize->Accounts->Fields then click Set History Tracking button and select which fields you want tracked.

Now you can execute a query like:

code:

SELECT AccountId, CreatedById, CreatedDate, Field, NewValue, OldValue FROM AccountHistory

If you wanna just explore the database and execute queries and such I recommend their Workbench utility: https://workbench.developerforce.com

# ? Jun 12, 2014 20:20

the: Jul 18, 2004; by Cowcaster

Thanks, I appreciate the help

# ? Jun 12, 2014 20:21

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

the posted:

edit: I'm reading through the SOQL help file, and what I'm really looking for is a listing of what all the field types are referring to. Like, how was I supposed to know that the Billing City is referred to as "billingCity" without seeing it in an example?

For that you have to use describe calls: https://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_describesobjects.htm

It will return everything about that object: https://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_describesobjects_describesobjectresult.htm#topic-title

In the array of fields that it returns you can see the label & name (Billing City vs. billingCity): https://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_describesobjects_describesobjectresult.htm#i1427375

edit: or if you just want to look at it through the Salesforce UI go to Setup->Customize->Accounts->Fields and it will list all the Field Labels & Field Names

# ? Jun 12, 2014 20:22

the: Jul 18, 2004; by Cowcaster

fletcher posted:

edit: or if you just want to look at it through the Salesforce UI go to Setup->Customize->Accounts->Fields and it will list all the Field Labels & Field Names

THAT is what I was looking for, thanks!

# ? Jun 12, 2014 20:31

the: Jul 18, 2004; by Cowcaster

Can I combine two selections? Like for that example, say I wanted to select the account history, but only for accounts located in New York. How would I do this? My inclination is to do something like:

code:

SELECT AccountId, CreatedById, CreatedDate, Field, NewValue, OldValue FROM AccountHistory 
AND Name, Id FROM Account WHERE billingCity = 'New York City'

or, moreover, how would I select a specific account?

the fucked around with this message at 20:42 on Jun 12, 2014

# ? Jun 12, 2014 20:39

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

the posted:

Can I combine two selections? Like for that example, say I wanted to select the account history, but only for accounts located in New York. How would I do this? My inclination is to do something like:
code:
SELECT AccountId, CreatedById, CreatedDate, Field, NewValue, OldValue FROM AccountHistory 
AND Name, Id FROM Account WHERE billingCity = 'New York City'
or, moreover, how would I select a specific account?

Yup, you can do that:

code:

SELECT AccountId, CreatedById, CreatedDate, Field, NewValue, OldValue FROM AccountHistory WHERE Account.Name = 'Some Account Name'

code:

SELECT AccountId, CreatedById, CreatedDate, Field, NewValue, OldValue FROM AccountHistory WHERE AccountId IN (SELECT Id FROM Account WHERE Name = 'Some Account Name')

I think the latter may perform better, I can't remember

# ? Jun 12, 2014 20:57

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Cocoa Crispies posted:

With Postgres, is there a way to automatically null/false out a where clause that causes an error?
Ruby code:
    begin
      @tweets = Tweet.
        limit(100).
        where(<<-SQL, q: @query).
          to_tsvector('english', text) @@ to_tsquery('english', :q)
        SQL
        order(created_at: :desc, id_number: :desc)
      @tweets.count
    rescue
      @tweets = Tweet.
        limit(100).
        where(<<-SQL, q: @query).
          to_tsvector('english', text) @@ plainto_tsquery('english', :q)
        SQL
        order(created_at: :desc, id_number: :desc)
    end
Right now I'm just using Ruby error handling in case the user passes in a query that doesn't work with to_tsquery, but does work with plainto_tsquery, but it just seems dirty this way.

You are correct, this is a mess. In answer to your first question, Yes, you can handle exceptions in functions. Second, I'm a proponent of letting the database do its job, namely allowing it to perform optimization of queries, instead of all this application-level second guessing. Alas, in this case, I cannot advise you to use an exception handler in the database because you really are doing it wrong.

Based on the code I see above, there's no guarantee that your rescue clause will even succeed, as it's not clear that the failure from the first attempt was actually a syntax error thrown from to_tsquery. This two-pass model suggests a higher issue: Why are you passing completely random noise into to_tsquery in the first place? Since it seems you might be defining your own query language elsewhere in your application, you really ought to be scrubbing your data in such a way as to permit only valid data to get to to_tsquery in the first place. As another potential solution, you scrub your data to a point that's sufficient for it to pass to plainto_ in all cases, but that obviously restricts your abilities with Boolean ORs.

So, instead of this 'oh crap something went wrong let me try something different that could have been the problem' approach, you really need to be able to say, "It prepares the query as valid input for to_tsquery calls".

# ? Jun 13, 2014 00:20

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Sup squeal buddies, long time no postin. I've been busy doing large scale data transformation and XML stuffs so I haven't had to touch SQL for a while. I come here again to bask in your received wisdom. This is a simple thing, and I have a way to do it, but I'm pretty it's not the 'best' way so I thought I'd throw it out here to see. This is on MSSQL 2008R2.

I've inherited an email contacts table that has three columns:
ID (incremental int) | Pairs (varchar 4000) | LastUpdated (datetime)

The ID is just a synthetic int id for indexing.

Pairs is a long string of value-pairs defined as such:
name:David|email:davidsemail@geemail.com|source:Signup|
name:Christine|lastname:Smith|mobilephone:555-555-5555|officephone:|homephone:|address1:123 etc etc

What I've been asked to do is retrieve the name: and email: value where source: = 'X'. The source part is easy:

code:

SELECT Pairs
FROM Contacts
WHERE Pairs like '%source:blah|%'

What's harder is retrieving the actual value. If I was in a programming environment I could use regexes easily enough like:

code:

Email = Regex("email:([a-zA-Z0-9@./]+)|", Pairs)

But I'm hoping to restrict this to a SQL only solution without having to write/compile code. Is there a way to do this? I was thinking one of two possibilities:
1. Use a split function to split on |. This is my split function currently (developed earlier in this very thread!):

code:

ALTER FUNCTION [dbo].[SplitDelimited]
(	
	@List nvarchar(2000),
	@SplitOn nvarchar(1)
)
RETURNS @RtnValue table (
	Id int identity(1,1),
	Value nvarchar(100)
)
AS
BEGIN
	While (Charindex(@SplitOn,@List)>0)
	Begin 
		Insert Into @RtnValue (value)
		Select 
			Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
		Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
	End 
	
	Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END

2. Do something magical with XMLPath somehow.

Any opinions? My 'git er done' way would be to just write up the regexes I'll need in .NET but this feels like it could be handled in-engine SQL-wise.

# ? Jun 13, 2014 20:35

Sedro: Dec 31, 2008

If you're going to be dealing with that data ongoing, you might consider using hstore or another no-squeal solution--they specifically solve your problem.

# ? Jun 14, 2014 01:12

Isurion: Jul 28, 2007

I'd use SUBSTRING and ~~PATINDEX~~ CHARINDEX but it would be pretty ugly.

E: Don't think PATINDEX is necessary for this.

Isurion fucked around with this message at 01:46 on Jun 14, 2014

# ? Jun 14, 2014 01:38

Xae: Jan 19, 2005

Sedro posted:

If you're going to be dealing with that data ongoing, you might consider using hstore or another no-squeal solution--they specifically solve your problem.

The other option would be to structure and normalize the data. Contact information is about as far from NoSQL's wheel house as you can get.

# ? Jun 14, 2014 02:05

big trivia FAIL: May 9, 2003; "Jorge wants to be hardcore,
but his mom won't let him"

Scaramouche posted:

Sup squeal buddies, long time no postin. I've been busy doing large scale data transformation and XML stuffs so I haven't had to touch SQL for a while. I come here again to bask in your received wisdom. This is a simple thing, and I have a way to do it, but I'm pretty it's not the 'best' way so I thought I'd throw it out here to see. This is on MSSQL 2008R2.

I've inherited an email contacts table that has three columns:
ID (incremental int) | Pairs (varchar 4000) | LastUpdated (datetime)

The ID is just a synthetic int id for indexing.

Pairs is a long string of value-pairs defined as such:
name:David|email:davidsemail@geemail.com|source:Signup|
name:Christine|lastname:Smith|mobilephone:555-555-5555|officephone:|homephone:|address1:123 etc etc

What I've been asked to do is retrieve the name: and email: value where source: = 'X'. The source part is easy:
code:
SELECT Pairs
FROM Contacts
WHERE Pairs like '%source:blah|%'
What's harder is retrieving the actual value. If I was in a programming environment I could use regexes easily enough like:
code:
Email = Regex("email:([a-zA-Z0-9@./]+)|", Pairs)
But I'm hoping to restrict this to a SQL only solution without having to write/compile code. Is there a way to do this? I was thinking one of two possibilities:
1. Use a split function to split on |. This is my split function currently (developed earlier in this very thread!):
code:
ALTER FUNCTION [dbo].[SplitDelimited]
(	
	@List nvarchar(2000),
	@SplitOn nvarchar(1)
)
RETURNS @RtnValue table (
	Id int identity(1,1),
	Value nvarchar(100)
)
AS
BEGIN
	While (Charindex(@SplitOn,@List)>0)
	Begin 
		Insert Into @RtnValue (value)
		Select 
			Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
		Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
	End 
	
	Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END
2. Do something magical with XMLPath somehow.

Any opinions? My 'git er done' way would be to just write up the regexes I'll need in .NET but this feels like it could be handled in-engine SQL-wise.

This is actually making me cry. Why...why would you store data that way?? Especially if you're going to need to query it? Someone should be straight up fired.

# ? Jun 14, 2014 02:06

Xae: Jan 19, 2005

-S- posted:

This is actually making me cry. Why...why would you store data that way?? Especially if you're going to need to query it? Someone should be straight up fired.

Database-phobia is real, and most developers have it.

# ? Jun 14, 2014 02:07

big trivia FAIL: May 9, 2003; "Jorge wants to be hardcore,
but his mom won't let him"

Xae posted:

Database-phobia is real, and most developers have it.

I mean you might as well have a Users table with 2 columns - a PK Id and then a column called "UserStuff" with everything in it pipe or comma separated. What in the actual gently caress?

# ? Jun 14, 2014 02:10

big trivia FAIL: May 9, 2003; "Jorge wants to be hardcore,
but his mom won't let him"

My real, actual suggestion is you need to smoke that table, build a real one, and refactor the existing code to store the goddamn contact information in a reportable format. If you're going to have to deal with this without being able to do that, you should quit.

# ? Jun 14, 2014 02:12

Xae: Jan 19, 2005

-S- posted:

My real, actual suggestion is you need to smoke that table, build a real one, and refactor the existing code to store the goddamn contact information in a reportable format. If you're going to have to deal with this without being able to do that, you should quit.

This is not far off how our System Architects use databases.

Hell, they sold our loving idiot mangers on going from having reference data in a database to storing it in flat files.

Hundreds of flat files, no naming system and no automation. We're a tape drive away from reliving the 80s.

# ? Jun 14, 2014 02:47

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Hah, thought that'd get a reaction. I'd like to emphasize something from my original message:

A genius posted:

I've inherited an email contacts table that has three columns

I'm not going to keep this thing moving forward; it's a weird mismash of contact info from three different big iron CRM systems, the president's outlook address book, the facebook newsletter signup page, etc. In fact whatever I end up doing to extract values from this is probably going to be how I'm going to convert it to to real data in the first place, which is why I was asking if there's a better way. There's not too many rows, maybe about 40,000 total so efficiency doesn't have to be your main concern if you're going to suggest something.

# ? Jun 14, 2014 03:05

Xae: Jan 19, 2005

Scaramouche posted:

Hah, thought that'd get a reaction. I'd like to emphasize something from my original message:

I'm not going to keep this thing moving forward; it's a weird mismash of contact info from three different big iron CRM systems, the president's outlook address book, the facebook newsletter signup page, etc. In fact whatever I end up doing to extract values from this is probably going to be how I'm going to convert it to to real data in the first place, which is why I was asking if there's a better way. There's not too many rows, maybe about 40,000 total so efficiency doesn't have to be your main concern if you're going to suggest something.

Your best bet is a cursor. SQL does not like multiple pieces of data stuffed into a single field.

Pseudo Code

code:

Open Cursor Contacts
for each Record in Contacts
    Get String Pairs
    for each delimiter in Pairs
        Print Contacts.Key, Pairs.Key, Pairs.Value
    end for each delimiter in Pairs
end for each Record in Contacts

There is no good way of dealing with 0-th normal form KV store in SQL because RDBMS are designed NOT to be one and SQL is designed to work with a RDBMS.

Xae fucked around with this message at 03:42 on Jun 14, 2014

# ? Jun 14, 2014 03:39

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

-S- posted:

I mean you might as well have a Users table with 2 columns - a PK Id and then a column called "UserStuff" with everything in it ~~pipe or comma separated~~ as a compressed YAML document. What in the actual gently caress?

Made that more realistic for you. :suicide:

I've seen very large databases with this type of structure, and engineers simultaneously claiming that they tried using a key-value system but it was too slow retrieving the data due to "query start times". Then they start talking about just using flat files and a revision control system.

Scaramouche posted:

I'm not going to keep this thing moving forward...

Dump it all as tab-separate values and process it with sed/awk/Perl/Python/vim macros/anything. I mean, doing it in SQL is a nice academic exercise, but seems wholly unnecessary.

# ? Jun 14, 2014 03:54

One Swell Foop: Aug 5, 2010; I'm afraid we have no time for codes and manners.

If all of the rows have the same fields in the same order, I'm pretty sure you could do something using a combination of SUBSTRING, PATINDEX and REVERSE but it's too late on Friday evening to work out the exact code.

# ? Jun 14, 2014 05:16

Xae: Jan 19, 2005

PhantomOfTheCopier posted:

Made that more realistic for you. I've seen very large databases with this type of structure, and engineers simultaneously claiming that they tried using a key-value system but it was too slow retrieving the data due to "query start times". Then they start talking about just using flat files and a revision control system.

Dump it all as tab-separate values and process it with sed/awk/Perl/Python/vim macros/anything. I mean, doing it in SQL is a nice academic exercise, but seems wholly unnecessary.

Excel is pretty handy for some things like this.

Example:

code:

1	name:David|email:davidsemail@geemail.com|source:Signup|

Replace | and : with Tab, or do text to columns in excel.

Rearrange the columns a bit, add quotes some quotes and statics in excel and you end up with...

code:

update NewTable set name = 'David',email = 'davidsemail@geemail.com',source = 'Signup' 	where ID = 1

# ? Jun 14, 2014 06:04

big trivia FAIL: May 9, 2003; "Jorge wants to be hardcore,
but his mom won't let him"

Every time I see something like this I wonder how I'm not making a million dollars a year

# ? Jun 14, 2014 14:04

big trivia FAIL: May 9, 2003; "Jorge wants to be hardcore,
but his mom won't let him"

Xae posted:

Your best bet is a cursor. SQL does not like multiple pieces of data stuffed into a single field.

Pseudo Code
code:
Open Cursor Contacts
for each Record in Contacts
    Get String Pairs
    for each delimiter in Pairs
        Print Contacts.Key, Pairs.Key, Pairs.Value
    end for each delimiter in Pairs
end for each Record in Contacts
There is no good way of dealing with 0-th normal form KV store in SQL because RDBMS are designed NOT to be one and SQL is designed to work with a RDBMS.

Please don't use SQL cursors. I know you said performance isn't necessary, but once you write one cursor is SQL you will be tempted to do it whenever you think you need one (hint: you rarely if every will need one, and if you do, you've gone wrong somewhere).

# ? Jun 14, 2014 14:07

Xae: Jan 19, 2005

-S- posted:

Please don't use SQL cursors. I know you said performance isn't necessary, but once you write one cursor is SQL you will be tempted to do it whenever you think you need one (hint: you rarely if every will need one, and if you do, you've gone wrong somewhere).

A cursor is nothing but a loop.

Sometimes you have logic that is extremely complex and can't be put nicely into a single statement.

# ? Jun 14, 2014 16:37

One Swell Foop: Aug 5, 2010; I'm afraid we have no time for codes and manners.

OK, here's some pseudocode, I didn't test it but I think it should be pretty close to working:

code:

SELECT SUBSTRING(
	Pairs,
	PATINDEX ('%name:%|nextkey:%', Pairs)  + LEN('name:'), -- start of name's value
	-- get end position by finding start position of reversed pattern
	SELECT (LEN(Pairs) - (PATINDEX('%:yektxen|%' , REVERSE(Pairs)) + LEN(':yektxen')) 
		-- then subtract start position to get length of substring
		- (PATINDEX('%name:%|nextkey:%', Pairs)  + LEN('name:'))
	)

This only works if your keys are in the same order each time.

E: or install Master Data Services on your SQL Server instance and use a regex.

One Swell Foop fucked around with this message at 17:46 on Jun 14, 2014

# ? Jun 14, 2014 17:11

Cocoa Crispies: Jul 20, 2001; Vehicular Manslaughter!; Pillbug

PhantomOfTheCopier posted:

So, instead of this 'oh crap something went wrong let me try something different that could have been the problem' approach, you really need to be able to say, "It prepares the query as valid input for to_tsquery calls".

I'm putting it in there right from the search box. Should I do some "application-level second guessing" and implement my own query language when the database provides a perfectly fine one?

# ? Jun 14, 2014 23:31

big trivia FAIL: May 9, 2003; "Jorge wants to be hardcore,
but his mom won't let him"

Xae posted:

A cursor is nothing but a loop.

Sometimes you have logic that is extremely complex and can't be put nicely into a single statement.

Yes but SQL is terrible at running non-set based transactions and the cursor locks the rows you've loaded. You can probably do anything you want to do with a cursor with a CTE or MERGE or any number of other cool poo poo SQL can do (obviously there are edge cases like the one above where the cursor was recommended). You should never, ever use a cursor in SQL if it can at all be avoided. The same is also true for any WHILE loop.

edit:
And also, if you're putting business/app logic into your SQL database you're doing something very wrong.

big trivia FAIL fucked around with this message at 02:19 on Jun 15, 2014

# ? Jun 15, 2014 02:14

Xae: Jan 19, 2005

-S- posted:

And also, if you're putting business/app logic into your SQL database you're doing something very wrong.

You're just full of bad advice today.

Try running an enterprise application that has dozens of different front ends with out some logic in the database.

Or try running a database with a few dozen/hundred terabytes. Sometimes it is much faster to keep the processing on the database than to ship out terabytes of data for some slow rear end business app to process them.

# ? Jun 15, 2014 02:41

Cocoa Crispies: Jul 20, 2001; Vehicular Manslaughter!; Pillbug

-S- posted:

And also, if you're putting business/app logic into your SQL database you're doing something very wrong.

There are two schools of thought about that: the logic has to go somewhere, SQL has tools for building that logic, and using those tools you don't have to worry about network partitions between your database and the client providing business logic causing issues: http://aphyr.com/posts/282-call-me-maybe-postgres

Compare http://martinfowler.com/bliki/IntegrationDatabase.html and http://martinfowler.com/bliki/ApplicationDatabase.html

# ? Jun 15, 2014 02:44

Hadlock: Nov 9, 2004

Isn't it a lot easier to audit your software if you're just updating the code in the DB, instead of the EXE front end?

Or maybe it's not. Looking at our friday afternoon weekly install meeting, about 75% of the code going in is in the form of SQL files updating SPs.

We have a lot of EXEs that aren't updated for years, but new SQL files on a monthly or semi monthly basis to meet various requirements.

Our primary DB is a couple TB in size I would imagine, at least 1.5

Hadlock fucked around with this message at 03:15 on Jun 15, 2014

# ? Jun 15, 2014 03:11

Cocoa Crispies: Jul 20, 2001; Vehicular Manslaughter!; Pillbug

Hadlock posted:

Isn't it a lot easier to audit your software if you're just updating the code in the DB, instead of the EXE front end?

Or maybe it's not. Looking at our friday afternoon weekly install meeting, about 75% of the code going in is in the form of SQL files updating SPs.

We have a lot of EXEs that aren't updated for years, but new SQL files on a monthly or semi monthly basis to meet various requirements.

Our primary DB is a couple TB in size I would imagine, at least 1.5

I've been on projects that deploy new code multiple times every day, having it all in one place is the most important part.

Moving the business logic isn't a choice you make lightly, or really ever.

# ? Jun 15, 2014 03:23

big trivia FAIL: May 9, 2003; "Jorge wants to be hardcore,
but his mom won't let him"

Xae posted:

You're just full of bad advice today.

Try running an enterprise application that has dozens of different front ends with out some logic in the database.

Or try running a database with a few dozen/hundred terabytes. Sometimes it is much faster to keep the processing on the database than to ship out terabytes of data for some slow rear end business app to process them.

Haha. You actually think it's a-ok to use cursors and loops in a set-based RDBMS and you're calling me out? You're going to have some logic in the database and use SPs and the like, but if you're putting actual, real business or app logic in triggers (lol) calling SPs you done messed up. And why are you giving your application "terabytes" of data at a time? Do you not understand how to build an actual API or utilize one?

# ? Jun 15, 2014 03:37

big trivia FAIL: May 9, 2003; "Jorge wants to be hardcore,
but his mom won't let him"

Hadlock posted:

Isn't it a lot easier to audit your software if you're just updating the code in the DB, instead of the EXE front end?

Or maybe it's not. Looking at our friday afternoon weekly install meeting, about 75% of the code going in is in the form of SQL files updating SPs.

We have a lot of EXEs that aren't updated for years, but new SQL files on a monthly or semi monthly basis to meet various requirements.

Our primary DB is a couple TB in size I would imagine, at least 1.5

That's because updating the DB is easier. It's the path of least resistance and so that's what's taken. That doesn't make it right.

# ? Jun 15, 2014 03:39

Adbot: ADBOT LOVES YOU

# ? May 28, 2024 23:45

Xae: Jan 19, 2005

-S- posted:

Haha. You actually think it's a-ok to use cursors and loops in a set-based RDBMS and you're calling me out? You're going to have some logic in the database and use SPs and the like, but if you're putting actual, real business or app logic in triggers (lol) calling SPs you done messed up. And why are you giving your application "terabytes" of data at a time? Do you not understand how to build an actual API or utilize one?

Who said anything about triggers?

And if you want to Process 15 Terabytes of transactions on an app server they end up being sent over the wire if you use an API or have the App server run SQL statements or whatever. The data either has to go from the DB server to the App server, or be processed on the database server.

Cursors are like anything else: A tool. There are times to use them, there are times to not use them. It all depends on what system you are running on and what you are trying to do.

# ? Jun 15, 2014 03:50

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »