[RESOLVED] Learning from the beginning? (CAIRO SQL)

[VanGoghGaming]

You already have working code that reads your "CSV" into a recordset. All it remains for you to do is loop through that recordset one record at a time and copy the field values into your newly created table provided by Olaf's code. I'll leave it to Olaf to provide a working example of this importing process.

[Schmidt]

Right now, Olaf, I'm just importing the DB Csv but it's saying it's not a database.
Did I miss something basic again?

I thought your CSV-file was "just an example" (filled by hand, to show what you want to end up with in a real DB-Table).

So, if there indeed are "a bunch of CSV-Files with real data" lying around,
then you need little importer routines, to move the CSV-Data over into "real Tables with properly typed Fields"...

But creating the "Target-SQLite-DBFile" (along with such "typed Tables") is the first task -
and you should finish this task first, before we talk about "imports from CSV into them"...

To help you with this Table-Creation-stuff, I've put together a little Demo-App -
which allows (via 2 helper-routines in a DBHelper.bas), to define them like that (basically like a VB-UDT):

Private Sub EnsureArmorTbl() 'each Table-Creation deserves its own little routine
  EnsureTbl "Armor"
    EnsureFld "Category  Text  Default '' Collate NoCase" 'Collate NoCase ensures "Case-InSensitive" text-comparisons
    EnsureFld "Name      Text  Default ''"
    EnsureFld "Material  Text  Default ''"
    EnsureFld "DmgReduce Float Default 0.1"
End Sub

The Demo also contains a little Grid-Widget-based Viewer-Form,
which you can use instead of printing a given Rs into the Debug-Window:


Here is the Demo-Zip:
https://vbRichClient.com/Downloads/SQLiteRsHandling.zip

Please use this, to create all your target-tables first (analog to the two tables "Weapons" and "Armor" which the Demo uses)
and then play around with adding a few example-records into them (using properly written Add-Routines, like in the Demo) -
and then let's talk about CSV-importing in the next posts...

Olaf

[-Corso->]

Awesome, Thank You Olaf. This is great.
I'll jump onto this first thing on the morrow.
Yep, the test one was just a made up CSV. Some of the real CSV's are nasty. They have an expanding number of fields depending upon how many limbs a species has for example, or, apparel too. Some have irregular rows as well. But the majority are normal.
Will post back when up to speed.

[Schmidt]

Ok, based on the example-project above (relying on the existing routines from modDBHelpers.bas) -
here's a Demo-Import-Function for the Weapons-Table (which could be placed in modDBHelpers.bas as well):

Sub ImportWeaponsCSV(CsvFile As String)
  Cnn.Execute "Create Virtual Table temp.csv using csv(header=true,filename='" & CsvFile & "')" 'opens (attaches) the csv-file as a Virtual table (named temp.csv)
  
  Cnn.Execute "Delete From Weapons" 'Attention! *All* pre-existing records of the target-table will be deleted, before importing new CSV-records

'  the following With-Block-loop is a bit more "Field-type conversion enforcing"
'  (though alternatively one can use the out-commented Execute "Insert Into..." below, which has the advantage of performing better)
  With Cnn.GetRs("Select * From temp.csv") 'select a source-recordset from the "virtual, sidewards-connected" temp.csv
    Do Until .EOF 'loop over each record of the csv-source-Rs, and pass its "named Fields" ...
       AddWeapon !Category, !Name, !Stat1, !Stat2 '...into the Params of the existing AddWeapon-Routine
      .MoveNext 'shift to the next Source-Record
    Loop
  End With
  
'  Cnn.Execute "Insert Into Weapons( ID,Category,Name,Stat1,Stat2 )" & _
'                            "Select ID,Category,Name,Stat1,Stat2 from temp.csv"

  Cnn.Execute "Drop Table temp.csv" 'remove the virtual temp-table from the DB-Connection
End Sub

Olaf

[-Corso->]

Ok, currently 'building' the Database tables from nothing and removing all the call subroutines so I can see it all in one line.
I don't require the 'Ensuring checkers' for this version, helps to de-complicate it all.

