VBA Rubberducking

Mathieu Guindon

19:11

(I hate SCREAMCASE SQL)

IvenBach

Options>Designers>Table & DB Designers>Prevent saving changes that require table re-creation is on by default. That's there to help prevent you from shooting yourself in the foot. Is that an option one ought to worry about in a dev environment or does it depend?

this

if you mean all tables and fields in SCREAM_CASE then I'm with you. It's ok when only the keywords are SCREAM since at least it can be read easily in an unformatted text)

Dain Ironfoot III

@MathieuGuindon Me too.

All lower-case keywords.

Go C# style!

this

@IvenBach as hard as it may be, I suggest you to not use designers - it does too much magic for my taste.

Dain Ironfoot III

What do you mean by designers?

this

19:16

GUI designer to build SQL tables

Dain Ironfoot III

Ah.

IvenBach

I'm not trying to use them. I right now want to change Invoice and Estimate fields to have a data type of money.

Dain Ironfoot III

I don't build my tables with those, but I'll occasionally use them to see the schema quick.

this

@IvenBach it'd be basically something like ALTER TABLE dbo.Invoices ALTER COLUMN Invoice money; (assuming it should be nullable)

Mathieu Guindon

@DainIronfootIII right-click the table, script, drop and create to, new query

Dain Ironfoot III

19:18

@MathieuGuindon What if I press F5?

I just do right-click, Design.

this

Hasta la vista, baby

Mathieu Guindon

Then you've recreated the table as is, and lost all the data

Dain Ironfoot III

That would get, very interesting, LOL. Especially with some of our tables allowing cascade delete.

this

fwiw, if you want to avoid that kind of accident, you could just script the CREATE only. It makes the script non-idempotent

Dain Ironfoot III

In design mode, I don't have to worry about changing things or not, because it's so clearly different than a script it fires alerts at me.

this

19:21

but... are you hitting F5 at random or what?

Dain Ironfoot III

It's basically a table of name, type, nullability.

@this Sometimes.

Mathieu Guindon

lol

this

if that's all you want, you could have consulted the object explorer.

Dain Ironfoot III

True.

Mathieu Guindon

I never do cascade deletes or cascade anything

this

19:24

#ItDepends.

Mathieu Guindon

That way FK constraints prevent deletion, instead of destroying everything and its mother

IvenBach

@this To do that Standard Toolbar>New Query and from there proceed to write up the script that would perform the action?

Mathieu Guindon

Yes

making sure you're scripting against the right db

IvenBach

I only have a single DB in my test instance.

Dain Ironfoot III

@MathieuGuindon Sometimes the mom needs to get destroyed.

this

19:26

also, keep in mind that scripts won't be nice and kind... "hey, are you sure you want to lose the data" will never be asked from you.

Mathieu Guindon

Then I wipe the kids first

Dain Ironfoot III

I'm really, really tempted to "not go wrong" and get my mom a huge bouquet of flowers for mother's day.

this

"Ok, sure, boss" or "can't do it, boss" is the responses you should expect from a script.

@MathieuGuindon Sometime that can be a make-work.

Mathieu Guindon

?

Dain Ironfoot III

Because my mom hates flowers and has allergies to them (some people think she does it on purpose to piss people off).

this

19:28

Ok - if we go no cascade EVAR route, that means we write a stored procedure to delete from child, then delete from the parent, right?

IvenBach

I feel so derpy right now... I saved the script and closed the window for it. Now can't find it. How do I open it?

this

use Recent in the File

Mathieu Guindon

@this assuming it's not a one-time thing, yeah

Dain Ironfoot III

TFW you realize your view models (MVC) were correct the first time so you forgot to go back and break the classes into their own files...

this

With a cascade route, you still write a stored procedure to delete from the parent and let the engine pick up the kids. One statement sproc. Less code FTW

Mathieu Guindon

19:29

Implicit

this

In either way, you already have the permission to manage the deletions so that nobody can delete it by accident; it must go thru the sproc.

Again, #ItDepends.

Mathieu Guindon

Are triggers ever ok?

this

On tables, almost never. On views, no problems.

Mathieu Guindon

