问:
How to add remote interpreter via ipv6 in Pycharm?
My server has only an ipv6 address, and my computer can directly ssh to it via this address. But when I tried to add the remote interpreter on this server in Pycharm, it tells me SFTP host is invalid when it sees the ipv6 address.

How can I make it work? Thank you!

Update: adding brackets [2001:da8:...] helped pass the host syntax check. But now upon clicking "Ok", Pycharm tells me "java.net.SocketException: Protocol family unavailable". How to pass this?

答:
Finally I got that to work.

First, a valid IPV6 SFTP address must be wrapped in brackets. That is, instead of 2001:da8:201:1146:226:b9ff:...., you should write [2001:da8:201:1146:226:b9ff:....].

Second, if you do so you will find Pycharm telling you "java.net.SocketException: Protocol family unavailable" and refuse to connect. This is an issue of Pycharm running under JDK 1.7 (yes, Pycharm is based on Java). To solve this:

Open Help | Edit Custom VM Options

Add -Djava.net.preferIPv6Addresses=true in it. You may also want to delete -Djava.net.preferIPv4Addresses=true but I'm not sure whether it matters.

Restart Pycharm.

See here for more information:https://intellij-support.jetbrains.com/hc/en-us/articles/207241215-Network-connectivity-issues-when-running-under-Java-1-7


来源: stackexchange

问:
Extract thumbnail for any file in Windows C#
I need to extract a thumbnail for uploaded files like ppt, word files, images etc. I have implemented this (Extract thumbnail for any file in Windows) answer but I am getting files with black background. On one machine it works on other it gives thumbnail with black background.

答: I don't know if that is possible. For what i know pictureboxes don't support .ico files, so you would have to convert the icon to a supported format.</p>

ContentLicense: CC BY-SA 3.0


问:
Paypal with Braintree saying "Merchant account not found"
I tried integrating PayPal using braintree and following this tutorial. I end up getting an error: Merchant account not found - screenshot.

I am using Swift 3 and Xcode 8.1.

here is my viewDidLoad

These are all required updates in my app:

ScreenShot 1

ScreenShot 2

ScreenShot 3

ScreenShot 4

ScreenShot 5

Any help will be appreciated!

答:
PayPal with Braintree didn't work for me like I was getting this message again and again.

"Merchant account not found"

So, I decided to integrate PayPal ios SDK and I managed to integrate Future payment myself within the app(without using backend server). And that worked flawlessly with the help of PayPal developer support.


问:
How to get the sum of column in SQL
I am developing the stored procedure and have a data as below

```<code>ID Name Value
1  All  0
2  ABC  10
3  DEF  15
4  GHI  20
</code>```

Now, my requirement is to calculate the sum of all columns except with Name as 'All' and it should show it as a value of 'All'.

So the expected result should be like this.

```<code>ID Name Value
1  All  45
2  ABC  10
3  DEF  15
4  GHI  20
</code>```

How do I achieve this using view/stored procedure in SQL?

答:
Do sum in Sub query and Handle the ALL row with case

Schema:

```<code>CREATE TABLE #TAB (ID int, Name Varchar(20),Value int)

INSERT INTO #TAB
SELECT 1,  'All',  0
UNION ALL
SELECT 2,  'ABC',  10
UNION ALL
SELECT 3,  'DEF',  15
UNION ALL
SELECT 4,  'GHI',  20
</code>```

Now Select it with case expression

```<code>SELECT ID
    ,NAME
    ,CASE
        WHEN NAME = 'All'
            THEN (
                    SELECT SUM(Value)
                    FROM #TAB
                    WHERE NAME <> 'ALL'
                    )
        ELSE VALUE
        END AS VALUE
FROM #TAB
</code>```

And the result will be

```<code>+----+------+-------+
| ID | NAME | VALUE |
+----+------+-------+
|  1 | All  |    45 |
|  2 | ABC  |    10 |
|  3 | DEF  |    15 |
|  4 | GHI  |    20 |
+----+------+-------+
</code>```

Title: How to get the sum of column in SQL

Body:
I am developing the stored procedure and have a data as below

ID Name Value
1  All  0
2  ABC  10
3  DEF  15
4  GHI  20

Now, my requirement is to calculate the sum of all columns except with Name as 'All' and it should show it as a value of 'All'.

So the expected result should be like this.

ID Name Value
1  All  45
2  ABC  10
3  DEF  15
4  GHI  20

How do I achieve this using view/stored procedure in SQL?

Body:
Do sum in Sub query and Handle the ALL row with case

Schema:

CREATE TABLE #TAB (ID int, Name Varchar(20),Value int)

INSERT INTO #TAB
SELECT 1,  'All',  0
UNION ALL
SELECT 2,  'ABC',  10
UNION ALL
SELECT 3,  'DEF',  15
UNION ALL
SELECT 4,  'GHI',  20

Now Select it with case expression

SELECT ID
    ,NAME
    ,CASE
        WHEN NAME = 'All'
            THEN (
                    SELECT SUM(Value)
                    FROM #TAB
                    WHERE NAME <> 'ALL'
                    )
        ELSE VALUE
        END AS VALUE
FROM #TAB

And the result will be

+----+------+-------+
| ID | NAME | VALUE |
+----+------+-------+
|  1 | All  |    45 |
|  2 | ABC  |    10 |
|  3 | DEF  |    15 |
|  4 | GHI  |    20 |
+----+------+-------+


问:
How to check if a variable is an integer in JavaScript?
How do I check if a variable is an integer in JavaScript, and throw an alert if it isn't?  I tried this, but it doesn't work:</p>

```<code><html>
    <head>
        <script type="text/javascript">
            var data = 22;
            alert(NaN(data));
        </script>
    </head>
</html>
</code>```

答:
The simplest and cleanest pre-ECMAScript-6 solution (which is also sufficiently robust to return false even if a non-numeric value such as a string or null is passed to the function) would be the following:

```<code>function isInteger(x) { return (x^0) === x; }
</code>```

The following solution would also work, although not as elegant as the one above:

```<code>function isInteger(x) { return Math.round(x) === x; }
</code>```

Note that Math.ceil() or Math.floor() could be used equally well (instead of Math.round()) in the above implementation.

Or alternatively:

```<code>function isInteger(x) { return (typeof x === 'number') && (x % 1 === 0); }
</code>```

One fairly common incorrect solution is the following:

```<code>function isInteger(x) { return parseInt(x, 10) === x; }
</code>```

While this parseInt-based approach will work well for many values of x, once x becomes quite large, it will fail to work properly. The problem is that parseInt() coerces its first parameter to a string before parsing digits. Therefore, once the number becomes sufficiently large, its string representation will be presented in exponential form (e.g., 1e+21). Accordingly, parseInt() will then try to parse 1e+21, but will stop parsing when it reaches the e character and will therefore return a value of 1. Observe:

```<code>> String(1000000000000000000000)
'1e+21'

> parseInt(1000000000000000000000, 10)
1

> parseInt(1000000000000000000000, 10) === 1000000000000000000000
false
</code>```

Body:
The simplest and cleanest pre-ECMAScript-6 solution (which is also sufficiently robust to return false even if a non-numeric value such as a string or null is passed to the function) would be the following:

function isInteger(x) { return (x^0) === x; }

The following solution would also work, although not as elegant as the one above:

function isInteger(x) { return Math.round(x) === x; }

Note that Math.ceil() or Math.floor() could be used equally well (instead of Math.round()) in the above implementation.

Or alternatively:

function isInteger(x) { return (typeof x === 'number') && (x % 1 === 0); }

One fairly common incorrect solution is the following:

function isInteger(x) { return parseInt(x, 10) === x; }

While this parseInt-based approach will work well for many values of x, once x becomes quite large, it will fail to work properly. The problem is that parseInt() coerces its first parameter to a string before parsing digits. Therefore, once the number becomes sufficiently large, its string representation will be presented in exponential form (e.g., 1e+21). Accordingly, parseInt() will then try to parse 1e+21, but will stop parsing when it reaches the e character and will therefore return a value of 1. Observe:

> String(1000000000000000000000)
'1e+21'

> parseInt(1000000000000000000000, 10)
1

> parseInt(1000000000000000000000, 10) === 1000000000000000000000
false


问:
Change one column value for a specific group of variables
I have the following dataset:

```<code>help <- data.frame(id = c(rep(5, times = 5), rep(12, times = 3), rep(33, times = 2), rep(54, times = 3), rep(66, times = 4)),
                   ob = c(rep(1, times = 5), rep(0, times = 3), rep(1, times = 2), rep(0, times =3), rep(1, times =4)))
</code>```

I am trying to change only the values for id = 5 and 66 from 1 to 0 so the df looks like this:

```<code>    id ob
1   5  0
2   5  0
3   5  0
4   5  0
5   5  0
6  12  0
7  12  0
8  12  0
9  33  1
10 33  1
11 54  0
12 54  0
13 54  0
14 66  0
15 66  0
16 66  0
17 66  0
</code>```

I have attempted:

```<code>change <- c(5, 66)
mutate(help, ob = ifelse(xor(id %in% change), ob == 0, 1))
</code>```

But I keep hitting errors and I don't want to change the values of any other id, only the ones specified. I could do this the long way by filtering out and replacing and joining back in, but guessing there is a quicker solution.  Any help would be great.

答:
We can do it also the data.table way:

```<code>require(data.table)
setDT(help)
help[id %in% c(5, 66), ob := 0]
</code>```

Body:
I have the following dataset:

help <- data.frame(id = c(rep(5, times = 5), rep(12, times = 3), rep(33, times = 2), rep(54, times = 3), rep(66, times = 4)),
                   ob = c(rep(1, times = 5), rep(0, times = 3), rep(1, times = 2), rep(0, times =3), rep(1, times =4)))

I am trying to change only the values for id = 5 and 66 from 1 to 0 so the df looks like this:

    id ob
1   5  0
2   5  0
3   5  0
4   5  0
5   5  0
6  12  0
7  12  0
8  12  0
9  33  1
10 33  1
11 54  0
12 54  0
13 54  0
14 66  0
15 66  0
16 66  0
17 66  0

I have attempted:

change <- c(5, 66)
mutate(help, ob = ifelse(xor(id %in% change), ob == 0, 1))

But I keep hitting errors and I don't want to change the values of any other id, only the ones specified. I could do this the long way by filtering out and replacing and joining back in, but guessing there is a quicker solution.  Any help would be great.

Body:
We can do it also the data.table way:

require(data.table)
setDT(help)
help[id %in% c(5, 66), ob := 0]


