The Heap™ – Consultancy ©® - 2014-09-25 (page 1 of 2)

user285oo6

9:18 AM

In sql developer how can i get the output of cursor

James

11:20 AM

@ypercube Can I ask you something about SQLAlchemy when you have a few minutes?

ypercube

11:50 AM

@James Sure

James

12:04 PM

@ypercube Well it's probably not a very well-formed question.

Some of the analysts here use to query a production SQL Server database. Sometimes they leave their sessions open after their queries finish and cause blocking. I'm trying to get them off the production server, but in the meantime is there a document you'd recommend that shows them how to start and end sessions & connections properly?

ypercube

@James let me check.

JNK

mornin

James

@ypercube Thanks. I found a few things in Google but am not sure what's best. I've never used it before.

Morning JNK

ypercube

Do they use the ORM?

James

@ypercube The guy I talked to this morning says no, but I haven't seen the code yet.

ypercube

12:14 PM

OK. This is the page one starts with: docs.sqlalchemy.org/en/latest/core/connections.html

It displays the various ways to connect to the db with sqlalchemy.

The problem might be sessions left open or transactions uncommitted?

user965347

helo i have some question here

about index mysql

ypercube

I think the default is autocommit=False, so the 2nd might be more probable.

user965347

ask here? or in SO?

James

@ypercube Ah yeah. Does that mean it opens a transaction for SELECTs?

ypercube

@user965347 You can ask a question at the site, here (dba.se)

user965347

12:16 PM

ok

ypercube

@James Not sure about selects, but probably yes.

user965347

i hope you guys answer it, i am not experienced in tuning

James

@ypercube Well thanks -- I'll see if I can test later. But that would explain exactly what we saw today.

ypercube

@James This paragraph, too: docs.sqlalchemy.org/en/latest/orm/…

Kermit

@bluefeet can you kill this? what part is programming related?

-3

Q: graphic APIs not available

So I have just downloaded and installed Bluestacks, and decided to play Summoners War, but whenever I try to run it, it says "Summoners War is requesting graphics APIs that are not available on your system andd has been terminated." Anybody know how I can fix this?

bluestacks

user965347

12:26 PM

0

Q: MySQL - whats wrong with my index?

i have a table here here is the structure CREATE TABLE `table_data_cache` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, `enterTollgateNumber` bigint(20) DEFAULT NULL, `exitTollgateNumber` bigint(20) DEFAULT NULL, `carType` varchar(255) COLLATE utf8_unicode_ci D...

mysql index

hmmmm :\

JNK

@user965347 added a comment. Don't use a function in your WHERE clause. I'm not a MySQL guy but that's a nono in sql server

user965347

nono ?

JNK

yeah, don't do it

NO! NO!

user965347

so its all about date()?

JNK

Again in SQL Server that would be the issue

user965347

12:30 PM

lets try

JNK

MySQL can be...special

Kermit

@user965347 oh dear.. 24 columns for hour?

user965347

lol yeah client wants so

Kermit

@user965347 smack the client and don't do it

tell them to learn how to database

JNK

Would work better normalized

user965347

12:31 PM

haha yeah

this is terrible client i ever had, they want split one year table into 12 table

JNK

HourID, HourValue

Kermit

@user965347 um you need to learn how to pushback

JNK

@Kermit There's probably a more diplomatic way to phrase that ;)

Kermit

@JNK pushing back = explaining why they're dumb

user965347

if i dont do it i cant live

JNK

12:33 PM

lol

user965347

XD

Kermit

@user965347 your problem. don't come to us later crying about it

JNK

Nothing worse than doing database development while being held hostage by a criminal organization who will kill you if you don't build to spec.

...except doing MySQL database development while being held hostage by a criminal organization who will kill you if you don't build to spec.

user965347

yeah my problem T_T

look at my table now lol

Kermit

i'm getting angry looking at such shitty design

JNK

12:35 PM

A table for each month of a year?

It's basically poor-man's partitioning

user965347

yeah client want so

Kermit

good luck with your career

user965347

thanks @Kermit

wow that works

its all about date() function

JNK

yeah

the engine doesn't know what the output of a function is until it runs it

So it has to run it for every row

so it won't use an index

user965347

thanks pal

JNK

12:38 PM

hth

Kermit

@JNK you're such a good pal

ypercube

@user965347 You also have duplicate indexes. Why 2 indexes for the same column, like?:

  PRIMARY KEY (`date`),
  KEY `Index 2` (`date`) USING HASH

You can delete the hash index.

user965347

ok

thanks

JNK

@Kermit how's Kermit Jr. doing?

ypercube

@user965347 Did you remove the date() function calls from the WHERE as well? Do that, too.

