Which SQL plug-ins are 11-compliant?

[Kevin Frank]

I know of three...

1. MyFMButler (doSQL)

2. CNS (MMQuery)

3. Dracoventions (SQL Runner)

Any others?

[Wim Decorte]

the fmDataGuard and SyncDek plugins

[Micah Woods]

I don't know of any others. Here's the complete list of plug-ins that support SQL (at least that I know of).

Any others??

2empowerFM SQL Runner

24U Toolbox Plug-In

360Works ScriptMaster

CNS MMQuery

Monkeybread MBS

myFMbutler DoSQL

qutic FMSQL

Scodigo SmartPill

fmDataGuard

Youseful ysflSQL

[Colleen]

Can you please clarify that all of these plug-ins both support SQL and are 11 compliant?

Thanks!

  On 2/27/2011 at 2:06 PM, Micah Woods said:

I don't know of any others. Here's the complete list of plug-ins that support SQL (at least that I know of).

Any others??

2empowerFM SQL Runner

24U Toolbox Plug-In

360Works ScriptMaster

CNS MMQuery

Monkeybread MBS

myFMbutler DoSQL

qutic FMSQL

Scodigo SmartPill

fmDataGuard

Youseful ysflSQL

[Colleen]

  On 2/26/2011 at 11:43 PM, Wim Decorte said:

the fmDataGuard and SyncDek plugins

Do you know if these plugins support SELECT INTO ?

I am attempting to use the fmDataGuard plug in for this statement & have not found any luck yet... but that could also be due to my newbie SQL skills ;-)

Thanks!

[Wim Decorte]

  On 2/28/2011 at 4:21 PM, Colleen said:

Can you please clarify that all of these plug-ins both support SQL and are 11 compliant?

fmDG and SyncDek are 11 compliant for sure. It's all we use.

  On 2/28/2011 at 7:20 PM, Colleen said:

Do you know if these plugins support SELECT INTO ?

I am attempting to use the fmDataGuard plug in for this statement & have not found any luck yet... but that could also be due to my newbie SQL skills ;-)

I seriously doubt it. AFAIK, SELECT INTO is not part of the SQL standard and is a feature that exists only in MS SQL Server. It's generally frowned upon and i've seen warnings not to use it in production, only in development.

[Colleen]

  On 3/1/2011 at 2:54 PM, Wim Decorte said:

fmDG and SyncDek are 11 compliant for sure. It's all we use.

I seriously doubt it. AFAIK, SELECT INTO is not part of the SQL standard and is a feature that exists only in MS SQL Server. It's generally frowned upon and i've seen warnings not to use it in production, only in development.

Thanks for that!! SELECT INTO was demoed at Pause & looked 'cool' ... so being aware that it shouldn't be used in production is a rather helpful piece of info! ;-)

It is however mentioned on pg 42 of the FileMaker ODBD & JDBC Guide, and I'm not seeing any kind of 'development only' warning. Out of curiosity, are the warnings you're referring to FileMaker specific? or general SQL best practices?

Thanks!!

[Kevin Frank]

Hi Colleen,

Is it possible you're thinking of "INSERT SELECT"?

Regards,

Kevin

  On 3/1/2011 at 4:01 PM, Colleen said:

Thanks for that!! SELECT INTO was demoed at Pause & looked 'cool' ... so being aware that it shouldn't be used in production is a rather helpful piece of info! ;-)

It is however mentioned on pg 42 of the FileMaker ODBD & JDBC Guide, and I'm not seeing any kind of 'development only' warning. Out of curiosity, are the warnings you're referring to FileMaker specific? or general SQL best practices?

Thanks!!

[Wim Decorte]

  On 3/1/2011 at 4:01 PM, Colleen said:

Thanks for that!! SELECT INTO was demoed at Pause & looked 'cool' ... so being aware that it shouldn't be used in production is a rather helpful piece of info! ;-)

It is however mentioned on pg 42 of the FileMaker ODBD & JDBC Guide, and I'm not seeing any kind of 'development only' warning. Out of curiosity, are the warnings you're referring to FileMaker specific? or general SQL best practices?

Thanks!!

I have to re-read what the FM xDBC guide says. I know I read about it in one of the O'Reilly books that I use (probably "SQL in a nutshell" which is a nice little reference book). Just did a google and found this in another O'Reilly book:

My link

[Colleen]

  On 3/1/2011 at 6:56 PM, Kevin Frank said:

Hi Colleen,

Is it possible you're thinking of "INSERT SELECT"?

Regards,

Kevin

Yep, highly likely... I now feel like that FileMaker user who just entered 100 data entry records into Find mode! ;-)

So, can you explain the difference between this example on pg 43 of the ODBC/JDBC Guide:

INSERT INTO table (fields) SELECT (fields) WHERE field = 'value'

And this example from Micah's POE demo,

SELECT

'INSERT INTO Employee(Department_ID, Employee_ID, First_Name, Last_Name, Salary) VALUES (' +

CAST(Department_ID AS VARCHAR) +

', ' + CAST(Employee_ID AS VARCHAR) +

', ''' + First_Name + '''' +

', ''' + Last_Name + '''' +

', ' + CAST(Salary AS VARCHAR) +

')'

FROM Employee

Thanks so much!!

Colleen

[Kevin Frank]

Uh... Michah?

[Colleen]

  On 3/2/2011 at 9:38 PM, Kevin Frank said:

Uh... Michah?

Micah Woods, Scodigo

The example files are up on the POE site

[Kevin Frank]

My "Uh, Micah..." was not asking "who?" It was asking Micah to respond to your question.

[Micah Woods]

  On 3/2/2011 at 8:54 PM, Colleen said:

Yep, highly likely... I now feel like that FileMaker user who just entered 100 data entry records into Find mode! ;-)

So, can you explain the difference between this example on pg 43 of the ODBC/JDBC Guide:

INSERT INTO table (fields) SELECT (fields) WHERE field = 'value'

And this example from Micah's POE demo,

SELECT

'INSERT INTO Employee(Department_ID, Employee_ID, First_Name, Last_Name, Salary) VALUES (' +

CAST(Department_ID AS VARCHAR) +

', ' + CAST(Employee_ID AS VARCHAR) +

', ''' + First_Name + '''' +

', ''' + Last_Name + '''' +

', ' + CAST(Salary AS VARCHAR) +

')'

FROM Employee

Thanks so much!!

Colleen

Hi Colleen, this is a confusing little query that's actually being used to build a INSERT statements by concatenating together some hard-coded pieces, for example:

'INSERT INTO Employee(Department_ID, Employee_ID, First_Name, Last_Name, Salary) VALUES ('

with some dynamic data from the Employee table. It's a query that builds a query, kind of like you can have a FileMaker calc that builds an expression that can then be evaluated. I created this so I could store all the data in the Employee table, then delete all the records, then run the query to restore everything. I hope that makes some kind of sense? (it really has nothing to do with using SELECT with INSERT)

[Colleen]

  On 3/14/2011 at 4:42 AM, Micah Woods said:

Hi Colleen, this is a confusing little query that's actually being used to build a INSERT statements by concatenating together some hard-coded pieces, for example:

'INSERT INTO Employee(Department_ID, Employee_ID, First_Name, Last_Name, Salary) VALUES ('

with some dynamic data from the Employee table. It's a query that builds a query, kind of like you can have a FileMaker calc that builds an expression that can then be evaluated. I created this so I could store all the data in the Employee table, then delete all the records, then run the query to restore everything. I hope that makes some kind of sense? (it really has nothing to do with using SELECT with INSERT)

That does indeed make sense, thanks for the explanation Micah!