Announcement

Collapse
No announcement yet.

SQL with Miva?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    SQL with Miva?

    I've decided to finally look into using SQL files with Miva rather than xBase3.

    When I click on the SQL links in the database section of Mivascript 5.20 online manual, there is no documentation.

    Where can I find some documentation or guidence for using SQL with Miva?


    thanks,
    Tim

    #2
    Re: SQL with Miva?

    https://github.com/ghassani/miva-sql-component

    Comment


      #3
      Re: SQL with Miva?

      MivaScript.com will give you the basics of MvOPENVIEW and MvQUERY; that's the core of it.

      A look at the LSK will give you lots of examples, including many of the pre-written functions in db.mv.
      Last edited by Kent Multer; 12-22-14, 02:04 AM.
      Kent Multer
      Magic Metal Productions
      http://TheMagicM.com
      * Web developer/designer
      * E-commerce and Miva
      * Author, The Official Miva Web Scripting Book -- available on-line:
      http://www.amazon.com/exec/obidos/IS...icmetalproducA

      Comment


        #4
        Re: SQL with Miva?

        Originally posted by tmallardi View Post
        I've decided to finally look into using SQL files with Miva rather than xBase3.
        When I click on the SQL links in the database section of Mivascript 5.20 online manual, there is no documentation.
        Where can I find some documentation or guidence for using SQL with Miva?
        thanks,
        Tim
        The best learning for me has been phpmyadmin.
        For example - use the search tab to find a record - then use the generated mysql / php code to create your mivascript code.
        When you know something works in phpmyadmin, you can nearly always get it to work in mivascript. I have recently created a low end, but heavily used and growing "cloud" based order manager that integrates with all our miva stores (5) and our warehouse Shipworks and our 2 fulfillment centers and amazon USA and Canada. Heavy mysql, and not a hiccup (well not many)... and none that I couldn't figure out.

        Here is one example that might help:
        Code:
        <MvOpenView name="refund" view="refunds" QUERY="{ 'SELECT amount, COUNT(amount) AS theCount FROM `refunds` WHERE orderNum=?;' }" FIELDS="orderNum">
        William Gilligan - Orange Marmalade, Inc.
        www.OrangeMarmaladeinc.com

        Comment


          #5
          Re: SQL with Miva?

          Two things which may help:

          1. We have a free tool to convert your MivaSQL database over to MySQL.

          https://support.miva.com/supportsuit...ysql-converter

          2. We have detailed documentation on the Miva database here:

          http://www.miva.com/pdf/MM_Database_...Manual_v20.pdf
          Brennan Heyde
          VP Product
          Miva, Inc.
          [email protected]
          https://www.miva.com

          Comment


            #6
            Re: SQL with Miva?

            Originally posted by Brennan View Post
            Two things which may help:
            2. We have detailed documentation on the Miva database here:
            http://www.miva.com/pdf/MM_Database_...Manual_v20.pdf
            Hi Brennan and all,
            Since I am a strong advocate for Miva Script, and seriously hoping to see future growth and marketing around it (I have had discussions with others on this), I'd like to suggest that going forward we could all make distinctions between Miva Script and Miva Merchant.

            The reality is - the pdf you reference is actually a Miva Merchant database reference, and not a Miva database.
            I know we use these things intermingled, but it would be great if we could start differentiating.

            I suppose the thread title is also not good. What is meant by Miva? Miva Script, Miva Merchant. neither? both?
            That should probably be the first question asked. So I am guilty as well.

            Bill
            William Gilligan - Orange Marmalade, Inc.
            www.OrangeMarmaladeinc.com

            Comment


              #7
              Re: SQL with Miva?

              Thanks for the clarification Bill.

              I am a Mivascript programmer only. I have never seen or touched anything related to Merchant.
              I just love this programming environment.
              I'm surprised there aren't more programmers using it outside of Merchant.

              I have a Linux server with MySQL installed. I'm trying to figure out how to connect to the SQL server using Mivascript.


              Kent,

              What is LSK and db.mv?

              Thanks to all,
              Tim

              Comment


                #8
                Re: SQL with Miva?

                Sorry, that's all Merchant-related stuff. "LSK" is the Limited Source Kit, a collection of Merchant source code that's available free. db.mv is a file that contains lots of functions for reading and writing Merchant's DB tables. In the LSK, it's been split up into many small files. Even if you're not writing modules, you'll probably find all that helpful as examples of how to write various kinds of functions.

                And I forgot to mention that, before you can use MvOPENVIEW or MvQUERY, you need to MvOPEN the database; that's where you enter the username & other connection parameters. (Merchant does that automatically, so we module authors don't often have to write one of those.)
                Last edited by Kent Multer; 12-23-14, 01:53 PM.
                Kent Multer
                Magic Metal Productions
                http://TheMagicM.com
                * Web developer/designer
                * E-commerce and Miva
                * Author, The Official Miva Web Scripting Book -- available on-line:
                http://www.amazon.com/exec/obidos/IS...icmetalproducA

                Comment


                  #9
                  Re: SQL with Miva?

                  Here are a couple examples to connect:

                  I use functions for alot of stuff. To access a function use this format
                  Code:
                  <MvAssign name="l.ok" value="{ [ 'functions/functions.mvc' ].dbOpen('sworders') }">
                  Which means... inside functions.mvc in the functions directory run the function dbOpen with the name (parameter) 'sworders'

                  Which would do this (connect to the mysql database)
                  Code:
                  <MvFUNCTION NAME="dbOpen" PARAMETERS="name" STANDARDOUTPUTLEVEL="html,text" >
                     <MvOPEN NAME = "{ name }" DATABASE = "myDataBaseName_@localhost" TYPE = "mysql" USERNAME = "myUserName" PASSWORD = "myPassWord">
                  <MvFunctionreturn value="0">
                  </MvFunction>
                  Then once open, I can do this:

                  Code:
                     <MvAssign name="l.myNumber" value="12345">
                     <MvOPenView name="sworders" view="notes" QUERY="{ 'SELECT * FROM `notes` where orderNum = ?;' }" FIELDS="myNumber">
                  <MvWhile expr="{ NOT notes.d.EOF }">
                     <MvEval expr="{ notes.d.name }">
                     <MvSkip name="sworders" view="notes">
                  </MvWhile>
                  <MvCloseview view="notes">
                  Which means:
                  1. Get everything from the notes table where the orderNum = 12345
                  2. Display the contents of the name column for a record
                  3. Skip to the next record
                  4. repeat 3
                  5. until the end of the collection is reached.
                  6. Close everything up.

                  Here is how you might insert a record (as compared to simply reading records):
                  Code:
                  <MvAssign name="orderNum" value="111">
                  <MvAssign name="trackingNum" value="1Z33445566">
                  <MvAssign name="l.ok" value="{ [ 'functions/functions.mvc' ].dbOpen('myTracking') }">
                  <MvQUERY NAME = "myTracking" QUERY = "INSERT INTO `tracking` VALUES (?,?,NOW())" FIELDS = "orderNum, trackingNum">
                  <MvClose name="myTracking">
                  1- open the database.
                  2 - insert a record containing the orderNumber, trackingNumber and the time NOW() into the tracking table.
                  3 - close the database

                  Thats should give you a start - use Google, these forums and http://www.mivascript.com/ to keep going.
                  Last edited by wmgilligan; 12-23-14, 05:36 PM.
                  William Gilligan - Orange Marmalade, Inc.
                  www.OrangeMarmaladeinc.com

                  Comment


                    #10
                    Re: SQL with Miva?

                    Bill,

                    Thanks for the tutorial.
                    Just what I needed to get started.

                    Gracias,
                    Tim

                    Comment


                      #11
                      Re: SQL with Miva?

                      Originally posted by tmallardi View Post
                      Bill,

                      Thanks for the tutorial.
                      Just what I needed to get started.

                      Gracias,
                      Tim
                      Hi Tim,

                      Like you, I strictly use Miva for non-merchant programming. I've been with this terrific scripting language when it was called HTMLscript (as has Bill and many other posters). I have built my entire company with programs written in Miva. Most of it in the medical industry, including a DBMS that handles 55,000 unique patients per year, applications to parse hospital HL7 data, a web portal (cloud) for medical entities that conduct remote medical testing, etc. etc. etc.

                      Besides heavy use of MySQL, I also use an old ODBC library to communicate with MS access and VFP databases. Personally, I feel there is a huge potential in corp America to use mivascript since so many applications are browser based these days. But I think miva scrpiters are few.

                      Obviously, I'm a huge fan. And miva rocks with MySQL. Like bill, hardly a hiccup with 1,000 records a day. If you need more examples (maybe with some joins), I can provide some.

                      It's a great language and a greater company with the current owners.

                      -mike

                      Comment


                        #12
                        Re: SQL with Miva?

                        Hi Mike,

                        I figured there must be other guys out there like you who have utilized MivaScript outside of the Merchant world.
                        It dawned on me recently though, that Miva probably has no incentive to dedicate any resources to non-merchant programming because their revenue comes from Merchant related business.

                        I ended up deciding to learn PHP to ultimately produce the complex reports that I needed.
                        So now my application has become a hybrid of Mivascript-xbase on the front end, and PHP-MySQL for reporting. I just run a quick PHP script that copies the necessary data from xBase into MySQL.

                        Now that I'm not in a huge rush, I will experiment with the examples that Bill provided earlier. If I have any trouble, I will take you up on your offer to see your MySQL examples.

                        Thanks!
                        Tim

                        Comment


                          #13
                          Re: SQL with Miva?

                          Originally posted by MLT View Post
                          Hi Tim,

                          Like you, I strictly use Miva for non-merchant programming. I've been with this terrific scripting language when it was called HTMLscript (as has Bill and many other posters). I have built my entire company with programs written in Miva. Most of it in the medical industry, including a DBMS that handles 55,000 unique patients per year, applications to parse hospital HL7 data, a web portal (cloud) for medical entities that conduct remote medical testing, etc. etc. etc.

                          Besides heavy use of MySQL, I also use an old ODBC library to communicate with MS access and VFP databases. Personally, I feel there is a huge potential in corp America to use mivascript since so many applications are browser based these days. But I think miva scrpiters are few.

                          Obviously, I'm a huge fan. And miva rocks with MySQL. Like bill, hardly a hiccup with 1,000 records a day. If you need more examples (maybe with some joins), I can provide some.

                          It's a great language and a greater company with the current owners.

                          -mike
                          Hi Mike, I agree that Mivascript is a great language, and I'm another non-Merchant coder who's been around a while. I looked at converting my CMS over to PHP as it's far more widely used, but there are things about Mivascript that have made me stick with it. PHP is a very flexible language, but Mivascript's sandboxing greatly reduces the risk that a bug could result in someone gaining system access. There's still the risk of SQL injection with badly coded SQL, but MvOpenView and MvQuery both support parameterised queries which can reduce this.
                          Compilation to Empresa VM bytecode catches a lot or errors without running any code.
                          At one stage when OBDC database support was official, I used Mivascript as a web front end to a Microsoft Dynamics NAV ERP system running on SQL Server. It had to be read-only due to NAV's way of managing data, but worked well.
                          Unfortunately lack of database libraries is the main limitation I find with Mivascript. I think even in a Merchant centred world, having the ODBC library back would be very helpful, as it would make interacting with a diverse range of back-end systems from Merchant possible, and of course it would be useful to non-Merchant coders as well.
                          Christopher Cookson
                          Create IT Powered by Webpression CMS

                          Comment

                          Working...
                          X