问:
How to display text stored in firebase in android?
I am writing a code for displaying the images about the different cities that my company lets the user visits. I want to display the information about what you are gonna do and travelling to the places in the company's list. I have uploaded the information using a text file in firebase storage. And when I click on my activity, I want to display the information using the text file stored in the firebase. I am not able to do so. I have tried using images and have fetched the images from firebase, but I am still not able to fetch text file. How should I do it?</p>

答:
Download file from firebase and use it..

```<code>Refer this link:
https://firebase.google.com/docs/storage/android/download-files
</code>```

After download file, read the content from file and display in your textview.

Body:
Download file from firebase and use it..

Refer this link:
https://firebase.google.com/docs/storage/android/download-files

After download file, read the content from file and display in your textview.


问:
pandas - How to check consecutive order of dates and copy groups of them?
At first I have two problems, the first will follow now:

I a dataframe df with many times the same userid and along with it a date and some unimportant other columns:

```<code>    userid   date
0    243     2014-04-01
1    234     2014-12-01
2    234     2015-11-01
3    589     2016-07-01
4    589     2016-03-01
</code>```

I am currently trying to groupby them by userid and sort the dates descending and cut out the twelve oldest. My code looks like this:

```<code>df = df.groupby(['userid'], group_keys=False).agg(lambda x: x.sort_values(['date'], ascending=False, inplace=False).head(12))
</code>```

And I get this error:

```<code>ValueError: cannot copy sequence with size 6 to array axis with dimension 12
</code>```

At the moment my aim is to avoid to split the dataframe in individual ones.

My second problem is more complex:

I try to find out if the sorted dates (respectively per group of userids) are monthly consecutive. This means if there is an date for one group of  userid, for example userid: 234 and date: 2014-04-01, the next entry below must be userid: 234 and date:2014-03-01. There is no focus on the day, only the year and month are important.
And only this consecutive 12 dates should be copied in another dataframe.

A second dataframe df2 contains the same userid, but they are unique and another column is 'code'. Here is an example:

```<code>         userid  code
0          433805  1
24          5448   0
48          3434   1
72          34434  1
96          3202   1
120         23766  1
153         39457  0
168         4113   1
172         3435   5
374         34093  1
</code>```

I summarize: I try to check if there are 12 consecutive months per userid and copy every correct sequence in another dataframe. For this I have also compare the 'code' from df2.

This is a version of my code:

```<code>df['YearMonthDiff'] = df['date'].map(lambda x: 1000*x.year + x.month).diff()
    df['id_before'] = df['userid'].shift()
    final_df = pd.DataFrame()
    for group in df.groupby(['userid'], group_keys=False):
        fi = group[1]
        if (fi['userid'] <> fi['id_before']) &  group['YearMonthDiff'].all(-1.0) & df.loc[fi.userid]['code'] != 5:
            final_df.append(group['userid','date', 'consum'])
</code>```

At first calculated from the date an integer and made diff(). On other posts I saw they shift the column to compare the values from the current row and the row before. Then I made groupby(userid) to iterate over the single groups. Now it's extra ugly I tried to find the beginning of such an userid-group, try to check if there are only consecutive months and the correct 'code'. And at least I append it on the final dataframe.

On of the biggest problems is to compare the row with the following row. I can iterate over them with iterrow(), but I cannot compare them without shift(). There exits a calendar function, but on these I will take a look on the weekend. Sorry for the mess I am new to pandas.

Has anyone an idea how to solve my problem?

答:
Using groupby and nlargest, we get the index values of those largest dates.  Then we use .loc to get just those rows

```<code>df.loc[df.groupby('userid').date.nlargest(12).index.get_level_values(1)]
</code>```

Consider the dataframe df

```<code>dates = pd.date_range('2015-08-08', periods=10)
df = pd.DataFrame(dict(
        userid=np.arange(2).repeat(4),
        date=np.random.choice(dates, 8, False)
    ))

print(df)

        date  userid
0 2015-08-12       0  # <-- keep
1 2015-08-09       0
2 2015-08-11       0
3 2015-08-15       0  # <-- keep
4 2015-08-13       1
5 2015-08-10       1
6 2015-08-17       1  # <-- keep
7 2015-08-16       1  # <-- keep
</code>```

We'll keep the latest 2 dates per user id

```<code>df.loc[df.groupby('userid').date.nlargest(2).index.get_level_values(1)]

        date  userid
0 2015-08-12       0
3 2015-08-15       0
6 2015-08-17       1
7 2015-08-16       1
</code>```

Body:
At first I have two problems, the first will follow now:

I a dataframe df with many times the same userid and along with it a date and some unimportant other columns:

    userid   date
0    243     2014-04-01
1    234     2014-12-01
2    234     2015-11-01
3    589     2016-07-01
4    589     2016-03-01

I am currently trying to groupby them by userid and sort the dates descending and cut out the twelve oldest. My code looks like this:

df = df.groupby(['userid'], group_keys=False).agg(lambda x: x.sort_values(['date'], ascending=False, inplace=False).head(12))

And I get this error:

ValueError: cannot copy sequence with size 6 to array axis with dimension 12

At the moment my aim is to avoid to split the dataframe in individual ones.

My second problem is more complex:

I try to find out if the sorted dates (respectively per group of userids) are monthly consecutive. This means if there is an date for one group of  userid, for example userid: 234 and date: 2014-04-01, the next entry below must be userid: 234 and date:2014-03-01. There is no focus on the day, only the year and month are important.
And only this consecutive 12 dates should be copied in another dataframe.

A second dataframe df2 contains the same userid, but they are unique and another column is 'code'. Here is an example:

         userid  code
0          433805  1
24          5448   0
48          3434   1
72          34434  1
96          3202   1
120         23766  1
153         39457  0
168         4113   1
172         3435   5
374         34093  1

I summarize: I try to check if there are 12 consecutive months per userid and copy every correct sequence in another dataframe. For this I have also compare the 'code' from df2.

This is a version of my code:

df['YearMonthDiff'] = df['date'].map(lambda x: 1000*x.year + x.month).diff()
    df['id_before'] = df['userid'].shift()
    final_df = pd.DataFrame()
    for group in df.groupby(['userid'], group_keys=False):
        fi = group[1]
        if (fi['userid'] <> fi['id_before']) &  group['YearMonthDiff'].all(-1.0) & df.loc[fi.userid]['code'] != 5:
            final_df.append(group['userid','date', 'consum'])

At first calculated from the date an integer and made diff(). On other posts I saw they shift the column to compare the values from the current row and the row before. Then I made groupby(userid) to iterate over the single groups. Now it's extra ugly I tried to find the beginning of such an userid-group, try to check if there are only consecutive months and the correct 'code'. And at least I append it on the final dataframe.

On of the biggest problems is to compare the row with the following row. I can iterate over them with iterrow(), but I cannot compare them without shift(). There exits a calendar function, but on these I will take a look on the weekend. Sorry for the mess I am new to pandas.

Has anyone an idea how to solve my problem?

Body:
Using groupby and nlargest, we get the index values of those largest dates.  Then we use .loc to get just those rows

df.loc[df.groupby('userid').date.nlargest(12).index.get_level_values(1)]

Consider the dataframe df

dates = pd.date_range('2015-08-08', periods=10)
df = pd.DataFrame(dict(
        userid=np.arange(2).repeat(4),
        date=np.random.choice(dates, 8, False)
    ))

print(df)

        date  userid
0 2015-08-12       0  # <-- keep
1 2015-08-09       0
2 2015-08-11       0
3 2015-08-15       0  # <-- keep
4 2015-08-13       1
5 2015-08-10       1
6 2015-08-17       1  # <-- keep
7 2015-08-16       1  # <-- keep

We'll keep the latest 2 dates per user id

df.loc[df.groupby('userid').date.nlargest(2).index.get_level_values(1)]

        date  userid
0 2015-08-12       0
3 2015-08-15       0
6 2015-08-17       1
7 2015-08-16       1


问:
UITableView clear data
How to clear whole tableview data and reload with new one?</p>

答:
ok.. but.. we need the solution for this.. if we add the cells in runtime, i have received the output with the same cells in first and last cells of the uitableview..

you can clear the cell before add any subviews

Example:

```<code>[mytableview.contentView clearsContextBeforeDrawing];
</code>```

OwnerDisplayName: Mike

ContentLicense: CC BY-SA 2.5

Title: UITableView clear data

Body:
ok.. but.. we need the solution for this.. if we add the cells in runtime, i have received the output with the same cells in first and last cells of the uitableview..

you can clear the cell before add any subviews

Example:

[mytableview.contentView clearsContextBeforeDrawing];


问:
Xamarin.Forms - InitializeComponent doesn't exist when creating a new page
I'm using Visual Studio to try out Xamarin.Forms. I'm trying to follow the guide:
http://developer.xamarin.com/guides/cross-platform/xamarin-forms/xaml-for-xamarin-forms/getting_started_with_xaml/

In short, I create a Xamarin.Forms solution using a PCL and then try to add a Forms XAML Page to the PCL-project.

The code-behind that gets created looks like this:

    public partial class Page1 : ContentPage
    {
        public Page1()
        {
            InitializeComponent();
        }
    }
```

<p>The problem here is that <code>InitializeComponent();</code> is red.
When I try to build I get informed that <code>The name 'InitializeComponent' does not exist in the current context</code>  </p>

<p>I've been looking around for solutions and even though others have had the same trouble, their solutions wont work for me. Here is one suggestion i tried to use:
http://blog.falafel.com/xamarin-error-initializecomponent-does-not-exist-in-the-current-context/</p>

<p>Please let me know if you have a solution for this problem. Thanks!</p>

<p>Update:</p>

<p>My PCL (which is where I also want to add my XAML-page) contains:</p>

<p>App.cs:</p>

<pre class="lang-cs prettyprint-override"><code>    public class App : Application
    {
        public App()
        {
            // The root page of your application
            MainPage = new ContentPage
            {
                Content = new StackLayout
                {
                    VerticalOptions = LayoutOptions.Center,
                    Children = {
                        new Label {
                            XAlign = TextAlignment.Center,
                            Text = "Welcome to Xamarin Forms!"
                        }
                    }
                }
            };
        }

        protected override void OnStart()
        {
            // Handle when your app starts
        }

        protected override void OnSleep()
        {
            // Handle when your app sleeps
        }

        protected override void OnResume()
        {
            // Handle when your app resumes
        }
    }
</code>```

And my XAML-page:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="XamaTest.MyXamlPage">

