Board index » Visual Studio » SQL error or VB?

Visual Studio328
I can take this to the SQL corner if you want me to, but I'm not letting go

of you guys/girls too easy.



sql = "SELECT * " & _

"FROM Pop3LocalMailBox AS T1 " & _

"INNER JOIN TMP_EmailB AS T2 " & _

"ON T1.Pop3From = T2.hptFrom OR " & _

"ON T1.Subject = T2.Subject"



Why do I get:



Syntax error (missing operator) in query expression 'T1.Pop3From =

T2.hptFrom OR ON T1.Subject = T2.Subject'



BT3


-

"BT3" <honeypot@epmctc.com>wrote in message

Because "OR ON" is not valid syntax; try just OR



--

Reply to the group so all can participate

VB.Net: "Fool me once..."



-

"BT3" <honeypot@epmctc.com>wrote in message



As Bob said, the second ON is not allowed. However, are you allowed to use

OR in a JOIN? I really don't think so.





-

yeah, it works.

bt3



"Jeff Johnson [MVP:VB]" <i.get@enough.spam>wrote in message



-

"BT3" <honeypot@epmctc.com>wrote in message



Interesting. I just looked it up in SQL Server Books Online and the search

condition can contain "any predicate." I have never seen a case where I'd

use an OR condition in a join, probably because It reeks of bad table

design. Joins should be performed on keys and other selection criteria

should be handled in the WHERE clause.





-

That hurts. Or seriously complex tables. We don't have the luxury of

hiring a team of hardened database designers. My 25 years of database

design and knowledge of every PC level programming language known to man has

to hold us for now. It's the intracacies I need help with sometimes.



If The current mailbox for all users has any mail in it that matches banned

FROM addresses, -OR- any 10 character checksummed part of the the subject

tag, then get the intended recipient from the temporary banned hold table,

and use the intended recipient to get the real Account Name from the local

POP3 account definition table, and make a new entry in the BlockedTags for

each record found, if and only if that account name has selected to opt-in

to receiving such notifications.



IOW:



sql2 = "SELECT T3.Pop3Email, T2.hptFrom, T2.hptSubject " & _

"FROM Pop3Local AS T3 " & _

"INNER JOIN (Pop3LocalMailBox AS T1 " & _

"INNER JOIN Tmp_EmailB AS T2 " & _

"ON T1.Pop3From = T2.hptFrom " & _

"OR T1.Subject = T2.hptSubject) " & _

"ON T3.[Account Name] = T1.Pop3To " & _

"WHERE T3.StatusEmails = True"

Set rsStage2 = dbHP.OpenRecordset(sql2)



'rsStage2 contains 1 record for each record in rsStage1

With rsStage2

If .RecordCount>0 Then

.MoveFirst

While Not .EOF

sql = "INSERT INTO BlockNotify " & _

"(BlockedFrom, BlockedTo, BlockedSubject,

BlockedDateTime) " & _

"VALUES (" & quote & ![hptFrom] & quote & ", " & _

quote & ![Pop3Email] & quote & ", " &

_

quote & ![hptSubject] & quote & ", "

& _

quote & Now() & quote & ")"

dbHP.Execute sql

.MoveNext

Wend

End If

.Close

End With

Set rsStage2 = Nothing



Not only does it work, but given a set of data from a real user, it parses

over a million records from over 300 mailboxes 12 seconds faster than my

previous "old school" method, freeing valuable processor slices for actual

Winsock tasking. The code has been field tested for over two years now, and

battle hardened. It's efficiency I seek now, and yes, sometimes that

involves minor database design changes to correct lack of foresight issues.



BT3



"Jeff Johnson [MVP:VB]" <i.get@enough.spam>wrote in message



-

I bet you'd be amazed at what you can do with DAO and an

Access .mdb. How much time does it take to generate your

report? How many records are read?



--

Jim Carlock

Post replies to newsgroup.



"BT3" wrote:

That hurts. Or seriously complex tables. We don't have the luxury of

hiring a team of hardened database designers. My 25 years of database

design and knowledge of every PC level programming language known to man has

to hold us for now. It's the intracacies I need help with sometimes.



If The current mailbox for all users has any mail in it that matches banned

FROM addresses, -OR- any 10 character checksummed part of the the subject

tag, then get the intended recipient from the temporary banned hold table,

and use the intended recipient to get the real Account Name from the local

POP3 account definition table, and make a new entry in the BlockedTags for

each record found, if and only if that account name has selected to opt-in

to receiving such notifications.



IOW:



sql2 = "SELECT T3.Pop3Email, T2.hptFrom, T2.hptSubject " & _

"FROM Pop3Local AS T3 " & _

"INNER JOIN (Pop3LocalMailBox AS T1 " & _

"INNER JOIN Tmp_EmailB AS T2 " & _

"ON T1.Pop3From = T2.hptFrom " & _

"OR T1.Subject = T2.hptSubject) " & _

"ON T3.[Account Name] = T1.Pop3To " & _

"WHERE T3.StatusEmails = True"

Set rsStage2 = dbHP.OpenRecordset(sql2)



'rsStage2 contains 1 record for each record in rsStage1

With rsStage2

If .RecordCount>0 Then

.MoveFirst

While Not .EOF

sql = "INSERT INTO BlockNotify " & _

"(BlockedFrom, BlockedTo, BlockedSubject,

BlockedDateTime) " & _

"VALUES (" & quote & ![hptFrom] & quote & ", " & _

