Data As A Service, ODATA

ODATA is a data protocol that relies on REST, which relies on HTTP/S. It supports XML or the more compact/popular JSON. It works across proxies/routers/firewalls.  One can use OAUTH2 or other authentication methods. There is a query language built in. The query language is not SQL, but ODATA’s own language.

ODATA supports INSERT, UPDATE, DELETE, SELECT… and many functions, including things like string functions, geo functions, math functions, etc. Note, the functions are not database functions, they are ODATA functions.

If you’re looking to use REST services to access/use your data in a database, ODATA is definitely worth a look!

While considering an API for your data application, you may want to also consider GraphQL (video).

Keywords: rest, data as a service, database as a service, daas, Open Data Access Protocol, rest, restful, http, https, ipas, ipaas

Troy Frericks.
blog 31-Mar-2020
troyf<at>CyberSecurityRecap.com

=

Copyright 2015-2020 by Troy Frericks, https://CyberSecurityRecap.com/, https://DBArecap.com/.

#

SQL Server, generate insert statements

Most developers don’t realize there is an easy way in MS SQL Server to create INSERT statements from a currently populated table. This can be useful for “promoting” data from a development environment to a production development.


This is a quick how-to tip.


To generate a CREATE TABLE statement and INSERT statements for each row in a table, in  SQL Server Management Studio (SSMS)…
  • Right  click on the database (not the table), select Tasks / Generate Scripts… (generate scripts is near the middle of the list). The “Choose Objects” window will appear
  • Check the “Select specific database objects” radio button, expand Tables, and select the specific table, click Next. The “Set Scripting Options” window will appear
  • Use the default selections, but click “Save to new query window” at the bottom. Here’s the tricky part! I’m not sure why Microsoft did this, but click the “ADVANCED” button. At the very bottom, just above the bold “Table/View Options” is the option: “Types of data to script”. Change the corresponding value from “Schema Only” to “Schema and data”. Click OK.
  • Click Next. The “Summary” window will appear.
  • Expand the “Selected Objects” tree value. Review the summary, click Next. The “Save or Publish” window will appear. Review any errors generated. Click “Finish” and the “Generate and Publish Scripts” set of windows will close. You should have a new SSMS tab with the generated script.
Discovering a method to convert from data in a table to a set of INSERT statements can take hours to figure out. It took me hours of digging. Here’s to saving your time!



Keywords: CyberSecurityRecap DBArecap microsoft sql server management studio create insert statements from table data query data creating insert statements table to inserts MS SQL Server management studio

Troy Frericks.
blog 30-Nov-2019
troyf<at>CyberSecurityRecap.com
=
#