How do you connect to a MySQL server?

[keffa]

This probably sounds like a really silly question but I've either missed it somewhere in the documentation or I'm just blind but how on earth do you get MIVA to work with a MySQL database server? I haven't seen anything that says "You need to do this" or "You need to have this" in the documentation.

If someone could give me a few steps I would be very grateful. Thankyou in advance.

[Vic - WolfPaw Computers]

You only have the choice once. When you install the store initially.

You are prompted for the database type. mivasql or MySQL.

That is the only time when you can define the connection to the database.

Typically the connection string looks like this:

databasename@hostname
mysqlusername
mysqlpasswd

mivasql:
Merchant5/mivamerchant.dbf

Once you install the store, you cannot change the database type. Unless of course you delete the install and start again.

[keffa]

Thankyou for the reply.

I should have been a little bit more specific. I'm not actually using Miva Merchant, I am using Empressa and developing my own scripts. I'm having trouble locating anything in the documentation that explains how you make Empressa work with MySQL. If you can give me a few pointers for this particular circumstance I would be grateful.

I've read through every bit of documentation I can find for Empressa but it doesn't really say much about MySQL other than you can use it if you prefer.

[Vic - WolfPaw Computers]

Perhaps if we knew a little more about what you are trying to accomplish, one could provide a little more guidance.

[keffa]

At the moment nothing in particular, I'm just trying to get it to work with MySQL. For example a simple MIVA script to connect to the MySQL server and return all the fields in a table. I know for the required SQL script and I think I know the relevent MIVA script to use but I'm unsure how exactly to tell the Empressa engine to use MySQL as opposed to its own internal DB system.

[mvmarkus]

At the moment nothing in particular, I'm just trying to get it to work with MySQL. For example a simple MIVA script to connect to the MySQL server and return all the fields in a table. I know for the required SQL script and I think I know the relevent MIVA script to use but I'm unsure how exactly to tell the Empressa engine to use MySQL as opposed to its own internal DB system.

Hi Keffa,

Unless you use Merchant, Mivascript doesn't care which database backend you use; they can be freely mixed (of course provided that the libraries are properly installed in the Apache/IE configuration ).

(the following example uses default-settings that are stored in a structure called g.system_variables; for example:


<MvASSIGN NAME = "g.system_variables:default_schema" VALUE = "MyWEBSITE" >
<MvASSIGN NAME = "g.system_variables:mysql_default_db" VALUE = "MyWEBSITE@localhost:3306" > // assuming you use that port; the port number may or may not be necessary
<MvASSIGN NAME = "g.system_variables:mysql_user" VALUE = "USERNAME" >
<MvASSIGN NAME = "g.system_variables:mysql_pw" VALUE = "MyPASSWORD" >
)


You first have to establish the connection (only once until you close it):


<Miva MvOPEN_ERROR ="nonfatal,nodisplay">
<MvOPEN NAME="{ g.system_variables:default_schema }"
DATABASE="{ g.system_variables:mysql_default_db }"
TYPE="MYSQL"
user="{ g.system_variables:mysql_user }"
password="{ g.system_variables:mysql_pw }">

<MvIF EXPR = "{ MvOPEN_ERROR }">
... error handling here.....
<MvFUNCRETURN VALUE="{ MvOPEN_ERROR }">
</MvIF>


If you have no MvOPEN_ERROR, you can then start to query your tables.

<Miva MvOPENVIEW_ERROR="nonfatal,nodisplay">
<Miva MvCLOSEVIEW_ERROR="nonfatal,nodisplay">
<Miva MvPRIMARY_ERROR="nonfatal,nodisplay">
<Miva MvREVEALSTRUCTURE_ERROR="nonfatal,nodisplay">

<MvASSIGN NAME = "l.record_id" VALUE = "123456" >
<MvASSIGN NAME = "l.query" VALUE = "SELECT * FROM table WHERE id=?" >

<MvOPENVIEW NAME = "{g.system_variables:default_schema }" VIEW = "v" QUERY = "{l.query}" Fields="l.record_id">

<MvIF EXPR = "{ MvOPENVIEW_ERROR }" >
<MvFUNCRETURN VALUE="{ MvOPENVIEW_ERROR }">
<MvELSE>

<MvIF EXPR = "{ NOT v.d.eof }" >

// The query has results, but since we don't know the structure of the results, we get it with MvREVEALSTRUCTURE

<MvREVEALSTRUCTURE NAME = "{ g.system_variables:default_schema}" VIEW="v" VARIABLE="l.struc">



<TABLE>
<MvPRIMARY NAME = "{ g.system_variables:default_schema }" VIEW="v">
<MvWHILE EXPR = "{ NOT v.d.eof }">
<TR>
<MvASSIGN NAME = "l.c" VALUE = "{ l.c+1 }" >
<MvASSIGN NAME = "l.a" VALUE = "1" >
<MvWHILE EXPR = "{ l.struc[l.a]:FIELD_NAME }">
<TD> <MvEVAL EXPR = "{ miva_variable_value('v.d.'$ l.struc[l.a]:FIELD_NAME) }" > &nbsp;</TD>
<MvASSIGN NAME = "l.a" VALUE = "{ l.a+1 }" >
</MvWHILE>
</TR>
<MvSKIP>
</MvWHILE>
</TABLE>

<MvEVAL EXPR = "{ l.c }" > results

<MvELSE>
No results...
</MvIF>