James

12:49 PM

@ypercube I just tested using sqlalchemy + pyodbc. It looks like by default it sets implicit_transactions on, then runs IF @@TRANCOUNT > 0 ROLLBACK TRAN, then runs the select, then runs IF @@TRANCOUNT > 0 ROLLBACK TRAN again.

Kermit

@JNK almost 5 mo old. miss him a lot

ypercube

@James Version of sqlalchemy?

JNK

@Kermit at work you mean?

Kermit

@JNK been traveling past 3 weeks..

JNK

@Kermit ugh

that sucks

Kermit

12:57 PM

i can't really complain. i'm thankful i was home for the first 4 months

James

@ypercube 0.9.7, which might not be what the analyst is using. If I set autocommit=true I get the same behavior except commit instead of rollback at the end.

Lamak

@Kermit you can tell this because your level of snarkiness has been risen lately

user965347

@ypercube yea i removed it now, sorry i forgot :p

anyway is the ref (null) is okay?

James

This is what I'm testing with: pastebin.com/havtE28F

Kermit

@Lamak use either "has risen" or "has been rising"

Lamak

1:15 PM

@Kermit yeah, I was going for the first one, and then decided for the second, but didn't read the whole sentence....and then 2 minutes already passed

Kermit

@Lamak ah okay.. just thought i would mention it in case you missed it

Lamak

@Kermit I truly appreciate this

Kermit

no worries

i want you to be an awesome stack overflow ambassador

#helpfulkermit

You need to break up your questions and show what you've done to try to solve each one. As is, there are either too many possible answers, or good answers would be too long for this format. — Kermit 33 secs ago

vtc as dup

-1

Q: Importing CSV file to an Oracle database

Is there an SQL command (for Oracle) that I could use to create a table and fill it in with the entries from a CSV file? The columns of the CSV file and the table are the same, but if there is a version of the command where I could decide which field from the file goes to which column it would be...

sql database oracle csv import

Lamak

@Kermit dupe hammered

Kermit

hammer time!

ah damn, nice @Lamak . didn't know you had a gold badge for sql

Lamak

1:26 PM

@Kermit didn't know either, thought I had only the sql-server one.

Kermit

@Lamak i'll have to borrow your hammer more often

Lamak

yeah, just ping me

Aaron Bertrand

The whole point of using an alias though is to not have to repeat database.schema.table throughout the query. I like the answer (+1) it just seems like it's arguing with itself: "Aliases are good!" "But I don't want to use an alias!" — Aaron Bertrand 1 min ago

bluefeet

@Kermit done

billinkc

Darn, I wanted to start playing bluestacks of war

Kermit

1:41 PM

ypercube

@James So, the connections are not closed. Are they?

You could add connection.close() in the end.

Or connection.execute('COMMIT;') if you don't want to close the connection and continue sending stuff.

Lamak

bluefeet

@Lamak haha

who is right?

0

Q: Are RANK() and DENSE_RANK() deterministic or non-deterministic?

According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan "... the RANK() and DENSE_RANK() functions are always deterministic". Who is right? What I have found so far: Microsoft's Definition "Deterministic functions always r...

sql sql-server tsql ranking-functions

Lamak

yup, the dog's expression in the last panel is hilarious

Kermit

@Lamak LOL

ypercube

2:04 PM

@bluefeet Question seems pretty good.

I wonder why even good questions attract shitty answers. Oh, yeah. SO why.

Kermit

@bluefeet lol

I actually have a question to upvote

ypercube

@bluefeet Itzik is right.

@PaulWhite or @AaronBertrand might have an explanation why Microsoft did not bother to implement on the optimizer that they have to be considered deterministic.

Lamak

@ypercube or they are still trying to vote in PASS

Shawn Melton

@Lamak <snicker>

Lamak

yeah, I'm not a PASS member, and even I am outraged ;-)

Shawn Melton

2:15 PM

Well I am not sure the whole thing about filling in the "new fields" in order to get your email to vote. I logged back into my account some time back and recall it prompted me to fill in something but I don't really recall them being new fields.

Kermit

can we vtc this as dup? OP answers his own q with a link to a dup

0

A: How to cache images in android?

I found this grate library by referring to this question and it solved my problem.Hope this will help you all.

Kermit

2:30 PM

LOL

I need a new car — gbestard 24 secs ago

Aaron Bertrand

@ypercube here's an example of why this can be non-deterministic:

RANK() OVER (PARTITION BY some_column ORDER BY NEWID()),

This is one of those runtime constant things, and technically it's deterministic if the same GUID were passed every time, or it were changed to ORDER BY (SELECT NEWID()))

Transcript for

The Heap™ – Consultancy ©®