Cascade delete sounds like a trigger to me

this

I suppose so but it's pretty simple and it enforces the integrity (e.g. no orphaned kids allowed)

Mathieu Guindon

19:32

nope. FK's do that.

IvenBach

@FreezePhoenix welcome to the pond.

Mathieu Guindon

I guess cascading makes a good use case for diagrams

this

If you are deleting an order, it doesn't make sense to have any order details floating around. With FK preventing cascades, you must clean each level manually. With cascading FK, you still have the integrity with the guranatee that a delete will take the kids.

But if your business is such that no orders must be ever deleted, then you definitely don't wanna a cascading FK.

Hence, #ItDepends.

FWIW, I like to keep cascades to minimum anyway. It's more likely that there's an accounting rule that disallow deleting an order even if it was voided.

Mathieu Guindon

I guess I've worked with ERP systems for too long lol

IvenBach

:44555759 Welcome back then!

FreezePhoenix

19:35

@IvenBach Thanks. I'm Pheo

Mathieu Guindon

Hi, I used to be a mug

FreezePhoenix

:P

this

@MathieuGuindon do you have any custom database that you build from scratch? Or is it all working with some db provided by some vendor?

Mathieu Guindon

I got one for a stupid-simple 'clocker' app that I built last month

Dain Ironfoot III

@this Oh sheesh. We've got custom databases build out of SQL Server.

Everything stored as strings and stuff.

Mathieu Guindon

19:37

Then another to store intermediate data and interfacing with the ERP

Dain Ironfoot III

We literally have structures for representing tables, rows, columns, and values.

this

@DainIronfootIII Sounds like a good use of a RDBMS to me.

Mathieu Guindon

And then there's my DW, where nothing ever gets hard-deleted

Dain Ironfoot III

@this Is that sarcasm?

this

@MathieuGuindon Yeah, that's what I usually do for those with such systems. As a matter of rule, we try to avoid touching databases we don't make; best to take it to our database so we can do what we want to.

Dain Ironfoot III

19:39

I didn't know RDBMS's were supposed to simulate themselves, LOL.

this

@DainIronfootIII Yes. ;)

FreezePhoenix

LOL

IvenBach

19:53

If a batch that was started with BEGIN TRANSACTION and contained an ALTER TABLE ... ALTER COLUMN ... instruction was selected then F5 to run the selection but never had COMMIT included. Would that disallow viewing a table in design view because the field updated is in an undefined data type sort of place?

this

no because the transaction is still open

Mathieu Guindon

enter isolation level :)

this

you must close the transaction --- F5 by itself doesn't close it; you must explicitly execute either COMMIT or ROLLBACK to close the connection.

Note that closing a query window will also close the transaction (usually rollbacks it)

Mathieu Guindon

if you're configured to read uncommitted, then yeah, you should see the uncommitted changes

this

that also implies that each query window is its own connection and thus are in a different scope.

IvenBach

19:57

I've left whatever the default configuration is.

Mathieu Guindon

that would be serialized IIRC

this

Object explorer can be thought as of having another connection (since it must have one to do the metadata)

Hm. I think the default OOB is read committed

Mathieu Guindon

could very well be

IvenBach

> This implies that you shouldn’t try to execute queries on the same objects between two query windows or you may get unexpected results. For example, don’t modify data in tblCustomers in window 1 and window 2 and expect it to rollback for both.

Dain Ironfoot III

Is read uncommitted more performant?

Mathieu Guindon

19:58

@this that is the case

this

and isolation level isn't that relevant - it's very important to ensure that you close transactions and do it ASAP.

Mathieu Guindon

^

this

@DainIronfootIII it's not really a matter of performance. It's more of a matter of concurrency.

Dain Ironfoot III

OK.

this

I suppose one could make a case that the more isolated you get, the more performance impact you have because you have to take out much more locks but that's peanuts compared to the impact you have on other users

Mathieu Guindon

19:59

then you start having fugly with (nolock) hints all over the place

this

NO FRICKING nolock HINT ON MY WATCH!

2

Dain Ironfoot III

As far as that goes, sometimes simply adding indexes can prevent running a query a million times at once can prevent deadlocks :)