I'm up to the adding rows bit.

But tell me, what arrangement do I need to make this, for reading simplicity? "Error: Expected Function or Variable"
Note: rs As cRecordset

    Table_Name = "Weapons" 'Specifiy the Table
    Sequel_String = "Select * From " & Table_Name 'Make the Search string
    Set rs = Cnn.GetRs(Sequel_String) 'Shorten the Record set for ease of access
    With rs.AddNew
        .Fields("Category") = "Test Sword"
        .UpdateBatch
    End With

I was trying to replicate this in the Helper function, but in an easy-read way:

With Cnn.GetRs("Select * From Weapons Where 1=0") 'Where 1=0 is a condition which matches "no record", resulting in a "FieldHeaders only"-Rs
    .AddNew 'add a new Record-(Row) into this Rs (which till this point was "headers-only" without any records)
      .Fields("Category") = Category

Also, I didn't know you had the entire FSO in Cario. That will help a lot. I built a crap tonne of file handling things, only to now find them in there. Wah! It's handy.

[VanGoghGaming]

    Table_Name = "Weapons" 'Specifiy the Table
    Sequel_String = "Select * From " & Table_Name 'Make the Search string
    Set rs = Cnn.GetRs(Sequel_String) 'Shorten the Record set for ease of access
    With rs.AddNew
        .Fields("Category") = "Test Sword"
        .UpdateBatch
    End With

Are you seriously not able to fix this error on your own? Now I understand why you found the "Business Card Thingy" tutorial too complicated...

    With rs
       .AddNew
       .Fields("Category") = "Test Sword"
       .UpdateBatch
    End With

[-Corso->]

Yes, I have retard strength in regards to this stuff.
I've a million and one bad habits from using VB and Access Db's ages ago.
However, try learning this stuff when you only get 1/2 hour sleep per night, each night, every night. It's breaking me.
I was confused and thought it was a singluar action call like rs.AddNew "SomeFieldName", "SomeValue"
Not a prep call to which you then enact the rest. I can't say I've ever come across that before.
As mentioned, I'm an infant level user in regards to SQL. So I'm going to ask a 5hit tonne more stupid questions.
I appreciate the assist all the same.

[Schmidt]

I don't require the 'Ensuring checkers' for this version, helps to de-complicate it all.

Please make use of the DBHelpers.bas Module - especially of the two helpers:
- EnsureTbl
- EnsureFld

They are dead-simple to use, similar Field-Def-Lines easy to copy&paste for your next (somewhat similarly structured Table) -
and that they provide your Table-Fields with proper Default-Values will help a great deal with potential surprises in queries, down the road...

Especially EnsureFld - because it operates based on "Alter Table ... Add Column" -
meaning that you can enhance an existing table in your DB-File at any time about a Field you forgot -
even when the Table alread contains a ton of records already...

I'm up to the adding rows bit.

Same recommendation here... (speaking from years of experience with DB-stuff).

Please adopt the philosophy behind the current AddHelper-functions in dedicated, separate routines (one for each table) -
enhance and adapt them to your specific table-fields with a dedicated, "identically named ParameterList"
(which will pop-up via Intellisense, reminding you of the FieldNames this table has).

Please wait with "inventing your own stuff" until you're really fluent with DBs, Recordsets and SQL.

Olaf

[-Corso->]

Yes Olaf, I do see the need for subs with helpers, and subs for all the functions really. Don't worry I'm not forsaking you. You are the -Master-. I am just a Sith Apprentice.

But when I'm learning new lines, I find jumping all over the place difficult. It's like looking at a pile of strings and knots, one loses their place rather easily. As in, you've got a set of variables in your head, you're now looking for some magic word, that might be a funciton, a sub or a reserved word, you don't know yet. You find it, see the variables enter and then it's off on another quest with an identical situation, but, with other magic words and more variables. Eventually you find yourself many layers deep, caked in variables and magic words, and wondering what quest you were on in the first place.