</ContentPage>
```

<p>Code-behind:</p>

 <pre class="lang-cs prettyprint-override"><code>    public partial class MyXamlPage : ContentPage
    {
        public MyXamlPage()
        {
            InitializeComponent();
        }
    }
</code>```

答:
I came across this error when;

I removed a ContentPage-cs+xaml and the App-cs+xaml from the project without actually deleting it.

Re-added these to the project in another folder.

This was fixed by;

Select the .xaml file of the class in which the issue is present.

Right click and select properties.

In Build action select "Embedded Resource"

In Custom Tool  type "MSBuild:UpdateDesignTimeXaml"

Clean and Build and it was gone.

Body:
I'm using Visual Studio to try out Xamarin.Forms. I'm trying to follow the guide:
http://developer.xamarin.com/guides/cross-platform/xamarin-forms/xaml-for-xamarin-forms/getting_started_with_xaml/

In short, I create a Xamarin.Forms solution using a PCL and then try to add a Forms XAML Page to the PCL-project.

The code-behind that gets created looks like this:

    public partial class Page1 : ContentPage
    {
        public Page1()
        {
            InitializeComponent();
        }
    }

The problem here is that InitializeComponent(); is red.
When I try to build I get informed that The name 'InitializeComponent' does not exist in the current context

I've been looking around for solutions and even though others have had the same trouble, their solutions wont work for me. Here is one suggestion i tried to use:
http://blog.falafel.com/xamarin-error-initializecomponent-does-not-exist-in-the-current-context/

Please let me know if you have a solution for this problem. Thanks!

Update:

My PCL (which is where I also want to add my XAML-page) contains:

App.cs:

    public class App : Application
    {
        public App()
        {
            // The root page of your application
            MainPage = new ContentPage
            {
                Content = new StackLayout
                {
                    VerticalOptions = LayoutOptions.Center,
                    Children = {
                        new Label {
                            XAlign = TextAlignment.Center,
                            Text = "Welcome to Xamarin Forms!"
                        }
                    }
                }
            };
        }

        protected override void OnStart()
        {
            // Handle when your app starts
        }

        protected override void OnSleep()
        {
            // Handle when your app sleeps
        }

        protected override void OnResume()
        {
            // Handle when your app resumes
        }
    }

And my XAML-page:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="XamaTest.MyXamlPage">

</ContentPage>

Code-behind:

    public partial class MyXamlPage : ContentPage
    {
        public MyXamlPage()
        {
            InitializeComponent();
        }
    }


问:
Create multiple temp tables in stored procedure using Prepared queries not working
Setup

MySQL 5.7 or 5.6

In the Below Stored procedure what I'm trying to do is create a few Temp Tables.    Some of which are simple and static, and 2 of which are dynamic (TempQuery1, TempQuery2).

All temp tables are Built -> Prepared -> executed first.  Then the main query that utilizes the temp tables (GetQuery) is executed.

Problem

When executed exactly like it is on a fresh connection, I get

```<code> Error Code: 1146. Table 'Job1111.TempPipeER' doesn't exist
</code>```

Which lead me to think I had a typeo or something.. In testing I found that if I follow the next couple steps t works fine.

Comment out Prepare and Execute for both Query2 and GetQuery

Save the stored procedure and run it 2 times. (getting expected error from Deallocate)

Uncomment out Query 2.

Save the stored procedure and run it 2 more times. (getting expected error from Deallocate)

Finally uncomment all Prepares and Executes

Save and run stored procedure... NOW it works.

Question

Why am I having to go through these step to let it build the temp tables one at a time.

Does a following execute wait for previous to finish before starting?

Is there a way to force the procedure to wait for an execute/ table build to finish?

Is there a way to signal/echo/console.log() type of method that could be used to accurately show the completion of a temp table build and the start of an execute to prove that is even my real problem?

Observation/Guess

The way it is acting is like the Execute BuildGetQuery is executing before TempTable Job1111.TempPipeER is finished building.  And sine GetQuery utilizes the TempPipeER able in a Join.  The GetQuery fails.

But if I slowly build each table one at a time, then it works fine for the duration of that MySQL work bench session.

Stored Procedure Create Statement

```DROP PROCEDURE IF EXISTS `Job1111`.`GetPipeStats`;
DELIMITER $$
CREATE DEFINER=`PSAAdmin`@`%` PROCEDURE `Job1111`.`GetPipeStats`( IN GroupedBy varchar(24))
BEGIN

### Declare Variables ###
DECLARE MechHoursPercent DECIMAL(3,2);
DECLARE TestingHoursPercent DECIMAL(3,2);
DECLARE CompleteForTestPercent DECIMAL(3,2);
DECLARE HydroHoursPercent DECIMAL(3,2);
DECLARE ClientTurnoverPercent DECIMAL(3,2);

### Set Variables ###
SELECT Value1 INTO MechHoursPercent
FROM `Job1111`.SETTINGS WHERE Label = "MechHoursPercent";
SELECT Value1 INTO TestingHoursPercent
FROM `Job1111`.SETTINGS WHERE Label = "TestingHoursPercent";
SELECT Value1 INTO CompleteForTestPercent
FROM `Job1111`.SETTINGS WHERE Label = "CompleteForTestPercent";
SELECT Value1 INTO HydroHoursPercent
FROM `Job1111`.SETTINGS WHERE Label = "HydroHoursPercent";
SELECT Value1 INTO ClientTurnoverPercent
FROM `Job1111`.SETTINGS WHERE Label = "ClientTurnoverPercent";

### Build Temp Table Queries ###
#DROP TABLE IF EXISTS `Job1111`.`TempPipeER`, `Job1111`.`TempPipeERT`;

SET @TempQuery1 = CONCAT('
CREATE TEMPORARY TABLE `Job1111`.`TempPipeER`(
INDEX `TempPipeER1` (`ID` ASC)
)
SELECT ID, BidID,
sum(EarnedErectionHours) AS EarnedErectionHours,
sum(EarnedBoltUpHours) AS EarnedBoltUpHours,
sum(EarnedWeldHours) AS EarnedWeldHours,
sum(EarnedCutHours) AS EarnedCutHours,
sum(EarnedTrimHours) AS EarnedTrimHours,
sum(EarnedSupportHours) AS EarnedSupportHours,
sum(EarnedDemoHours) AS EarnedDemoHours,
sum(EarnedMiscHours) AS EarnedMiscHours,
sum(InstalledFootage) AS InstalledFootage,
sum(InstalledBoltUps) AS InstalledBoltUps,
sum(InstalledWelds) AS InstalledWelds,
sum(InstalledCuts) AS InstalledCuts,
sum(InstalledTrim) AS InstalledTrim,
sum(InstalledSupports) AS InstalledSupports,
sum(InstalledMisc) AS InstalledMisc,
sum(InstalledDemo) AS  InstalledDemo

                FROM(SELECT min(td.ID) AS ID,td.BidID,

IF(
COALESCE(se.ErectionLengthSpool1,0)
+ COALESCE(se.ErectionLengthSpool2,0)
+ COALESCE(se.ErectionLengthSpool3,0)
+ COALESCE(se.ErectionLengthSpool4,0)
+ COALESCE(se.ErectionLengthSpool5,0)
+ COALESCE(se.ErectionLengthSpool6,0)
+ COALESCE(sme.MiscQuantity,0)
+ sum(COALESCE(cse.InstalledQuantity,0)) > td.TakeOffQuantity, /*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(cse.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/
COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity)
* (COALESCE(se.ErectionLengthSpool1,0)
+ COALESCE(se.ErectionLengthSpool2,0)
+ COALESCE(se.ErectionLengthSpool3,0)
+ COALESCE(se.ErectionLengthSpool4,0)
+ COALESCE(se.ErectionLengthSpool5,0)
+ COALESCE(se.ErectionLengthSpool6,0)
+ COALESCE(sme.MiscQuantity,0)),0)
) AS EarnedErectionHours,

IF(COALESCE(sb.BoltUpQuantity,0) + COALESCE(smb.MiscQuantity,0) + sum(COALESCE(csb.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csb.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(sb.BoltUpQuantity,0)+COALESCE(smb.MiscQuantity,0)), 0)
    ) AS EarnedBoltUpHours,

IF(COALESCE(sw.WeldPercentCompleted * .01,0) + COALESCE(smw.MiscQuantity,0) + sum(COALESCE(csw.InstalledQuantity * .01,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csw.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(sw.WeldPercentCompleted * .01,0)+COALESCE(smw.MiscQuantity,0)),0)
    ) AS EarnedWeldHours,

IF(COALESCE(scut.WeldPercentCompleted * .01,0) + COALESCE(smc.MiscQuantity,0) + sum(COALESCE(csc.InstalledQuantity * .01,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csc.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(scut.WeldPercentCompleted * .01,0)+COALESCE(smc.MiscQuantity,0)),0)
    ) AS EarnedCutHours,

IF(COALESCE(st.TrimQuantity,0) + COALESCE(smt.MiscQuantity,0) + sum(COALESCE(cst.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(cst.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(st.TrimQuantity,0)+COALESCE(smt.MiscQuantity,0)),0)
    ) AS EarnedTrimHours,

IF(COALESCE(ssu.SupportInstalled,0) + COALESCE(smssu.MiscQuantity,0) + sum(COALESCE(cssu.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(cssu.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity)*(COALESCE(ssu.SupportInstalled,0)+COALESCE(smssu.MiscQuantity,0)),0)
    ) AS EarnedSupportHours,

IF(COALESCE(sde.DemoQuantity,0) + COALESCE(smd.MiscQuantity,0) + sum(COALESCE(csd.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csd.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours/td.TakeOffQuantity) * (COALESCE(sde.DemoQuantity,0)+COALESCE(smd.MiscQuantity,0)),0)
    ) AS EarnedDemoHours,

IF(COALESCE(sm.MiscQuantity,0) + sum(COALESCE(csm.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csm.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * COALESCE(sm.MiscQuantity,0),0)
    ) AS EarnedMiscHours,

sum(COALESCE(se.ErectionLengthSpool1,0) + COALESCE(se.ErectionLengthSpool2,0) + COALESCE(se.ErectionLengthSpool3,0) + COALESCE(se.ErectionLengthSpool4,0) + COALESCE(se.ErectionLengthSpool5,0) + COALESCE(se.ErectionLengthSpool6,0) + COALESCE(sme.MiscQuantity,0)) AS InstalledFootage,
sum(COALESCE(sb.BoltUpQuantity,0) + COALESCE(smb.MiscQuantity,0)) AS InstalledBoltUps,
sum(COALESCE(sw.WeldPercentCompleted * .01,0) + COALESCE(smw.MiscQuantity,0)) AS InstalledWelds,
sum(COALESCE(scut.WeldPercentCompleted * .01,0) + COALESCE(smc.MiscQuantity,0)) AS InstalledCuts,
sum(COALESCE(st.TrimQuantity,0) + COALESCE(smt.MiscQuantity,0)) AS InstalledTrim,
sum(COALESCE(ssu.SupportInstalled,0) + COALESCE(smssu.MiscQuantity,0)) AS InstalledSupports,
sum(COALESCE(sm.MiscQuantity,0)) AS InstalledMisc,
sum(COALESCE(sde.DemoQuantity,0) + COALESCE(smd.MiscQuantity,0)) AS InstalledDemo

FROM `Job1111`.SubmissionStatus ss
    JOIN PSA.REF_ActivityTypes at
        ON at.ID = ss.ActivityType

    LEFT JOIN `Job1111`.SubsErection se
        ON se.ID = ss.ActivityRowId AND ss.ActivityType = 11 AND se.ErectionRework = 0

    LEFT JOIN `Job1111`.SubsBoltUp sb
        ON sb.ID = ss.ActivityRowId AND ss.ActivityType = 21 AND sb.BoltUpRework = 0

    LEFT JOIN `Job1111`.SubsWeld sw
        ON sw.ID = ss.ActivityRowId AND ss.ActivityType = 22 AND  sw.WeldRework = 0

    LEFT JOIN `Job1111`.SubsWeld scut
        ON scut.ID = ss.ActivityRowId AND ss.ActivityType = 23 AND scut.WeldRework = 0

    LEFT JOIN `Job1111`.SubsTrim st
        ON st.ID = ss.ActivityRowId AND ss.ActivityType IN (30,31,33,34,35,39) AND st.TrimRework = 0

    LEFT JOIN (SELECT ssu.ID, COALESCE((ssu.SupportPercentCompleted * .01 * ssu.SupportQuantity),0) AS SupportInstalled
                FROM `Job1111`.SubsSupports ssu
                WHERE ssu.SupportRework = 0) ssu
        ON ssu.ID = ss.ActivityRowId AND ss.ActivityType = 32

    LEFT JOIN `Job1111`.SubsDemo sde
        ON sde.ID = ss.ActivityRowId AND ss.ActivityType IN (50,51,52,53,59)

    LEFT JOIN `Job1111`.`TempTrackerDetailsPipe`td
        ON td.BidID = ss.fk_BidIDAssigned

/* #### Begining of Misc activities assigned joins #### */
    LEFT JOIN `Job1111`.SubsMisc sm
        ON sm.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT != "D" AND td.PipingActivityType = "MISC"  AND sm.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc sme
        ON sme.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "PE" AND sme.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smb
        ON smb.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "BU" AND smb.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smw
        ON smw.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "W" AND smw.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smc
        ON smc.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT != "D" AND td.PipingActivityType = "CUT" AND smc.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smt
        ON smt.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "T" AND td.PipingActivityType IN ("V","Plug","HpLp","Other","Blind") AND smt.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smssu
        ON smssu.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "S" AND smssu.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smd
        ON smd.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "D" AND smd.MiscRework = 0

/* #### Begining of Cumulative Past sums Calculation #### */
    LEFT JOIN `Job1111`.`TempPipeCSE` cse
        ON ss.ActivityType IN (11,91) AND ss.fk_BidIDAssigned = cse.fk_BidIDAssigned AND cse.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSB` csb
        ON ss.ActivityType IN (21,91) AND ss.fk_BidIDAssigned = csb.fk_BidIDAssigned AND csb.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSW` csw
        ON ss.ActivityType IN (22,91) AND ss.fk_BidIDAssigned = csw.fk_BidIDAssigned AND csw.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSC` csc
        ON ss.ActivityType IN (23,91) AND ss.fk_BidIDAssigned = csc.fk_BidIDAssigned AND csc.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCST` cst
        ON ss.ActivityType IN (30,31,33,34,35,39,91) AND ss.fk_BidIDAssigned = cst.fk_BidIDAssigned AND cst.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSSU` cssu
        ON ss.ActivityType IN (32,91) AND ss.fk_BidIDAssigned = cssu.fk_BidIDAssigned AND cssu.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSD` csd
        ON ss.ActivityType IN (50,51,52,53,59,91) AND ss.fk_BidIDAssigned = csd.fk_BidIDAssigned AND csd.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSM` csm
        ON ss.ActivityType = 91 AND ss.fk_BidIDAssigned = csm.fk_BidIDAssigned AND csm.PSAActivityID < ss.PSAActivityID