this

Yeah that's the correct solution.

Dain Ironfoot III

It usually doesn't matter.

SQL is pretty standard, until it isn't.

Mathieu Guindon

SQL is pretty standard, until it's in Access

IvenBach

20:07

~~I figured it was a re-derpy question.~~ Maybe not.

Dain Ironfoot III

I think the only T-SQL things I use are the rename SP and querying things like locks, keys and other constraints, and stuff.

this

As the standards go, SQL is pretty good. Take HTML for example....

Even Access is largely compliant with SQL standard.

Dain Ironfoot III

@this Launches blood from eyes.

C# is extremely compliant with the standards--at least until Rider came along--because it was the standard.

Can't have differences from the standards if there is only one implementation :P

this

Is there really a standard for C#, though? There's only one vendor, isn't it?

Dain Ironfoot III

Yes, there is a standard. They publish it each version.

Sometimes they tweak it slightly because they find bugs that would break backward compatibility to fix.

this

20:09

Hmm. not quite the same thing when you have 10 different vendors who has their opinions on what it should.

which certainly is the case w/ SQL and HTML

Dain Ironfoot III

Some of the versions are actually registered with ISO.

this

and ~~JavaScript~~ ECMAScript, too, i guess.

IvenBach

@this per your suggestion I'm trying not to create a new view using the UI, but via a query. Reading the Syntax codeblock it has select_statement. Does mean anything that equates out to a Select is valid?

-- Syntax for SQL Server and Azure SQL Database

CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ]
[ ; ]

<view_attribute> ::=
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]
}

this

Yes, @IvenBach

Trust me, you won't grok SQL until you do it by hand.

IvenBach

In the same way as Dim foo as WorkSheet lets me pass foo into anything that requires a WorkSheet object.

@this I only ever lightly modified SQL before, and it was by hand. I never had anyone to ask before.

this

20:15

@IvenBach uh, sort of but that's not a good analogy.

IvenBach

This is why I'm constantly asking. I may kind-sorta understand but miss things that make me really stumble later on.

this

better to think of it as something that you should include. If you can make a SELECT statement, that same SELECT statement can be copied'n'pasted into the select_statement portion.

because a view is basically just a saved SELECT statement after all.

IvenBach

mmk. Working on a simple select, from which I'll build up.

Duga

20:38

[rubberduck-vba/Rubberduck] rkapka commented on issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

> The dictionaries are assigned to the Properties dynamic object and accessed in the inspection's quick fix.

[rubberduck-vba/Rubberduck] rkapka assigned rkapka to issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

mansellan

20:57

@this WITH NOLOCK should be renamed WITH NOT A SINGLE DAMN GIVEN ABOUT GARBAGE RESULTS, imho :-)

this

totally! the most abused band aid that we never needed and shouldn't have had.

IvenBach

@this To change a view you prefer RtClick>Script View As>Alter To>New Query?

this

Sure.

Script As should be your bestest friend to infinity and beyond

#ProTip: want a quick way to build an INSERT INTO...SELECT where the table is the same? Script As -> INSERT, delete the VALUES portion, then `Script As -> SELECT -> To clipboard. Paste into the INSERT script. Now you only have to do few small tweaks instead of hand-typing all those columns!

Mathieu Guindon

@this C# is a language, therefore a spec, therefore a standard, by definition ;-) #Pedantic

oh you mean the way people write it

like Java braces in C#

mansellan

is this page out of date: github.com/rubberduck-vba/Rubberduck/wiki/…

I thought ANTLR had a c# port now?

I wanna start learning about the parser, but not enough to stomach installing Eclipse again...

this

21:09

@MathieuGuindon I was alluded to it earlier but not in that post you linked to. I still think that a one-vendor standard isn't really a standard. Standard implies something that you use among vendors. Otherwise, there's little to standardize on.

@mansellan no that's still valid for viewing the tree.

Mathieu Guindon

@mansellan saw this?

Understanding the VBA grammar, tokens, lexer, parser and parse trees

Mar 8 '17 at 4:01, 1 hour 44 minutes total – 397 messages, 6 users, 7 stars

