datetime format in GB-PVR

[zehd]

I'm having a problem setting dateTime data types in the sqlite3 database

When I place a date, it saves into the database like

Code:

2009-03-26 10:00:00 PM

But I think it should look like

Code:

2009-03-24 09:00:00.0000000

the incorrect format breaks GB-PVR

I'm writing in VB .net.

[psycik]

tried using a creating a parameter object, setting the parameter value type then adding it to the dbcommand object.parameters collection?

Might be the best way to do it.

Parameters are placeheld in sql strings with "?"

[sub]

Yep, what he said. You should be using parameter objects for dates, not formatted strings.

[zehd]

Would you mind giving an example?

[sub]

Code:

// try to find show with the same name and start time
DbCommand findProgrammeCommand = systemConfig.getDatabaseCommand("SELECT oid FROM PROGRAMME where name=? and start_time=?" , connection);
findProgrammeCommand.Parameters.Add(systemConfig.getDatabaseParameter("@name", System.Data.DbType.String));
findProgrammeCommand.Parameters.Add(systemConfig.getDatabaseParameter("@start_date", System.Data.DbType.DateTime));

findProgrammeCommand.Parameters[0].Value = title;
findProgrammeCommand.Parameters[0].Size = title.Length;
findProgrammeCommand.Parameters[0].DbType = System.Data.DbType.String;

findProgrammeCommand.Parameters[1].Value = startTime;
findProgrammeCommand.Parameters[1].DbType = System.Data.DbType.DateTime;

[zehd]

Code:

// try to find show with the same name and start time
DbCommand findProgrammeCommand = systemConfig.getDatabaseCommand("SELECT oid FROM PROGRAMME where name=? and start_time=?" , connection);
findProgrammeCommand.Parameters.Add(systemConfig.getDatabaseParameter("@name", System.Data.DbType.String));
findProgrammeCommand.Parameters.Add(systemConfig.getDatabaseParameter("@start_date", System.Data.DbType.DateTime));

findProgrammeCommand.Parameters[0].Value = title;
findProgrammeCommand.Parameters[0].Size = title.Length;
findProgrammeCommand.Parameters[0].DbType = System.Data.DbType.String;

findProgrammeCommand.Parameters[1].Value = startTime;
findProgrammeCommand.Parameters[1].DbType = System.Data.DbType.DateTime;

So this retrieves the data into 'findProgrammeCommand' and you read the values out as parameters that have been 'shaped' by the datatypes. I think I get that. I haven't really bee having trouble retrieving from data base yet (I might someday)

How do you shape the data before it is sent into a record with an UPDATE or REPLACE ?

[sub]

Using these types of 'prepared statements' is useful for several reasons. Setting parameters this way takes care of different format expectations of the different underlying databases, ie different databases can expect dates in different string formats.

It also provides very significant performance improvements when the same statement needs to be executabled several times with only the parameters varying on each execution.

[zehd]

I don't have to set stuff into the db often or quickly, but I guess I should learn the best way, just in case

I have been trying to create routine that will send all comums to the SQL statement. Just before I send the data to the record, I change the data.

so here's is my SQL text. 'myDB_Rec_Sched' is a single record class that was filled with existing data. I should then just change one thing or another, send the text to ExceuteNoQuery

Code:

With myDB_Rec_Sched
            'theis record is indexed on oid, so there's no changing it...
            SQLText = "UPDATE OR REPLACE RECORDING_SCHEDULE SET " & _
                    "capture_source_oid = " & .capture_source_oid & "" & _
                    ", status = " & .status & "" & _
                    ", filename = '" & [B].filename[/B] & "'" & _
                    ", recording_type = " & .recording_type & "" & _
                    ", recording_group = " & .recording_group & "" & _
                    ", manual_start_time = '" & .manual_start_time & "'" & _
                    ", manual_end_time = '" & .manual_end_time & "'" & _
                    ", manual_channel_oid = " & .manual_channel_oid & "" & _
                    ", quality_level = " & .quality_level & "" & _
                    ", pre_pad_minutes = " & .pre_pad_minutes & "" & _
                    ", post_pad_minutes = " & .post_pad_minutes & "" & _
                    ", priority = " & .priority & "" & _
                    ", conversion_profile = '" & .conversion_profile & "'" & _
                    ", renamed = " & .renamed & " " & _
                    "WHERE programme_oid =" & param_programme_oid

When I set this up,

Code:

NewFileName = Blah blah

                With myDB_Rec_Sched
                    [B].filename[/B] = NewFileName
                End With

OK. So my problem is that I have to build a Parameter object (supposedly reuseable)

I created a public Object "MyParameters". In another routine I fill 'My Parameters' with parameters

Code:

Dim command As DbCommand = dbProviderFactory.CreateParameter()

        With command
            .Parameters.Add("@manual_start_time")
            .Parameters(0).DbType = System.Data.DbType.DateTime
            .Parameters(0).Value = myDB_Rec_Sched.manual_start_time

            .Parameters.Add("@manual_end_time")
            .Parameters(1).DbType = System.Data.DbType.DateTime
            .Parameters(1).Value = myDB_Rec_Sched.manual_end_time

        End With

        myParameters = command

Now I have this object with the same structure as the Parameters object. I just have to copy it over just before I call the ExcuteNonQuery.

So I have a structure with my parameters, and datatypes
I have my instance structure I want to write to record.

I just don't know how to reference the paramaters structure.

(Thanks for everyones patience. I'm having trouble learning new words AND learning new C# words...)

[zehd]

K... THis is C I got from an example on the net. I can't understand something though

Code:

command.CommandText = "insert into items (id, name, icon, level, quality, type) values (?1, ?2, ?3, ?4, ?5, ?6);";
                    command.Parameters.Add(item_id);
                    command.Parameters.Add(item_name);
                    command.Parameters.Add(item_icon);
                    command.Parameters.Add(item_level);
                    command.Parameters.Add(item_quality);
                    command.Parameters.Add(item_type);

Obviously, In C#, you can 'add', and the parameters are auto given the index, (and I read the index starts from 1)

So referencing the parameters, ?1 references the first parameter and so on...

In keeping with this example, I I wanted to set the data type for the first parameter would I type something like

Code:

command.Parameters.Add(item_id);
[B]command.Parameters.(1).DbType = System.Data.DbType.DateTime        [/B]            
command.Parameters.Add(item_name);
command.Parameters.Add(item_icon);
command.Parameters.Add(item_level);
command.Parameters.Add(item_quality);
command.Parameters.Add(item_type);

[sub]

Sorry, I know nothing about VB.Net syntax. I'm sure googling "VB.NET Prepared Statements" will return some examples.