<MvCLOSEVIEW NAME="{ g.system_variables:default_schema }" VIEW="v">

</MvIF>


It is important to remember to use the SCHEMA and not a table alias in the NAME-attributes! I don't know how many times I used the alias instead - of course this never worked...


HTH

Markus

[keffa]

Thanyou very much for this Markus. The documentation is very obscure when it comes to the database functions and it wasn't clear just how you told MIVA it was a MySQL database. This has given me something to chew on finally and its quite comprehensive. :)

[wetlander]

I try this code and all i get are:

results No results...

i'm positive i set this up as mysql.

is there no way to connect to the miva version of mysql using an external tool such as phpMyAdmin?

[Vic - WolfPaw Computers]

There is no MIVA version of MySQL.

Merchant runs on existing MySQL database software...assuming your host has support for this installation method.

You can read a MySQL database with phpMyAdmin. As long as you know the database name, username and password with correct permissions.

Heck, you can read MySQL tables with PHP scripting.

If you're not sure what installation you used, look in your admin, click on Main. It will tell you the MIVA Merchant version, MIVA Engine Version, and Database API.

[wetlander]

hmmm, wonder exactly what i'm doing wrong then. Not a whole lot of documentation out on how to do this. I can understand why, but it makes it difficult for new developers to come in.

I'm sure i'm just doing the miva script portion wrong some how. I installed the mia thing last nite, now if i can ever get time I can actually play with it.

[wetlander]

Ok, i've found out what I've done wrong. I'm on the wrong sql version and it's set to mivasql *slaps forehead*. Been a few weeks now since i initially installed it. I wanted to make sure of the version before i installed.

Is MySQL 4.1.2 acceptable in order to get miva to work on mysql instead of mivasql?

[lockep]

How to Update or add SQL records

I have a client that does not want to use dBASEx because it is old and is insisting on using MySQL for a intranet project.
I am just starting to learn how to use MySQL and MIVA and there is NO info out there. I used MvMarkus example scriptto view records and it work fine. I tried to cahnge the query to UPDATE tablename SET field='value' WHERE field1='value2'; and I got the following error - Input parameter count mismatch: Found 1, expected 0

Then I tried the origanial select query and then
<MvASSIGN NAME="d.address" VALUE="{street}">
<MvUPDATE VIEW="v">
that gave me this error -MvUPDATE is an Unsupported Operation

HELP I have no clue.

Patrick

[lockep]

OK now I am really lost. The code I am using is below. This code does update the database but everytime I open it in a browser Mia crashes.

<MvASSIGN NAME = "g.system_variables:default_schema" VALUE = "test" >
<MvASSIGN NAME = "g.system_variables:mysql_default_db" VALUE = "test@localhost:3306" > // assuming you use that port; the port number may or may not be necessary
<MvASSIGN NAME = "g.system_variables:mysql_user" VALUE = "root" >
<MvASSIGN NAME = "g.system_variables:mysql_pw" VALUE = "patnjen" >

<Miva MvOPEN_ERROR ="nonfatal,nodisplay">
<MvOPEN NAME="{ g.system_variables:default_schema }"
DATABASE="{ g.system_variables:mysql_default_db }"
TYPE="MYSQL"
user="{ g.system_variables:mysql_user }"
password="{ g.system_variables:mysql_pw }">

<MvIF EXPR = "{ MvOPEN_ERROR }">
... error handling here.....
we had an error <br>
<MvEVAL EXPR="{MvOPEN_ERROR}">
<MvEXIT>

</MvIF>



<Miva MvOPENVIEW_ERROR="nonfatal,nodisplay">
<Miva MvCLOSEVIEW_ERROR="nonfatal,nodisplay">
<Miva MvPRIMARY_ERROR="nonfatal,nodisplay">
<Miva MvREVEALSTRUCTURE_ERROR="nonfatal,nodisplay">

<MvASSIGN NAME="record_id" VALUE="3">

<MvASSIGN NAME = "l.query" VALUE = "UPDATE testtable SET address='testaddr2' WHERE id=? ;" >
<MvEVAL EXPR="{l.query}">
<MvOPENVIEW NAME = "{g.system_variables:default_schema }" VIEW = "v" QUERY = "{l.query}" Fields="record_id">
<MvIF EXPR = "{ MvOPENVIEW_ERROR }" >
open error<br>
<MvEVAL EXPR="{MvOPENVIEW_ERROR}">
<MvEXIT>

<MvELSE>


<MvCLOSEVIEW NAME="{ g.system_variables:default_schema }" VIEW="v">

</MvIF>

[Emma]

Hi,

Small question... What Windows do you have? If you really need to develop with mivascript you should look for WinXP Pro + IIS + Empresa. That way you will have a real developement server and no crash.

PS: Hi everyone :) Long time no see... What's up MvMarkus , Bill :o
PS2: MvMarkus... lol ... still 100% structures fan as I see :D

Best regards,
Claudiu Bischoff

[Southlander]

Not that this is the cause of any of your issues but you should scope your variables.

You have:

<MvASSIGN NAME="record_id" VALUE="3">

.. and

<MvOPENVIEW NAME = "{g.system_variables:default_schema }" VIEW = "v" QUERY = "{l.query}" Fields="record_id">

Make it:

<MvASSIGN NAME="l.record_id" VALUE="3">

.. and

<MvOPENVIEW NAME = "{g.system_variables:default_schema }" VIEW = "v" QUERY = "{l.query}" Fields="l.record_id">

Give that a try.