Bookmarked Mar 8 '17 at 14:33 by Mathieu Guindon

this

which is not necessarily required for using it, which can be done purely in C#

Mathieu Guindon

@this AFAIK Mono is a non-MS implementation of the spec

mansellan

ok cool

is it not this: ecma-international.org/publications/standards/Ecma-334.htm?

have I misumderstood the q?

IvenBach

@this I'll appreciate what this represents after I understand more of the basics.

this

21:11

oh? I thought Mono was maintained by MS, ok that makes more sense then!

mansellan

@this ok thanks

this

@mansellan yes that's what I'm referring to earlier but I was under the mistaken impression that MS was the only one to implement the standard, which isn't that much of a standard.

FWIW.... if you really wanted, you could build your own C# viewer of the parse tree. ;)

mansellan

@this uh, i'll pass...

;-)

this

lol

hence why you need eclipse if you want to see it in action.

mansellan

ewww... holds nose

java makes me cry

Mathieu Guindon

21:13

meh.. VS debugger works just as good

this

IKR!

meh. debugger works but it pale in comparison to seeing it laid out in a tree.

if you have to open 20 submenus to make sense of it....

Mathieu Guindon

no way I'm going to put up with "A new version of Java is available!" every 20 minutes, followed by "Hey did you know Java runs on 3 billion devices" then by "OBTW PLEASE INSTALL THIS CRAPWARE AND THIS AND THAT AND OH HERE'S A BROWSER TOOLBAR AND ANOTHER POC YOU NEVER ASKED FOR"

mansellan

hmm... maybe I'll put Eclipse on a VM....

this

^ problem solved.

you can then unplug the VM's internet

mansellan

@MathieuGuindon AND BTW, JUST FOR GIGGLES WE BORKED THE JVM AGAIN. HAPPY RECOMPILING KTHXBYE!

this

21:15

Hence why unplugging the VM's internet should be mandatory. So that java never expires or try to autoupdate itself (and breaks)

Duga

[rubberduck-vba/Rubberduck] MDoerner commented on issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

> What is a bit of a concern for me is that the inspection does work for one quickfix while finding the results. The second dictionary actually dictates what labels the quickfix uses. In an ideal world the inspections would determine the offending contexts and the quickfixes would determine how to fix the results.