quote & ![Pop3Email] & quote & ", " &

_

quote & ![hptSubject] & quote & ", "

& _

quote & Now() & quote & ")"

dbHP.Execute sql

.MoveNext

Wend

End If

.Close

End With

Set rsStage2 = Nothing



Not only does it work, but given a set of data from a real user, it parses

over a million records from over 300 mailboxes 12 seconds faster than my

previous "old school" method, freeing valuable processor slices for actual

Winsock tasking. The code has been field tested for over two years now, and

battle hardened. It's efficiency I seek now, and yes, sometimes that

involves minor database design changes to correct lack of foresight issues.



BT3



"Jeff Johnson [MVP:VB]" wrote:



-

I haven't timed the loop given below by itself. That is a re-done "middle"

and all it does is save a few details concerning the blocks it will make, so

that every night at midnight, it can email the user informing him/her of the

blocks it has made on their behalf in the previous 24 hour table. The

actual analyzing is done real time as mail comes in. The entire loop I have

timed. It consists of:



1) Empty temporary tables from previous run

2) Create temporary tables containing relevant data (hence the ON OR)

3) Populate said tables with triggers and relevant data accumulated from

bans

4) Preview the future blocks and store their basic details for future

handling (afore mentioned loop)

5) Search the mail account files for matches, and delete them if they are

not from trusted sources or networks, or ambiguous.

6) Flag the ambiguous records for end user approval if user has so opted-in,

queue for delivery if not

7) Use acquired data from step 4, and prepare the ban records for

transmission to the peer-peer network

8) Update all the relevant [Flags] to prevent double processing



The entire loop started at a little more than 11 minutes using "old school"

manual cycling code. After hundreds of trips to my MSDN CD's I have rid

myself (for now) of user complaints by dropping the loop down to about 7

seconds, but that still allows handling of an untold number of WinSock

connections (server and client).



There are about ten tables altogether. I have data from a customer that I

am using to time and develop with. The current raw bans have a bit over

700K records right now, but they expire and new ones always come in. This

produces about 450,000 or so direct template records that will be used to

make matches, and about 200 server records which will yield an email header

search to make those matches. There are currently over 300 email accounts,

about a third are many-to-one (1 account might have three email addresses).

I don't have the stats for that on-hand. There are currently a bit over

1200 emails holding for delivery. And there are right now 243 records in

the "Notify users of blocks" table. This table will eventually hold 98.86%

(currently) of the entries in the original "Email holding" table.



It's not a report, it just autonomous background noise. It maintains itself

on timer intervals or external and internal triggers.



BT3



"Jim Carlock" <anonymous@localhost.com>wrote in message



-

What's taking the time? 1200 emails waiting to be delivered

sounds like a definitely dent. With SMTP going over TCP,

a connection must be established, then the info gets sent,

TCP does some checking, then closes the connection. It's

the connect and disconnect and maybe the error checking

of TCP that seems to be the slowness of such. It provides

a considerable amount of reliability though.



I might have misunderstood something. 12 seconds in running

a set of queries against any database seems quite high, even

with a million records involved... unless your using TCP to

transfer the data and your emailing 1200 emails every 12

seconds, then that's a different story.



--

Jim Carlock

Post replies to newsgroup.



"BT3" wrote"

I haven't timed the loop given below by itself. That is a re-done "middle"

and all it does is save a few details concerning the blocks it will make, so

that every night at midnight, it can email the user informing him/her of the

blocks it has made on their behalf in the previous 24 hour table. The

actual analyzing is done real time as mail comes in. The entire loop I have

timed. It consists of:



1) Empty temporary tables from previous run

2) Create temporary tables containing relevant data (hence the ON OR)

3) Populate said tables with triggers and relevant data accumulated from

bans

4) Preview the future blocks and store their basic details for future

handling (afore mentioned loop)

5) Search the mail account files for matches, and delete them if they are

not from trusted sources or networks, or ambiguous.

6) Flag the ambiguous records for end user approval if user has so opted-in,

queue for delivery if not

7) Use acquired data from step 4, and prepare the ban records for

transmission to the peer-peer network

8) Update all the relevant [Flags] to prevent double processing



The entire loop started at a little more than 11 minutes using "old school"

manual cycling code. After hundreds of trips to my MSDN CD's I have rid

myself (for now) of user complaints by dropping the loop down to about 7

seconds, but that still allows handling of an untold number of WinSock

connections (server and client).



There are about ten tables altogether. I have data from a customer that I

am using to time and develop with. The current raw bans have a bit over

700K records right now, but they expire and new ones always come in. This

produces about 450,000 or so direct template records that will be used to

make matches, and about 200 server records which will yield an email header

search to make those matches. There are currently over 300 email accounts,

about a third are many-to-one (1 account might have three email addresses).

I don't have the stats for that on-hand. There are currently a bit over

1200 emails holding for delivery. And there are right now 243 records in

the "Notify users of blocks" table. This table will eventually hold 98.86%

(currently) of the entries in the original "Email holding" table.



It's not a report, it just autonomous background noise. It maintains itself

on timer intervals or external and internal triggers.



BT3



"Jim Carlock" <anonymous@localhost.com>wrote in message





-

Ha, yeah I know, by itself, but there are active Winsock sonnections going

on. I just checked, and there were about 30 or so last time (best guess from

their logs). Only took 2 seconds on my machine without any Winsocks hot.



BT3



"Jim Carlock" <anonymous@localhost.com>wrote in message



-