Start a new topic

Read SQL From DB and Format Tag

Has anyone done a read from the database with scripting and write the results to a tag? For that matter, has anyone done a read from the database with a script?


Anyone?


Is there anyone at all who knows how to do a simple lookup in a script for a field? 

I have read what I could find on the Database calls but the structure isn't clear to me.


I just want to read the field for example: Artist Born Name and write it to a Custom Tag. Someone, please? :)

Hi!


I'm trying to figure out what it is you want to do and how to do it.


Do you want to go through all artists in the database, get the "Artist Born Name" and then write it to all tracks from that artist?

Hi, thanks for writing back. To clarify the need: I have gone through most of the hundreds of the artists and entered their biography into the Artist Info screen. I came to find out that when doing so that it doesn't write that bio to the actual track information. If I need to move the data to a different database then the biography will not move because it's only stored in the database. 


So... I want to read the bio from the database and write it to the Track tag information.


I have done other scripts but not reading from the db :(  need help on this area. Thanks.


Anyone at all know how to do this? Please assist if you can. Thanks!

Well the database reading goes like this:

// set your Artist_ID
int artistid = YOUR_ARTIST_ID;
// create the SQL-Query: Get biography from one specific artist and only if it's not empty
sql = string.Format("SELECT Biography FROM `tblartists` WHERE Artist_ID = '{0}' AND Biography IS NOT NULL", artistid);
// Call the database and get the results
reader = host.RemoteCalls.GetDataReader(sql);
// check if any result has been found
if (reader.NumberOfRows > 0)
{
// since we looked for a specific Artist_Id we have only one result, so we can simply use the first row
	row = reader.Rows[0];
// We only wanted to get the biography field, so we can use the first field
	string biography = (string)row.Fields[0];
}

BUT you shouldn't need to do this.


Simply get the related artist using host.RemoteCalls.ArtistByName(string name) or host.RemoteCalls.ArtistById(int id). Both calls returns NeonScriptArtist, which includes the biography you've added to the artist.

Afterwards you can use track.Biography = artist.Biography; to copy the biography to the track's data.


At last do host.RemoteCalls.CommitAllChanges(YOURTRACKS). This will write tags and update the database.


Thank you much! I will give this a shot :)

Hmm, I don't have this right :(


using System;

using NeonScripting;

using System.Collections;

using System.IO;

using System.Text;

using System.Linq;


public class Script

{

    public bool Run(INeonScriptHost host)

    {

        var idx = 1;

        foreach(var track in host.Tracks

        {

            host.RemoteCalls.ArtistByName(string name);

            host.UpdateProgress(string.Format("File {0}"idx));

            track.Biography = artist.Biography;

            idx++;

        }

        host.RemoteCalls.CommitAllChanges(host.Tracks);

 

        return true;

    }

}


This isn't right :(


using System;

using NeonScripting;

using System.Collections;

using System.IO;

using System.Text;

using System.Linq;


public class Script

{

    public bool Run(INeonScriptHost host)

    {

        var idx = 1;

        foreach(var track in host.Tracks

        {

            host.RemoteCalls.ArtistByName(string name);

            host.UpdateProgress(string.Format("File {0}"idx));

            track.Biography = artist.Biography;

            idx++;

        }

        host.RemoteCalls.CommitAllChanges(host.Tracks);

 

        return true;

    }

}


It should look like this:
using System;
using NeonScripting;
using NeonScripting.Models;
using System.Collections;
using System.IO;
using System.Text;
using System.Linq;
public class Script
{
	public bool Run(INeonScriptHost host)
	{
		var idx = 1;
		foreach(var track in host.Tracks) 
		{
			// you can use idx++ here, idx will be increased after it's been added to the string
			// you might use ++idx in case you want to increase idx before it is used
			// i've also added host.Tracks.Count() to show the number of total tracks
			host.UpdateProgress(string.Format("File {0}/{1}", idx++, host.Tracks.Count()));

			// we need to define the variable artist here
			NeonScriptArtist artist = new NeonScriptArtist();

			// we will run into troubles when a track has multiple artists (artist1 feat. artist2) so we will simply try to do this
			try
			{
				// lookup the artist and push data to variable artist
				artist = host.RemoteCalls.ArtistByName(track.Artist);
			}
			// in case this did not work, we continue with the next track
			catch
			{
				continue;
			}

			// if the artist has a biography...
			if (!string.IsNullOrEmpty(artist.Biography))
				// ...copy biography from artist to track
				track.Biography = artist.Biography;
		}
		host.RemoteCalls.CommitAllChanges(host.Tracks);
		return true;
	}
}


Hello and thank you for sharing this with me. It runs without any errors but the Biography section with the tag editor is still blank :(



Well it seems there's a bug in the script engine...


Please try this one for now:

using System;
using NeonScripting;
using NeonScripting.Models;
using System.Collections;
using System.IO;
using System.Text;
using System.Linq;
public class Script
{
	public bool Run(INeonScriptHost host)
	{
		var idx = 1;
		foreach(var track in host.Tracks) 
		{
			// you can use idx++ here, idx will be increased after it's been added to the string
			// you might use ++idx in case you want to increase idx before it is used
			// i've also added host.Tracks.Count() to show the number of total tracks
			host.UpdateProgress(string.Format("File {0}/{1}", idx++, host.Tracks.Count()));

			// create the SQL-Query: Get biography from one specific artist and only if it's not empty
			string sql = string.Format("SELECT Biography FROM `tblartists` WHERE ArtistName = '{0}' AND Biography IS NOT NULL", track.Artist);
			// Call the database and get the results
			NeonDataReader reader = host.RemoteCalls.GetDataReader(sql);
			// check if any result has been found
			if (reader.NumberOfRows > 0)
			{
				// since we looked for a specific Artist_Id we have only one result, so we can simply use the first row
				NeonDataRow row = reader.Rows[0];
				// We only wanted to get the biography field, so we can use the first field
				track.Biography = (string)row.Fields[0];
			}
		}
		host.RemoteCalls.CommitAllChanges(host.Tracks);
		return true;
	}
}

 

That works beautifully!! Thanks so much for your help.

Login or Signup to post a comment