Regarding my comment about the first context, in the quickfix `result.Properties.BodyElement` should be equivalent to `result.Context.GetAncestor<VBAParser.ModuleBodyElementContext>

IvenBach

21:36

All that frustration I had with Access is now coming back with SSMS.

this

as i said you have some unlearning to do w/ RDBMS

IvenBach

I've forgotten anything I did with Access.

this

no, referring more to the fact that you are familiar with VBA and Excel, both which are a liability against SQL skills.

IvenBach

I'm trying to make field a primary key and cant figure it out.

this

PK is a constraint

IvenBach

21:38

Would that require Alter an the table?

this

ALTER TABLE foo ADD CONSTRAINT PK_foo PRIMARY KEY CLUSTERED (bar);

actually for now, you'll want your PK to be clustered by default, too

note that this is legal, too: ALTER TABLE foo ADD PRIMARY KEY (bar); but please don't use that. You will want to always name your constraints.

In SQL Server world, 3 things are constraints; the primary key, the foreign key and the default.

IvenBach

I'm trying to connect the wires between Alter Table Syntax and table_constraint.

Time to go back to writing it out by hand on paper for it to click.

this

Ooo, i forgot the CHECK constraint. So that's 4 types of constraints.

But yeah, you generally will use either ADD or DROP subbranch.

A constraint cannot be ALTER'd like a column could be.

do note that you additionally can define the constraints inline with a column definition.... something like this ALTER TABLE foo ADD bar int NOT NULL CONSTRAINT PK_foo PRIMARY KEY CLUSTERED (note that the column specifications is omitted since it's an inline definition)

IvenBach

I've understood up to ... CLUSTERED (bar);. What part does bar represent the following?

[ CONSTRAINT constraint_name ]
{
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        (column [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor
        [ WITH ( <index_option>[ , ...n ] ) ]
        [ ON { partition_scheme_name ( partition_column_name ... ) | filegroup | "default" } ]
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

this

bar => column

e.g. bar is a column on the table foo for which you are making a primary key

IvenBach

21:53

> column
Is a column or list of columns specified in parentheses that are used in a new constraint.

mmk.

Unfamiliarity and the myriad of [{(| opening and closing loose me...

this

yeah it doesn't help that there's lot of options that are... optional and are mostly noise

IvenBach

:hangs head: How do I make the field non-nullable?

I can do it easily via a designer but I'd rather know how to do it SQL wise.

this

you must include NOT NULL in the column definition

note that when you ALTER COLUMN, you must re-include the NOT NULL because it's basically a new column with no memory of any properties it previously had.

IvenBach

Let me struggle on this one. This is one I know I can overcome, given enough time.

So far I've condensed it down to ALTER TABLE dbo.foo ALTER COLUMN Name NOT NULL; but I'm getting NOT redlined with "incorrect syntax near not".

this

7 mins ago, by this

note that when you ALTER COLUMN, you must re-include the NOT NULL because it's basically a new column with no memory of any properties it previously had.

the lack of memory also includes its data type

IvenBach

22:05

:barf: Removing all the clutter I'm left with ALTER COLUMN column_name type_name NOT NULL

@this I see the words but they obviously didn't translate to comprehension then. Now it does.

this

keep at it. :)

IvenBach

22:25

I will. All this pain now will pay of in the longterm.

I can already see the situation that'll happen if all the information is kept in Excel workbooks.

Thank you for helping, again. Need to find some way to repay all this breadcrumbing and spoonfeeding.

Duga

[rubberduck-vba/Rubberduck] rkapka commented on issue #4002: OnErrorResumeNext Inspection can fail while walking ParseTrees

> I agree completely on the second point, this needs to be simplified using the extension method.

I also understand that the separation of concerns is violated here, but I can't think of a good alternative at the moment. I suppose I could find all existing labels inside the issue's body element and then deduce the new label name, but what if there are multiple issues in one procedure? If I understand the code in `QuickFixProvider` correctly, all fixes are applied before the module is rewritte

IvenBach

22:39

When altering a view to add an additional calculated field is that possible to do via ALTER VIEW or should that be done through Design?

Duga

[rubberduck-vba/Rubberduck] mansellan commented on issue #1505: Parser: More special forms

> So, now that #298 is closed, this needs revisiting. VB6 PSet has a funky syntax which our grammar hates:

Pset (x , y ), color

Note though that PSet exists on other objects (for example, PictureBox) where it follows a standard syntax.

[rubberduck-vba/Rubberduck] mansellan added label vb6-specific to issue #1505: Parser: More special forms

mansellan

@Duga Looks like PSet, Line and Circle are special-cased by the VB6 compiler

Duga

23:09

[rubberduck-vba/Rubberduck] mansellan commented on issue #1505: Parser: More special forms

> So, now that #298 is closed, this needs revisiting.

VB6 appears to special-case three drawing methods:

object.Line [Step] (x1, y1) [Step] -(x2, y2), [color],[B][F]
object.Circle [Step] (x, y), radius, [color], [start], [end], [aspect]
object.Pset [Step] (x , y), [color]

These get special treatment to allow the points to be supplied as a bracketed pair (which appears to be a legacy from QBasic). I've not found any other special forms yet.

[rubberduck-vba/Rubberduck] mansellan commented on issue #1505: Parser: More special forms

> So, now that #298 is closed, this needs revisiting.

VB6 appears to special-case three drawing methods:

object.Line [Step] (x1, y1) [Step] -(x2, y2), [color],[B][F]
object.Circle [Step] (x, y), radius, [color], [start], [end], [aspect]
object.Pset [Step] (x , y), [color]

These get special treatment to allow the points to be supplied as a bracketed pair (which appears to be a legacy from QBasic), and in the case of Line, to allow B and F as unquoted flags. It appears that object must

IvenBach

23:45

Home Time

@this Appreciate your help as always.

Understanding the VBA grammar, tokens, lexer, parser and parse trees

Transcript for