It's the simple story of, once I see what a thing does, I can use it in other ways, and not just copy-paste it without understanding.
I'm physically de-subbing it for that reason. One can see the mechanical apparatus in full view of it's consituents. In all it's nakedness. Is it practical? HELL NO. Is it helpful? HELL YES.

Though, in the long shot. If I add in a new 'field category', I'd want the code to fail. Simply because it demands I need to insert the program-operational code for the new 'thing', whatever that may be. For the most part, I'd want fields hard coded in the main software to show up that type of problem.

So yeah Olaf, I can clearly appreciate what you've done. Very smooth, extremely efficient and tidy, self-checking too. But I need the most basic mud and dirt stuff for playing and 'seeing' how it works.

Anyway, getting there, just having a little trouble loading in the rows from the file now (De subbed).
I am assuming !Category means loading in that field from the file. I think I've fluffed the rs somewhere. Still, it's good to see it this way, coded compactly.

Edit: Got it working. I might start some tests on copies on real game data now.

[Schmidt]

...when I'm learning new lines, I find jumping all over the place difficult...

Then you need to make better use of the IDEs Intellisense, which in your case means:
- rename the modDBHelpers-Module to something shorter to type, e.g. modDB
- then - anytime you want to "add something to a DB-table", you start typing: modDB.A
- and Intellisense will list all the Add-prefixed functions defined in modDB (.AddWeapon, .AddMaterial a.s.o)
- choose the one you actually need, and look at the list of "well-named params" to fill in...

That's it - and you will get no such Intellisense-Support for the "String-literal-based" FieldNames in Rs.Fields("someField")...
(you have to remember the correctly written Fieldname "all on your own" for the given Table the Rs was derived from).

Besides, the point in time where you write "a new Add-Function" is, shortly after you wrote its matching "EnsureTablexxxx"-function
(ideally directly below it in modDB, so "the context is fresh in your mind" and the "correct fieldnames are near each other").

Olaf

[-Corso->]

Loading in the early test data for materials via CSV. NICE!



Finding Blue Cloth and Updating to Ultra Marine Cloth. NICE!



And uploading back to Materials Table and Saving DB (Done). NICE!

And then re-opening pre-loaded Db3 file with all new records (Done). NICE!

Thats' 4 for 4.


Thanks Olaf, VanGoghGaming and Zvoni for making this 30 day wandering trek to a 3 day effort.

Time for an adventure!

[Schmidt]

And uploading back to Materials Table and Saving DB (Done). NICE!

Done via Rs.UpdateBatch, I guess...

Anyways, glad you had it running.

And then re-opening pre-loaded Db3 file with all new records (Done). NICE!

Hint... if you want to refresh a given Rs after inserting new records via the Add...Functions -
a simple Rs.ReQuery would be enough to bring Rs-content "up-to-date" (no need to "re-open" a given DB-Cnn).

E.g. if you have a "Form-scoped" Rs-Variable like this:
Private RsMaterial As cRecordset

Which you selected with only the ID and Unique_Name Fields:
Set RsMaterial = Cnn.GetRs("Select ID, Unique_Name From Materials")

...to e.g. bind it directly as a DataSource to an (RC6Widgets.dll) drpDown-Widget like that:
drpMaterials.SetDataSource RsMaterial, "RsMaterial"

...then a later:
RsMaterial.Requery
should be enough, to update the content of the dropdown-box as well...

Olaf

[-Corso->]

Done via Rs.UpdateBatch

Yep

Rs.ReQuery Ohhhh, okay. I can do that.

The dropdown linking would be handy for the Facebuilder actually. But, that'll come later. (It's working quite happily).

Today, it's time to 'rebuild' the enitre game via it's modular-component-form while adding in the new stuff. Aka, a real build, that's clean and easy to manage when it gets really big.
After that it'll be onto the NPC dialogue/chatting which can now utilize SQL. (And/or the weapons generator).

But, realistically, I'll need a proper way to handle the instant facebuilds for NPC's as the facebuilder isn't spliced with the main program. Though, in the short term, pre generated NPC's will work easily enough. I'll most likely need to modularize the facebuilder too (to separate out the constructor bits).

Anyway, it's getting there.