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!

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]

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]

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.

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]

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

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]

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]

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]

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]

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]

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!