WHERE fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (10,11,20,21,22,23,30,31,32,33,34,35,39,50,51,52,53,59,90,91,93)
GROUP BY ss.PSAActivityID)er0
GROUP BY BidID
');

SET @TempQuery2 = CONCAT('
CREATE TEMPORARY TABLE `Job1111`.`TempPipeERT`(
INDEX `TempPipeERT1` (`ID` ASC)
)
SELECT min(td.ID) AS ID, td.BidID,

IF(
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ' * sum(COALESCE(steR.InstalledReadyForTesting, 0)))
    > COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ',
    /*TRUE*/
    COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ',
    /*FALSE*/
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ' * sum(COALESCE(steR.InstalledReadyForTesting, 0)))
) AS EarnedReadyForTestingHours,

IF(
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ' * sum(COALESCE(steH.InstalledHydro, 0)))
    > COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ',
    /*TRUE*/
    COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ',
    /*FALSE*/
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ' * sum(COALESCE(steH.InstalledHydro, 0)))
) AS EarnedHydroHours,

IF(
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ' * sum(COALESCE(steC.InstalledClientTurnover, 0)))
    > COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ',
    /*TRUE*/
    COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ',
    /*FALSE*/
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ' * sum(COALESCE(steC.InstalledClientTurnover, 0)))
) AS EarnedClientTurnoverHours

FROM `Job1111`.trackerDetails td

LEFT JOIN (SELECT ste.ActivityType, ste.TestingTestPackageNumber, 1 AS InstalledReadyForTesting
            FROM `Job1111`.SubsTesting ste
            JOIN `Job1111`.SubmissionStatus ss
                ON ss.ActivityRowId = ste.ID AND ss.ActivityType = 41 AND ss.fk_ActivityStatus IN (5,12) AND ste.TestingRework = 0) steR
    ON steR.TestingTestPackageNumber = td.TestPackageNumber AND steR.ActivityType = 41 AND td.ECT != "D"

LEFT JOIN (SELECT ste.ActivityType, ste.TestingTestPackageNumber, 1 AS InstalledHydro
            FROM `Job1111`.SubsTesting ste
            JOIN `Job1111`.SubmissionStatus ss
                ON ss.ActivityRowId = ste.ID AND ss.ActivityType = 42 AND ss.fk_ActivityStatus IN (5,12) AND ste.TestingRework = 0) steH
    ON steH.TestingTestPackageNumber = td.TestPackageNumber AND steH.ActivityType = 42 AND td.ECT != "D"

LEFT JOIN (SELECT ste.ActivityType, ste.TestingTestPackageNumber, 1 AS InstalledClientTurnover
            FROM `Job1111`.SubsTesting ste
            JOIN `Job1111`.SubmissionStatus ss
                ON ss.ActivityRowId = ste.ID AND ss.ActivityType = 43 AND ss.fk_ActivityStatus IN (5,12) AND ste.TestingRework = 0) steC
    ON steC.TestingTestPackageNumber = td.TestPackageNumber AND steC.ActivityType = 43 AND td.ECT != "D"

GROUP BY td.BidID
');

### Build Main Query ###
SET @GetQuery = CONCAT('
QUERY REMOVED DUE TO BODY SIZE LIMIT
');

### Execute NON Prep Queries ###
DROP TABLE IF EXISTS `Job1111`.`TempTrackerDetailsPipe`;
CREATE TEMPORARY TABLE `Job1111`.`TempTrackerDetailsPipe`(
INDEX `TempPipeTD1` (`BidID` ASC),
INDEX `TempPipeTD2` (`PipingActivityType` ASC, `ECT` ASC),
INDEX `TempPipeTD3` (`ECT` ASC)
)
SELECT min(ID) AS ID, BidID, sum(TakeOffQuantity) AS TakeOffQuantity, sum(EstimatedHours) AS EstimatedHours, ECT, PipingActivityType
FROM`Job1111`.`trackerDetails`
GROUP BY BidID;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSE`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSE`(
INDEX `TempPipeCSE1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(ErectionLengthSpool1,0) + COALESCE(ErectionLengthSpool2,0) + COALESCE(ErectionLengthSpool3,0) + COALESCE(ErectionLengthSpool4,0) + COALESCE(ErectionLengthSpool5,0) + COALESCE(ErectionLengthSpool6,0) + COALESCE(sme.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsErection
    ON ss.ActivityType = 11 AND ss.ActivityRowId = ID AND ErectionRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc sme
    ON sme.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "PE" AND sme.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (11,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSB`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSB`(
INDEX `TempPipeCSB1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(BoltUpQuantity,0) + COALESCE(smb.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsBoltUp
    ON ss.ActivityType = 21 AND ss.ActivityRowId = ID AND BoltUpRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smb
    ON smb.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "BU" AND smb.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (21,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSW`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSW`(
INDEX `TempPipeCSW1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(WeldPercentCompleted,0) + COALESCE(smw.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsWeld
    ON ss.ActivityType = 22 AND ss.ActivityRowId = ID AND WeldRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe`td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smw
    ON smw.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "W"AND smw.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (22,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSC`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSC`(
INDEX `TempPipeCSC1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(WeldPercentCompleted,0) + COALESCE(smc.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsWeld
    ON ss.ActivityType = 23 AND ss.ActivityRowId = ID AND WeldRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe`td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smc
    ON smc.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT != "D" AND td.PipingActivityType = "CUT" AND smc.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (23,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCST`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCST`(
INDEX `TempPipeCST1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(TrimQuantity,0) + COALESCE(smt.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsTrim
    ON ss.ActivityType IN (30,31,33,34,35,39) AND ss.ActivityRowId = ID AND TrimRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smt
    ON smt.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "T" AND td.PipingActivityType IN ("V","Plug","HpLp","Other","Blind")AND smt.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (30,31,33,34,35,39,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSSU`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSSU`(
INDEX `TempPipeCSSU1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(SupportQuantity,0) + COALESCE(smssu.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsSupports
    ON ss.ActivityType = 32 AND ss.ActivityRowId = ID AND SupportRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smssu
    ON smssu.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "S" AND smssu.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSD`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSD`(
INDEX `TempPipeCSD` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(DemoQuantity,0) + COALESCE(smd.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsDemo
    ON ss.ActivityType IN (50,51,52,53,59) AND ss.ActivityRowId = ID
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smd
    ON smd.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "D" AND smd.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (50,51,52,53,59,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSM`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSM`(
INDEX `TempPipeCSM` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, MiscQuantity AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
JOIN `Job1111`.SubsMisc sm
    ON ss.ActivityType = 91 AND ss.ActivityRowId = sm.ID AND td.PipingActivityType = "MISC"  AND td.ECT != "D" AND sm.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12);

### Prepare Queries to execute ###
PREPARE BuildTempQuery1 FROM @TempQuery1;
PREPARE BuildTempQuery2 FROM @TempQuery2;
PREPARE BuildGetQuery FROM @GetQuery;

### Execute Prep Queries ###
EXECUTE BuildTempQuery1;
EXECUTE BuildTempQuery2;
EXECUTE BuildGetQuery;

### Deallocate Execute Querys and Tables###
DEALLOCATE PREPARE BuildTempQuery1;
DEALLOCATE PREPARE BuildTempQuery2;
DEALLOCATE PREPARE BuildGetQuery;

END$$
DELIMITER ;
```

答:
The way I see your code is that you want a query like this:

```<code>CREATE TEMPORARY TABLE `database`.`temp_table` AS
SELECT user_id, name, address
FROM user
WHERE user_id = 3829;
ALTER TABLE `database`.`temp_table`
ADD INDEX `index_name` (`user_id` ASC);
</code>```

To be stored in a session variable using a style like this:

```<code>SET @session_variable = CONCAT('
    CREATE TEMPORARY TABLE `database`.`temp_table` AS
    SELECT user_id, name, address
    FROM user
    WHERE user_id = 3829;
    ALTER TABLE `database`.`temp_table`
    ADD INDEX `index_name` (`user_id` ASC);
');
</code>```

But the proper way to do the style you want is this:

```<code>SET @session_variable = CONCAT('CREATE TEMPORARY TABLE `database`.`temp_table` AS ');
SET @session_variable = CONCAT(@session_variable, 'SELECT user_id, name, address ');
SET @session_variable = CONCAT(@session_variable, 'FROM user ');
SET @session_variable = CONCAT(@session_variable, 'WHERE user_id = 3829; ');
SET @session_variable = CONCAT(@session_variable, 'ALTER TABLE `database`.`temp_table` ');
SET @session_variable = CONCAT(@session_variable, 'ADD INDEX `index_name` (`user_id` ASC);');
</code>```

or like this:

```<code>SET @session_variable = CONCAT(
    'CREATE TEMPORARY TABLE `database`.`temp_table` AS ',
    'SELECT user_id, name, address ',
    'FROM user ',
    'WHERE user_id = 3829; ',
    'ALTER TABLE `database`.`temp_table` ',
    'ADD INDEX `index_name` (`user_id` ASC); '
);
</code>```

I would like to suggest that you read about Prepared Statement for MySQL in here: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

Hope I can help, cheers.

Body:
Setup

MySQL 5.7 or 5.6

In the Below Stored procedure what I'm trying to do is create a few Temp Tables.    Some of which are simple and static, and 2 of which are dynamic (TempQuery1, TempQuery2).

All temp tables are Built -> Prepared -> executed first.  Then the main query that utilizes the temp tables (GetQuery) is executed.

Problem

When executed exactly like it is on a fresh connection, I get

 Error Code: 1146. Table 'Job1111.TempPipeER' doesn't exist

Which lead me to think I had a typeo or something.. In testing I found that if I follow the next couple steps t works fine.

Comment out Prepare and Execute for both Query2 and GetQuery

Save the stored procedure and run it 2 times. (getting expected error from Deallocate)

Uncomment out Query 2.

Save the stored procedure and run it 2 more times. (getting expected error from Deallocate)

Finally uncomment all Prepares and Executes

Save and run stored procedure... NOW it works.

Question

Why am I having to go through these step to let it build the temp tables one at a time.

Does a following execute wait for previous to finish before starting?

Is there a way to force the procedure to wait for an execute/ table build to finish?

Is there a way to signal/echo/console.log() type of method that could be used to accurately show the completion of a temp table build and the start of an execute to prove that is even my real problem?

Observation/Guess

The way it is acting is like the Execute BuildGetQuery is executing before TempTable Job1111.TempPipeER is finished building.  And sine GetQuery utilizes the TempPipeER able in a Join.  The GetQuery fails.

But if I slowly build each table one at a time, then it works fine for the duration of that MySQL work bench session.

Stored Procedure Create Statement

DROP PROCEDURE IF EXISTS `Job1111`.`GetPipeStats`;
DELIMITER $$
CREATE DEFINER=`PSAAdmin`@`%` PROCEDURE `Job1111`.`GetPipeStats`( IN GroupedBy varchar(24))
BEGIN

### Declare Variables ###
DECLARE MechHoursPercent DECIMAL(3,2);
DECLARE TestingHoursPercent DECIMAL(3,2);
DECLARE CompleteForTestPercent DECIMAL(3,2);
DECLARE HydroHoursPercent DECIMAL(3,2);
DECLARE ClientTurnoverPercent DECIMAL(3,2);

### Set Variables ###
SELECT Value1 INTO MechHoursPercent
FROM `Job1111`.SETTINGS WHERE Label = "MechHoursPercent";
SELECT Value1 INTO TestingHoursPercent
FROM `Job1111`.SETTINGS WHERE Label = "TestingHoursPercent";
SELECT Value1 INTO CompleteForTestPercent
FROM `Job1111`.SETTINGS WHERE Label = "CompleteForTestPercent";
SELECT Value1 INTO HydroHoursPercent
FROM `Job1111`.SETTINGS WHERE Label = "HydroHoursPercent";
SELECT Value1 INTO ClientTurnoverPercent
FROM `Job1111`.SETTINGS WHERE Label = "ClientTurnoverPercent";

### Build Temp Table Queries ###
#DROP TABLE IF EXISTS `Job1111`.`TempPipeER`, `Job1111`.`TempPipeERT`;

SET @TempQuery1 = CONCAT('
CREATE TEMPORARY TABLE `Job1111`.`TempPipeER`(
INDEX `TempPipeER1` (`ID` ASC)
)
SELECT ID, BidID,
sum(EarnedErectionHours) AS EarnedErectionHours,
sum(EarnedBoltUpHours) AS EarnedBoltUpHours,
sum(EarnedWeldHours) AS EarnedWeldHours,
sum(EarnedCutHours) AS EarnedCutHours,
sum(EarnedTrimHours) AS EarnedTrimHours,
sum(EarnedSupportHours) AS EarnedSupportHours,
sum(EarnedDemoHours) AS EarnedDemoHours,
sum(EarnedMiscHours) AS EarnedMiscHours,
sum(InstalledFootage) AS InstalledFootage,
sum(InstalledBoltUps) AS InstalledBoltUps,
sum(InstalledWelds) AS InstalledWelds,
sum(InstalledCuts) AS InstalledCuts,
sum(InstalledTrim) AS InstalledTrim,
sum(InstalledSupports) AS InstalledSupports,
sum(InstalledMisc) AS InstalledMisc,
sum(InstalledDemo) AS  InstalledDemo

                FROM(SELECT min(td.ID) AS ID,td.BidID,

IF(
COALESCE(se.ErectionLengthSpool1,0)
+ COALESCE(se.ErectionLengthSpool2,0)
+ COALESCE(se.ErectionLengthSpool3,0)
+ COALESCE(se.ErectionLengthSpool4,0)
+ COALESCE(se.ErectionLengthSpool5,0)
+ COALESCE(se.ErectionLengthSpool6,0)
+ COALESCE(sme.MiscQuantity,0)
+ sum(COALESCE(cse.InstalledQuantity,0)) > td.TakeOffQuantity, /*Is Installed Greater then TakeOff*/
/*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(cse.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
/*FALSE*/
COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity)
* (COALESCE(se.ErectionLengthSpool1,0)
+ COALESCE(se.ErectionLengthSpool2,0)
+ COALESCE(se.ErectionLengthSpool3,0)
+ COALESCE(se.ErectionLengthSpool4,0)
+ COALESCE(se.ErectionLengthSpool5,0)
+ COALESCE(se.ErectionLengthSpool6,0)
+ COALESCE(sme.MiscQuantity,0)),0)
) AS EarnedErectionHours,

IF(COALESCE(sb.BoltUpQuantity,0) + COALESCE(smb.MiscQuantity,0) + sum(COALESCE(csb.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csb.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(sb.BoltUpQuantity,0)+COALESCE(smb.MiscQuantity,0)), 0)
    ) AS EarnedBoltUpHours,

IF(COALESCE(sw.WeldPercentCompleted * .01,0) + COALESCE(smw.MiscQuantity,0) + sum(COALESCE(csw.InstalledQuantity * .01,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csw.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(sw.WeldPercentCompleted * .01,0)+COALESCE(smw.MiscQuantity,0)),0)
    ) AS EarnedWeldHours,

IF(COALESCE(scut.WeldPercentCompleted * .01,0) + COALESCE(smc.MiscQuantity,0) + sum(COALESCE(csc.InstalledQuantity * .01,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csc.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(scut.WeldPercentCompleted * .01,0)+COALESCE(smc.MiscQuantity,0)),0)
    ) AS EarnedCutHours,

IF(COALESCE(st.TrimQuantity,0) + COALESCE(smt.MiscQuantity,0) + sum(COALESCE(cst.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(cst.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * (COALESCE(st.TrimQuantity,0)+COALESCE(smt.MiscQuantity,0)),0)
    ) AS EarnedTrimHours,

IF(COALESCE(ssu.SupportInstalled,0) + COALESCE(smssu.MiscQuantity,0) + sum(COALESCE(cssu.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(cssu.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity)*(COALESCE(ssu.SupportInstalled,0)+COALESCE(smssu.MiscQuantity,0)),0)
    ) AS EarnedSupportHours,

IF(COALESCE(sde.DemoQuantity,0) + COALESCE(smd.MiscQuantity,0) + sum(COALESCE(csd.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csd.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours/td.TakeOffQuantity) * (COALESCE(sde.DemoQuantity,0)+COALESCE(smd.MiscQuantity,0)),0)
    ) AS EarnedDemoHours,

IF(COALESCE(sm.MiscQuantity,0) + sum(COALESCE(csm.InstalledQuantity,0))
    > td.TakeOffQuantity,/*Is Installed Greater then TakeOff*/
    /*TRUE*/ GREATEST(td.TakeOffQuantity - sum(COALESCE(csm.InstalledQuantity,0)),0) * (td.EstimatedHours * ' ,MechHoursPercent, ' / td.TakeOffQuantity),
    /*FALSE*/ COALESCE((td.EstimatedHours*' ,MechHoursPercent, '/td.TakeOffQuantity) * COALESCE(sm.MiscQuantity,0),0)
    ) AS EarnedMiscHours,

sum(COALESCE(se.ErectionLengthSpool1,0) + COALESCE(se.ErectionLengthSpool2,0) + COALESCE(se.ErectionLengthSpool3,0) + COALESCE(se.ErectionLengthSpool4,0) + COALESCE(se.ErectionLengthSpool5,0) + COALESCE(se.ErectionLengthSpool6,0) + COALESCE(sme.MiscQuantity,0)) AS InstalledFootage,
sum(COALESCE(sb.BoltUpQuantity,0) + COALESCE(smb.MiscQuantity,0)) AS InstalledBoltUps,
sum(COALESCE(sw.WeldPercentCompleted * .01,0) + COALESCE(smw.MiscQuantity,0)) AS InstalledWelds,
sum(COALESCE(scut.WeldPercentCompleted * .01,0) + COALESCE(smc.MiscQuantity,0)) AS InstalledCuts,
sum(COALESCE(st.TrimQuantity,0) + COALESCE(smt.MiscQuantity,0)) AS InstalledTrim,
sum(COALESCE(ssu.SupportInstalled,0) + COALESCE(smssu.MiscQuantity,0)) AS InstalledSupports,
sum(COALESCE(sm.MiscQuantity,0)) AS InstalledMisc,
sum(COALESCE(sde.DemoQuantity,0) + COALESCE(smd.MiscQuantity,0)) AS InstalledDemo

FROM `Job1111`.SubmissionStatus ss
    JOIN PSA.REF_ActivityTypes at
        ON at.ID = ss.ActivityType

    LEFT JOIN `Job1111`.SubsErection se
        ON se.ID = ss.ActivityRowId AND ss.ActivityType = 11 AND se.ErectionRework = 0

    LEFT JOIN `Job1111`.SubsBoltUp sb
        ON sb.ID = ss.ActivityRowId AND ss.ActivityType = 21 AND sb.BoltUpRework = 0

    LEFT JOIN `Job1111`.SubsWeld sw
        ON sw.ID = ss.ActivityRowId AND ss.ActivityType = 22 AND  sw.WeldRework = 0

    LEFT JOIN `Job1111`.SubsWeld scut
        ON scut.ID = ss.ActivityRowId AND ss.ActivityType = 23 AND scut.WeldRework = 0

    LEFT JOIN `Job1111`.SubsTrim st
        ON st.ID = ss.ActivityRowId AND ss.ActivityType IN (30,31,33,34,35,39) AND st.TrimRework = 0

    LEFT JOIN (SELECT ssu.ID, COALESCE((ssu.SupportPercentCompleted * .01 * ssu.SupportQuantity),0) AS SupportInstalled
                FROM `Job1111`.SubsSupports ssu
                WHERE ssu.SupportRework = 0) ssu
        ON ssu.ID = ss.ActivityRowId AND ss.ActivityType = 32

    LEFT JOIN `Job1111`.SubsDemo sde
        ON sde.ID = ss.ActivityRowId AND ss.ActivityType IN (50,51,52,53,59)

    LEFT JOIN `Job1111`.`TempTrackerDetailsPipe`td
        ON td.BidID = ss.fk_BidIDAssigned

/* #### Begining of Misc activities assigned joins #### */
    LEFT JOIN `Job1111`.SubsMisc sm
        ON sm.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT != "D" AND td.PipingActivityType = "MISC"  AND sm.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc sme
        ON sme.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "PE" AND sme.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smb
        ON smb.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "BU" AND smb.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smw
        ON smw.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "W" AND smw.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smc
        ON smc.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT != "D" AND td.PipingActivityType = "CUT" AND smc.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smt
        ON smt.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "T" AND td.PipingActivityType IN ("V","Plug","HpLp","Other","Blind") AND smt.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smssu
        ON smssu.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "S" AND smssu.MiscRework = 0

    LEFT JOIN `Job1111`.SubsMisc smd
        ON smd.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "D" AND smd.MiscRework = 0

/* #### Begining of Cumulative Past sums Calculation #### */
    LEFT JOIN `Job1111`.`TempPipeCSE` cse
        ON ss.ActivityType IN (11,91) AND ss.fk_BidIDAssigned = cse.fk_BidIDAssigned AND cse.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSB` csb
        ON ss.ActivityType IN (21,91) AND ss.fk_BidIDAssigned = csb.fk_BidIDAssigned AND csb.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSW` csw
        ON ss.ActivityType IN (22,91) AND ss.fk_BidIDAssigned = csw.fk_BidIDAssigned AND csw.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSC` csc
        ON ss.ActivityType IN (23,91) AND ss.fk_BidIDAssigned = csc.fk_BidIDAssigned AND csc.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCST` cst
        ON ss.ActivityType IN (30,31,33,34,35,39,91) AND ss.fk_BidIDAssigned = cst.fk_BidIDAssigned AND cst.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSSU` cssu
        ON ss.ActivityType IN (32,91) AND ss.fk_BidIDAssigned = cssu.fk_BidIDAssigned AND cssu.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSD` csd
        ON ss.ActivityType IN (50,51,52,53,59,91) AND ss.fk_BidIDAssigned = csd.fk_BidIDAssigned AND csd.PSAActivityID < ss.PSAActivityID

    LEFT JOIN `Job1111`.`TempPipeCSM` csm
        ON ss.ActivityType = 91 AND ss.fk_BidIDAssigned = csm.fk_BidIDAssigned AND csm.PSAActivityID < ss.PSAActivityID

WHERE fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (10,11,20,21,22,23,30,31,32,33,34,35,39,50,51,52,53,59,90,91,93)
GROUP BY ss.PSAActivityID)er0
GROUP BY BidID
');

SET @TempQuery2 = CONCAT('
CREATE TEMPORARY TABLE `Job1111`.`TempPipeERT`(
INDEX `TempPipeERT1` (`ID` ASC)
)
SELECT min(td.ID) AS ID, td.BidID,

IF(
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ' * sum(COALESCE(steR.InstalledReadyForTesting, 0)))
    > COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ',
    /*TRUE*/
    COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ',
    /*FALSE*/
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,CompleteForTestPercent, ' * sum(COALESCE(steR.InstalledReadyForTesting, 0)))
) AS EarnedReadyForTestingHours,

IF(
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ' * sum(COALESCE(steH.InstalledHydro, 0)))
    > COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ',
    /*TRUE*/
    COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ',
    /*FALSE*/
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,HydroHoursPercent, ' * sum(COALESCE(steH.InstalledHydro, 0)))
) AS EarnedHydroHours,

IF(
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ' * sum(COALESCE(steC.InstalledClientTurnover, 0)))
    > COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ',
    /*TRUE*/
    COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ',
    /*FALSE*/
    (COALESCE(td.EstimatedHours,0)*' ,TestingHoursPercent, '*' ,ClientTurnoverPercent, ' * sum(COALESCE(steC.InstalledClientTurnover, 0)))
) AS EarnedClientTurnoverHours

FROM `Job1111`.trackerDetails td

LEFT JOIN (SELECT ste.ActivityType, ste.TestingTestPackageNumber, 1 AS InstalledReadyForTesting
            FROM `Job1111`.SubsTesting ste
            JOIN `Job1111`.SubmissionStatus ss
                ON ss.ActivityRowId = ste.ID AND ss.ActivityType = 41 AND ss.fk_ActivityStatus IN (5,12) AND ste.TestingRework = 0) steR
    ON steR.TestingTestPackageNumber = td.TestPackageNumber AND steR.ActivityType = 41 AND td.ECT != "D"

LEFT JOIN (SELECT ste.ActivityType, ste.TestingTestPackageNumber, 1 AS InstalledHydro
            FROM `Job1111`.SubsTesting ste
            JOIN `Job1111`.SubmissionStatus ss
                ON ss.ActivityRowId = ste.ID AND ss.ActivityType = 42 AND ss.fk_ActivityStatus IN (5,12) AND ste.TestingRework = 0) steH
    ON steH.TestingTestPackageNumber = td.TestPackageNumber AND steH.ActivityType = 42 AND td.ECT != "D"

LEFT JOIN (SELECT ste.ActivityType, ste.TestingTestPackageNumber, 1 AS InstalledClientTurnover
            FROM `Job1111`.SubsTesting ste
            JOIN `Job1111`.SubmissionStatus ss
                ON ss.ActivityRowId = ste.ID AND ss.ActivityType = 43 AND ss.fk_ActivityStatus IN (5,12) AND ste.TestingRework = 0) steC
    ON steC.TestingTestPackageNumber = td.TestPackageNumber AND steC.ActivityType = 43 AND td.ECT != "D"

GROUP BY td.BidID
');

### Build Main Query ###
SET @GetQuery = CONCAT('
QUERY REMOVED DUE TO BODY SIZE LIMIT
');

### Execute NON Prep Queries ###
DROP TABLE IF EXISTS `Job1111`.`TempTrackerDetailsPipe`;
CREATE TEMPORARY TABLE `Job1111`.`TempTrackerDetailsPipe`(
INDEX `TempPipeTD1` (`BidID` ASC),
INDEX `TempPipeTD2` (`PipingActivityType` ASC, `ECT` ASC),
INDEX `TempPipeTD3` (`ECT` ASC)
)
SELECT min(ID) AS ID, BidID, sum(TakeOffQuantity) AS TakeOffQuantity, sum(EstimatedHours) AS EstimatedHours, ECT, PipingActivityType
FROM`Job1111`.`trackerDetails`
GROUP BY BidID;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSE`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSE`(
INDEX `TempPipeCSE1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(ErectionLengthSpool1,0) + COALESCE(ErectionLengthSpool2,0) + COALESCE(ErectionLengthSpool3,0) + COALESCE(ErectionLengthSpool4,0) + COALESCE(ErectionLengthSpool5,0) + COALESCE(ErectionLengthSpool6,0) + COALESCE(sme.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsErection
    ON ss.ActivityType = 11 AND ss.ActivityRowId = ID AND ErectionRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc sme
    ON sme.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "PE" AND sme.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (11,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSB`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSB`(
INDEX `TempPipeCSB1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(BoltUpQuantity,0) + COALESCE(smb.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsBoltUp
    ON ss.ActivityType = 21 AND ss.ActivityRowId = ID AND BoltUpRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smb
    ON smb.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "BU" AND smb.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (21,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSW`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSW`(
INDEX `TempPipeCSW1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(WeldPercentCompleted,0) + COALESCE(smw.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsWeld
    ON ss.ActivityType = 22 AND ss.ActivityRowId = ID AND WeldRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe`td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smw
    ON smw.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "W"AND smw.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (22,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSC`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSC`(
INDEX `TempPipeCSC1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(WeldPercentCompleted,0) + COALESCE(smc.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsWeld
    ON ss.ActivityType = 23 AND ss.ActivityRowId = ID AND WeldRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe`td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smc
    ON smc.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT != "D" AND td.PipingActivityType = "CUT" AND smc.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (23,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCST`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCST`(
INDEX `TempPipeCST1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(TrimQuantity,0) + COALESCE(smt.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsTrim
    ON ss.ActivityType IN (30,31,33,34,35,39) AND ss.ActivityRowId = ID AND TrimRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smt
    ON smt.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "T" AND td.PipingActivityType IN ("V","Plug","HpLp","Other","Blind")AND smt.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (30,31,33,34,35,39,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSSU`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSSU`(
INDEX `TempPipeCSSU1` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(SupportQuantity,0) + COALESCE(smssu.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsSupports
    ON ss.ActivityType = 32 AND ss.ActivityRowId = ID AND SupportRework = 0
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smssu
    ON smssu.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.PipingActivityType = "S" AND smssu.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSD`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSD`(
INDEX `TempPipeCSD` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, (COALESCE(DemoQuantity,0) + COALESCE(smd.MiscQuantity,0)) AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.SubsDemo
    ON ss.ActivityType IN (50,51,52,53,59) AND ss.ActivityRowId = ID
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
LEFT JOIN `Job1111`.SubsMisc smd
    ON smd.ID = ss.ActivityRowId AND ss.ActivityType = 91 AND td.ECT = "D" AND smd.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12)
AND ss.ActivityType IN (50,51,52,53,59,91)
HAVING InstalledQuantity > 0;

DROP TABLE IF EXISTS `Job1111`.`TempPipeCSM`;
CREATE TEMPORARY TABLE `Job1111`.`TempPipeCSM`(
INDEX `TempPipeCSM` (`fk_BidIDAssigned` ASC, `PSAActivityID` ASC)
)
SELECT ss.PSAActivityID, ss.fk_BidIDAssigned, MiscQuantity AS InstalledQuantity
FROM `Job1111`.SubmissionStatus ss
LEFT JOIN `Job1111`.`TempTrackerDetailsPipe` td
    ON td.BidID = ss.fk_BidIDAssigned
JOIN `Job1111`.SubsMisc sm
    ON ss.ActivityType = 91 AND ss.ActivityRowId = sm.ID AND td.PipingActivityType = "MISC"  AND td.ECT != "D" AND sm.MiscRework = 0
WHERE ss.fk_ActivityStatus IN (5,12);

### Prepare Queries to execute ###
PREPARE BuildTempQuery1 FROM @TempQuery1;
PREPARE BuildTempQuery2 FROM @TempQuery2;
PREPARE BuildGetQuery FROM @GetQuery;

### Execute Prep Queries ###
EXECUTE BuildTempQuery1;
EXECUTE BuildTempQuery2;
EXECUTE BuildGetQuery;

### Deallocate Execute Querys and Tables###
DEALLOCATE PREPARE BuildTempQuery1;
DEALLOCATE PREPARE BuildTempQuery2;
DEALLOCATE PREPARE BuildGetQuery;

END$$
DELIMITER ;

Body:
The way I see your code is that you want a query like this:

CREATE TEMPORARY TABLE `database`.`temp_table` AS
SELECT user_id, name, address
FROM user
WHERE user_id = 3829;
ALTER TABLE `database`.`temp_table`
ADD INDEX `index_name` (`user_id` ASC);

To be stored in a session variable using a style like this:

SET @session_variable = CONCAT('
    CREATE TEMPORARY TABLE `database`.`temp_table` AS
    SELECT user_id, name, address
    FROM user
    WHERE user_id = 3829;
    ALTER TABLE `database`.`temp_table`
    ADD INDEX `index_name` (`user_id` ASC);
');

But the proper way to do the style you want is this:

SET @session_variable = CONCAT('CREATE TEMPORARY TABLE `database`.`temp_table` AS ');
SET @session_variable = CONCAT(@session_variable, 'SELECT user_id, name, address ');
SET @session_variable = CONCAT(@session_variable, 'FROM user ');
SET @session_variable = CONCAT(@session_variable, 'WHERE user_id = 3829; ');
SET @session_variable = CONCAT(@session_variable, 'ALTER TABLE `database`.`temp_table` ');
SET @session_variable = CONCAT(@session_variable, 'ADD INDEX `index_name` (`user_id` ASC);');

or like this:

SET @session_variable = CONCAT(
    'CREATE TEMPORARY TABLE `database`.`temp_table` AS ',
    'SELECT user_id, name, address ',
    'FROM user ',
    'WHERE user_id = 3829; ',
    'ALTER TABLE `database`.`temp_table` ',
    'ADD INDEX `index_name` (`user_id` ASC); '
);

I would like to suggest that you read about Prepared Statement for MySQL in here: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

Hope I can help, cheers.


问:
Flask SQLAlchemy Data Mapper vs Active Record Pattern
I have recently started working on Flask and Flask-SQLAlchemy. Coming from Django background I found Flask-SQLAlchmey to be quite complex. I have read that SQLAlchemy implements Data Mapper pattern while Django ORM is based on Active Record Pattern.

Here is a sample code written that implements repository pattern to access the database.

Here is another link of a comment by S.Lott (271k reputation) who says that ORM is the data access layer and it is separate from model.

My questions are these:

Can you provide a practical use case in the above example or an example of your own where Data mapper pattern is useful? Everywhere I have read is that data mapper pattern is useful in complex situation, but not seen examples.

Is using repositories pattern as in above case same as using a data mapper pattern?

Does data mapper advocates write select queries in a different class than the model as done in the example?

Why is Question.query.filter_by(text = text).all() not better to use than
db.session.query(Question).filter(Question.text == text).all()?

This is not a duplicate of DataMapper vs ActiveRecord pattern because this just tells the definition, I am more interested in the practical examples.

答:
Completely agree with above answer: yes, SQLAlchemy's Data Mapper pattern is really more flexible and for complex queries it's really more powerful, less magical and more controlled.

But, in simple tasks such as CRUD SQLAlchemy's code becomes too overweight/excessive/redundant.

For example, to just create some object in simplest "create" controller, you need something like this:

```<code>user = User(name='Nick', surname='Nickson')
session.add(user)
session.flush()
</code>```

While in Active Record ORM you will only need single string.

Well, for simple tasks, some of us may want something simpler.
I mean it will be cool to have Active Record for SQLAlchemy.

Good news: I recently created package for this (it also contains other useful stuff).

Check it out: https://github.com/absent1706/sqlalchemy-mixins

Body:
Completely agree with above answer: yes, SQLAlchemy's Data Mapper pattern is really more flexible and for complex queries it's really more powerful, less magical and more controlled.

But, in simple tasks such as CRUD SQLAlchemy's code becomes too overweight/excessive/redundant.

For example, to just create some object in simplest "create" controller, you need something like this:

user = User(name='Nick', surname='Nickson')
session.add(user)
session.flush()

While in Active Record ORM you will only need single string.

Well, for simple tasks, some of us may want something simpler.
I mean it will be cool to have Active Record for SQLAlchemy.

Good news: I recently created package for this (it also contains other useful stuff).

Check it out: https://github.com/absent1706/sqlalchemy-mixins


问:
Anybody interested in possible operation/use of quantum computers?
I went through a period of being interested in how quantum computers work and what they might be good for if they ever become practical. I know they are talked about for code-breaking. I was interested is using them for validating software by essentially trying all possible inputs (in parallel) and seeing if any error states are reached.

I know it's a bit of a blue-sky question, but I wonder if others are interested in quantum computers, how they might work, and what they would be useful for.

Added: Just for fun, let me throw out a mini-tutorial:

Suppose you've got N bits of memory to play with. Suppose you can load those bits (or some of them) with your input data. Then suppose there is a finite sequence of operations you can do on them (without using any additional memory) leaving the answer in them.

To do this with a quantum computer, it is only necessary that you make sure that the entire computation is reversible, by reserving some of the bits to record branches you took, so you can undo them. If you do that, then all the operations can be written as simple unitary matrix transformations on the N bits. (A unitary transform is a pure rotation in the N-dimensional coordinate system.) So performing the computation consists of applying a succession of pure rotations on the bit-vector.

If you do this, then if the N-bit vector is in a quantum computer, it can be initialized into a state where all 2^N (or fewer) possible inputs are superimposed at the same time in "parallel universes". Then if you do the computation, it is doing them all at the same time.

Now all you have to do to see if one of the inputs gives you a particular answer is to let it run to a particular state. If you halt it and examine the state, what it does is choose one of the universes at random and throw away all the rest. So what the Grover algorithm lets you do is, without halting it, accentuate the probability of the universes with the answer state. Then you run it forward, then backward, then forward, and so on for a number of iterations until the answer universe has very high probability. Then if you examine it, you have a high probability of seeing the answer you want.

Whew...

OwnerDisplayName: Mike Dunlavey

LastEditorDisplayName: Mike Dunlavey

Body: A link: http://www.dwavesys.com/index.php?page=applications

OwnerDisplayName: ChrisW


问:
Java detect connection errors
I have some Java code which reads a gzip file from an URL and decompress it. How do I make sure that the whole file is read from the connection or how do I make sure that there are no problem with it?

```<code>try {
    URL url = new URL("http://example.com/de?Debug=1");
    URLConnection myUrlConnection = url.openConnection();
    GZIPInputStream gZIPInputStream = new GZIPInputStream(myUrlConnection.getInputStream());
    StringBuffer decompressedStringBuffer = new StringBuffer();
    int bytes_read;
    while ((bytes_read = gZIPInputStream.read(buffer)) > 0) {
        String part = new String(buffer, 0 ,bytes_read, "UTF-8");
            decompressedStringBuffer.append(part);
        }
        gZIPInputStream.close();
        String decompressedString = decompressedStringBuffer.toString();

        ...

} catch (MalformedURLException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}
</code>```

The try-catch block are auto-generated by IntelliJ. What other types of exceptions should I catch to cover : connection break, partial data received, connection timeout. These are all the problems I could think of. Can you think of others?

答:
If you get to the point where read() returns -1 you have the entire file that was sent.

But don't collect it in memory: do something useful with it each time you get a piece: i.e. send it on, or write it to a file, or whatever.

You don't need to catch any more exceptions, and any IOExceptions indicate trouble of one kind or another.

Title: Java detect connection errors

Body:
I have some Java code which reads a gzip file from an URL and decompress it. How do I make sure that the whole file is read from the connection or how do I make sure that there are no problem with it?

try {
    URL url = new URL("http://example.com/de?Debug=1");
    URLConnection myUrlConnection = url.openConnection();
    GZIPInputStream gZIPInputStream = new GZIPInputStream(myUrlConnection.getInputStream());
    StringBuffer decompressedStringBuffer = new StringBuffer();
    int bytes_read;
    while ((bytes_read = gZIPInputStream.read(buffer)) > 0) {
        String part = new String(buffer, 0 ,bytes_read, "UTF-8");
            decompressedStringBuffer.append(part);
        }
        gZIPInputStream.close();
        String decompressedString = decompressedStringBuffer.toString();

        ...

} catch (MalformedURLException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

The try-catch block are auto-generated by IntelliJ. What other types of exceptions should I catch to cover : connection break, partial data received, connection timeout. These are all the problems I could think of. Can you think of others?


问:
Exporting datasets into multiple excel sheets of one excel file
I need to export two dataset's values in two excel sheets of the same workbook.
My Query is like:

//Dataset One:

```<code>        DataSet ds1 = new DataSet();
        SqlCommand commandOpen = new SqlCommand("storedproc1", conSql);
        commandOpen.CommandType = CommandType.StoredProcedure;
        var adaptOpen = new SqlDataAdapter();
        adaptOpen.SelectCommand = commandOpen;
        adaptOpen.Fill(ds1, "table");
</code>```

//Dataset Two:

```<code>        DataSet ds2 = new DataSet();
        SqlCommand commandOpen = new SqlCommand("storedproc2", conSql);
        commandOpen.CommandType = CommandType.StoredProcedure;
        var adaptOpen = new SqlDataAdapter();
        adaptOpen.SelectCommand = commandOpen;
        adaptOpen.Fill(ds2, "table");
</code>```

Now to create one Excel workbook I have used:

```<code>        ExcelLibrary.DataSetHelper.CreateWorkbook("C:/New Folder/file1.xls", ds1);
</code>```

But instead of this, in the same workbook I want to add two sheets; one for ds1 and another for ds2. How to do that?
Thanks.

答:
Here we can export several tables into single excel sheet. </p>

```<code>using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace multiplesheets_export
{
    class Program
    {
    public static void Main(string[] args)
                {
                    object missing = Type.Missing;
                    SqlConnection con = new SqlConnection("Data Source=WINCTRL-KJ8RKFO;Initial Catalog=excel;Integrated Security=True");

                    SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);
                    SqlDataAdapter da1 = new SqlDataAdapter("select * from Department", con);

                    DataTable dt = new DataTable();
                    DataTable dt1 = new DataTable();

                    da.Fill(dt);
                    da1.Fill(dt1);

                    if (dt == null || dt.Columns.Count == 0)
                                                        throw new Exception("ExportToExcel: Null or empty input table!\n");
                    Excel.Application oXL = new Excel.Application();
                    Excel.Workbook oWB = oXL.Workbooks.Add(missing);
                    Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
                    oSheet.Name = "Employee Details";

                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        oSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    }
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        for (var j = 0; j < dt.Columns.Count; j++)
                        {
                            oSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
                        }
                    }

                    // From Here am taking EXCEL SHEET -2

                    Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)as Excel.Worksheet;

                    if (dt1 == null || dt1.Columns.Count == 0)
                        throw new Exception("ExportToExcel: Null or empty input table!\n");
                    oSheet2.Name = "Depatment Details";

                    for (var i = 0; i < dt1.Columns.Count; i++)
                    {
                        oSheet2.Cells[1, i + 1] = dt1.Columns[i].ColumnName;
                    }
                    for (var i = 0; i < dt1.Rows.Count; i++)
                    {
                        for (var j = 0; j < dt1.Columns.Count; j++)
                        {
                            oSheet2.Cells[i + 2, j + 1] = dt1.Rows[i][j];
                        }
                    }
                    oXL.Visible = true;
                }
            }
        }
</code>```

Body:
I need to export two dataset's values in two excel sheets of the same workbook.
My Query is like:

//Dataset One:

        DataSet ds1 = new DataSet();
        SqlCommand commandOpen = new SqlCommand("storedproc1", conSql);
        commandOpen.CommandType = CommandType.StoredProcedure;
        var adaptOpen = new SqlDataAdapter();
        adaptOpen.SelectCommand = commandOpen;
        adaptOpen.Fill(ds1, "table");

//Dataset Two:

        DataSet ds2 = new DataSet();
        SqlCommand commandOpen = new SqlCommand("storedproc2", conSql);
        commandOpen.CommandType = CommandType.StoredProcedure;
        var adaptOpen = new SqlDataAdapter();
        adaptOpen.SelectCommand = commandOpen;
        adaptOpen.Fill(ds2, "table");

Now to create one Excel workbook I have used:

        ExcelLibrary.DataSetHelper.CreateWorkbook("C:/New Folder/file1.xls", ds1);

But instead of this, in the same workbook I want to add two sheets; one for ds1 and another for ds2. How to do that?
Thanks.

Body:
Here we can export several tables into single excel sheet.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace multiplesheets_export
{
    class Program
    {
    public static void Main(string[] args)
                {
                    object missing = Type.Missing;
                    SqlConnection con = new SqlConnection("Data Source=WINCTRL-KJ8RKFO;Initial Catalog=excel;Integrated Security=True");

                    SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);
                    SqlDataAdapter da1 = new SqlDataAdapter("select * from Department", con);

                    DataTable dt = new DataTable();
                    DataTable dt1 = new DataTable();

                    da.Fill(dt);
                    da1.Fill(dt1);

                    if (dt == null || dt.Columns.Count == 0)
                                                        throw new Exception("ExportToExcel: Null or empty input table!\n");
                    Excel.Application oXL = new Excel.Application();
                    Excel.Workbook oWB = oXL.Workbooks.Add(missing);
                    Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;
                    oSheet.Name = "Employee Details";

                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        oSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    }
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        for (var j = 0; j < dt.Columns.Count; j++)
                        {
                            oSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
                        }
                    }

                    // From Here am taking EXCEL SHEET -2

                    Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)as Excel.Worksheet;

                    if (dt1 == null || dt1.Columns.Count == 0)
                        throw new Exception("ExportToExcel: Null or empty input table!\n");
                    oSheet2.Name = "Depatment Details";

                    for (var i = 0; i < dt1.Columns.Count; i++)
                    {
                        oSheet2.Cells[1, i + 1] = dt1.Columns[i].ColumnName;
                    }
                    for (var i = 0; i < dt1.Rows.Count; i++)
                    {
                        for (var j = 0; j < dt1.Columns.Count; j++)
                        {
                            oSheet2.Cells[i + 2, j + 1] = dt1.Rows[i][j];
                        }
                    }
                    oXL.Visible = true;
                }
            }
        }


问:
Html5 (audio) on Safari & iOS
I am working on a web application and I have one compatibility problem with Apple devices & Safari on PCs.

Html5 audio tag:

```<code><audio controls>
<source src="/audio/en/file.mp3" type="audio/mpeg">
<source src="/audio/en/file.ogg" type="audio/ogg">
Your browser does not support the audio element.
</audio>
</code>```

I just want to play an audio file with basic controls.

The previous code works perfectly on Chrome, Opera, Firefox (Windows & Android devices).

But controlers do no appear with Safari (tested with the latest version on PC, iPad & iPad mini).

Audio player have a grey background with only "play/pause" function.

Here is a screenshot that describes my problem :

Thanks.

答:
I had exactly the same problem.

My solution: I added the full URL for the audiofile source. Don't know why but it makes a difference. Here's my full code. The CSS modifications are only to hide the download button. But when I take it out I don't see the timeline. Very strange but exactly this code works for me.

```<code><!DOCTYPE html>
<html>
<head>
    <title>html5 audio player on iPhone</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
<style>
audio::-internal-media-controls-download-button {
    display:none;
}
audio::-webkit-media-controls-enclosure {
    overflow:hidden;
}
audio::-webkit-media-controls-panel {
    width: calc(100% + 33px);
}
</style>
</head>
<body>
<audio controls preload="auto" style="width:100%;">
    <source src="https://example.com/audio/audiofile.mp3" type="audio/mpeg">
    Your browser does not support the audio element.
</audio><br />
</body>
</html>
</code>```

Title: Html5 (audio) on Safari & iOS

Body:
I am working on a web application and I have one compatibility problem with Apple devices & Safari on PCs.

Html5 audio tag:

<audio controls>
<source src="/audio/en/file.mp3" type="audio/mpeg">
<source src="/audio/en/file.ogg" type="audio/ogg">
Your browser does not support the audio element.
</audio>

I just want to play an audio file with basic controls.

The previous code works perfectly on Chrome, Opera, Firefox (Windows & Android devices).

But controlers do no appear with Safari (tested with the latest version on PC, iPad & iPad mini).

Audio player have a grey background with only "play/pause" function.

Here is a screenshot that describes my problem :

Thanks.

Body:
I had exactly the same problem.

My solution: I added the full URL for the audiofile source. Don't know why but it makes a difference. Here's my full code. The CSS modifications are only to hide the download button. But when I take it out I don't see the timeline. Very strange but exactly this code works for me.

<!DOCTYPE html>
<html>
<head>
    <title>html5 audio player on iPhone</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
<style>
audio::-internal-media-controls-download-button {
    display:none;
}
audio::-webkit-media-controls-enclosure {
    overflow:hidden;
}
audio::-webkit-media-controls-panel {
    width: calc(100% + 33px);
}
</style>
</head>
<body>
<audio controls preload="auto" style="width:100%;">
    <source src="https://example.com/audio/audiofile.mp3" type="audio/mpeg">
    Your browser does not support the audio element.
</audio><br />
</body>
</html>


问:
How to display different windows in different monitors with OpenCV
I have a PC with multiple monitors.

I want to display different windows in different monitor with OpenCV/C++.

How can I show the window in the specific window?

Thanks!

答: You should use MoveWindow with (values ~= your screen resolution). Just find values which would suit you better, you can try using your OS API to get values for screen resolution, or get this data from system variables</p>