Handling SQL Scripts


I have a PB app that was written long ago, and one thing it does is build its (MySQL) database as needed.  To do this, all of the scripts needed to build  the database were added to function objects, one per database object. i.e., there's an "fn_create_mytable" that builds a table, "fn_create_myproc" that creates a procedure, etc.  

Because these are functions, they are really ungainly because the create statement must be pasted into the function, and then every line wrapped like:

ls_sql = "select this,"~r~n" +&
"that~r~n" +&

etc., until the whole script is there.  Then it executes the statement at the end. So when it needs maintenance, you have to copy the script out, strip out the junk, paste it into a SQL editor, and then reverse the process to put it back.

In a C# application you can store text files in a class as embedded resources and they're not visible to the user, but can be referenced by the code.  I would love to be able to do that somehow in that PB application.  Is it possible?

I apologize if this is an obvious question. I can certainly deploy all the scripts as text files, but then it's all editable and visible to the user.  I want it hidden in a class/NVO, or something without all the string manipulation.

Any ideas would be much appreciated.

Question Tags: 


Chris Pollach answered Handling SQL Scripts


Hi Van;

   That is a great question!

Here are some of my thoughts & suggestions ...

  1. Store the SQL in a DB table in a Text Blob column. Then do a SelectBlob () command to get the SQL - which as a Blob, will retain all the CR's, Tab's, NewLine, etc formatting characters you might need as well.
  2. Yes, use a NVUO to host all your SQL and pull it out of the NVUO at run-time.
  3. Create a PB Web Service. Host an NVUO, Text files and/or access the DB table in the Web Service & return the appropriate SQL.
  4. Variation: Store the entire DW Source and It's SQL in a DB Table and never even have to deploy DW object classes again in your entire PB, Web or Mobile Apps!  wink

Food for thought!


Regards ... Chris


vps_24347's picture

I like the idea of storing everything in the database... except... building the database is why I need the scripts.  So to store them in the database I'd have to distribute a database with the installer so I'd have all of that.  Maybe that would work though... I'd just have to make sure my "system" database was there, and have a special transaction object to connect to that?  Interesting idea.

I can't do a web service because some users like the idea of not needing an internet connection to run the software. So they download it from computer A, but install it on non-web-accessible computer B. :-)

Mike S answered Handling SQL Scripts


store it as data that is part of a datawindow object definition.

create external datawindow with string 2K (or whatever is bigger than you need)

make sure you are displaying the data tab, then enter the scripts there.

when you save the datawindow, the data will save.



vps_24347's picture

So you're saying create an external dw with a single large column, correct?  And then create that and grab the contents of row 1, column 1? So one dw per script?

I wonder how safe it would be to have say, 200 scripts in the data of one datawindow, where each row had a ScriptName column, and a ScriptSQL column. i.e., with all the eggs in one dw basket, I wonder if there's a risk of a crash.  It would be thousands of lines of code.


mike@searer.com's picture

Its just text in a datawindow, so no risk of a crash based on that.  You have more risk of accidently deleting the data out of the datawindow in design mode and then saving the datawindow and losing everything.  But that can happen with any code and is one of the reasons to use source code control.    

If you have a LOT of scripts,   I would also break up the scripts into multiple datawindows:  tables in one, procedures in another - indexes in another. etc.  or some other method.  

for what its worth, i just have everything in text files and have a separate application run the scripts.  It handles new installs as well as upgrades using alter scripts.  If you went that route, you could always encrypt the text files.  

Van Sederburg's picture

A separate application is a good idea. Less to load with the main application since it's rarely used.  Do you have any recommendation on the easiest method of encryption/decryption of files that works with PB?