CODEWITHSUNDEEP
mysqldatabasef#
Bella
Bella

Reputation: 414

Insert data in MySQL db in all tables

here I'm again for some help. :) I'm trying to insert data into my MySQL database using F#. My schema includes 3 tables (Experiments, Gene and Results):

Experiments         |       Results           |        Gene 
Experiment id       |       Experiment id     |        Gene id 
Experiment name     |       Gene id           |        Gene Name                         
                    |       Value             |

Now, I'm trying to add data into the db using SQLProvider.

let db = sql.GetDataContext()

let gene_tb = db.Vp32.Genes
let exp_tb = db.Vp32.Experiments
let results_tb = db.Vp32.Results

let createNewInput (genename: string) (expname: string) res =
    let newRes = results_tb.Create()
    let newGene = gene_tb.Create()
    let newExp = exp_tb.Create()

    newGene.GeneName <- genename
    newExp.ExpName <- expname
    newRes.ResultVal <- Some res

createNewInput "test1"  "experiment_test1" (float32 999.5)

db.SubmitUpdates()

I'm having problems to make it understand that the result value is linked to the experiment and gene ids. It gives an error when I try to submit it: "Field 'Exp_id' doesn't have a default value". Well, there isn't a default value because it should be the ids of the other tables. Oh, and.. these IDs are set to auto increase. Moreover, everytime I add new data, if the experiment name exists already in the table, the id shouldn't increase. Do I have to do something like a 'trigger' for this or some specification code using 'if...then'?

Thank you very much in advance! :D Thanks to you I've already learned sooo much alredy, I can't believe! ^-^

SOLUTION: So, I found out that the problem was that I had to query the IDs of Experiments and Genes.. So I changed stuff a bit. For now I have 3 functions (one for each table) and the one for results do a small query before adding the result value:

let addNewExp (expname: string)=
    let newExp = exp_tb.Create()
    newExp.ExpName <- expname
    db.SubmitUpdates()

let addNewGene (genename: string)=
    let newGene = gene_tb.Create()
    newGene.GeneName <- genename
    db.SubmitUpdates()

let addNewRes (expName: string) (geneName: string) res =
    let tempExpId = 
        query {
            for experiment in exp_tb do
                where (experiment.ExpName = expName)
                select experiment.ExpId
            }
        |> Seq.head

    let tempGeneId = 
        query {
            for gene in gene_tb do
                where (gene.GeneName = geneName)
                select gene.GeneName
            }
        |> Seq.toArray |> String.concat ""

    let newRes = results_tb.Create()
    newRes.ExpId <- tempExpId
    newRes.GeneId <- tempGeneId
    newRes.ResultVal <- Some res
    db.SubmitUpdates()

Indeed, it's necessary to do the query to get the id values before adding something in the associative table (results). Afterwards I just converted to the correct types (that are described in the database) since the query returns as sequence (Seq). I hope this can also help someone in the future.

Upvotes: 4

Views: 177

Answers (1)

Tomas Petricek
Tomas Petricek

Reputation: 243061

I have not actually tried this, so it might not be right - but I think one strategy that should work would be to first insert the Experiment record (with ExpName) together with Gene record with GeneName.

When you then call SubmitUpdates, the provider should insert the two records (which should work, because they have all the fields they need) and fill the Experiment id and Gene id properties of the inserted records.

You then have those IDs, so you should be abe to create a new Result record with all the correct IDs, insert it and call SubmitUpdates again.

(There might be a better way, but this could do the trick...)

Upvotes: 2

Related Questions