1. psmits
  2. RO CSVI
  3. Thursday, 01 December 2016
  4.  Subscribe via email
I am new to CSVI but I got it working, I can add records and update fields in my table
But in order to create a new record it is necessary to deliver a new record without a value for the field that has the primary key
How can I populate this field after the insertion with the proper value so it gets found by the primary key when I want to update it
I have tried with combining but that does not work
PHP version 7.0.13
Joomla! version 3.6.4
Database schema version 6.6.0
CSVI version 6.6.4.
Accepted Answer Pending Moderation
Hello,
You don't actually need a primary key field for doing an update. I am not sure which import your are using but there are few required fields for import, if the value of these required fields match with the one in database, CSVI will automatically do an update to a record.

But then one way to get primary key field for update is that you can export the needed fields along with primary key field using an export template and do your updates to the export file and import the file.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 1
Accepted Answer Pending Moderation
Hi Tharuna
I am using component CSVI PRO with import on custom tables, so no Joomla related tables
If I leave the field that is the primary key out of the import, than all records are inserted !!
Which fields are rquired for import ??
Ho can I indicate which fields need to be used to search the table ??
  1. more than a month ago
  2. RO CSVI
  3. # 2
Accepted Answer Pending Moderation
Hello,
If you are using custom table import then yes you need primary key field else records will be inserted. As i said earlier you can export the primary key field with other fields using custom table export template, do the update to fields and import the file.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 3
Accepted Answer Pending Moderation
Hi Tharuna

That is disapointing for me as it means more work to maintain the table
Can you tell me in which peace of the code, this decision is made
Because then I can change the code
If an update does deliver a rowcount "0" then I can decide to insert it
Or is it somehow possible to combine two import fields and use them to update the field afterwards ?
I know combination is possible, but that happens up front
  1. more than a month ago
  2. RO CSVI
  3. # 4
Accepted Answer Pending Moderation
Hello,
If you are looking for updating custom table based on a field other than primary key field in database, the functionality is coming in our next CSVI release. Please check custom table import documentation under SETTING THE OPTIONS section.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 5
Accepted Answer Pending Moderation
Hi Tharuna

I did read this article and was wondering how it works than. Would adding then be done based on the fact that the field other than primary is empty ?
Why not make an option to add a field indicating that the records need to be inserted
Or an option what to do if the record read from the csv is not present ??
That would make life a bit easier

By the way in my opinion the program has an error if the primary key is not set in the table
It will throw an unhandled exception not indicating which field is empty
Before going into this import this situation should be dealth with, as you can find out that no primary key is found
Than the user will directly know what is wrong, it took me a couple of hours to find that in the documentation
If I had gotten a proper warning I would have known that my table needed to be altered

when is this new version released ?
  1. more than a month ago
  2. RO CSVI
  3. # 6
Accepted Answer Pending Moderation
Hello,
Why not make an option to add a field indicating that the records need to be inserted
Or an option what to do if the record read from the csv is not present ??
That would make life a bit easier

Thats what required fields do. Their values decide if a record has to be updated or inserted. If the values in these required fields are found matching with database values we do an update else insert.

By the way in my opinion the program has an error if the primary key is not set in the table
It will throw an unhandled exception not indicating which field is empty
Before going into this import this situation should be dealth with, as you can find out that no primary key is found
Than the user will directly know what is wrong, it took me a couple of hours to find that in the documentation
If I had gotten a proper warning I would have known that my table needed to be altered

It is very clearly explained in the custom table import document that table should have a primary key else it will not work.

when is this new version released ?

Release date is not decided yet but should be in coming weeks.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 7
Accepted Answer Pending Moderation
Hi Tharuna

Very much thanks for the quick response !
I would be happy to test drive the new version, I am not using it in a live environment

You indicate that if there are requiered fields you do an insert
How can I set a field to required, I did not notice this in the template (also not in the SQL table)

Also if I try to import the same file with an empty keyfield twice, the I do get an "Duplicate key" error !
I would expect that the program should insert it again, a I did not setup required fields obviously

Yes indeed the document tells you that a primary key is necessary, but RTF is not always what we do in first case
And for a user not as experianced as I am, it does not help if you get a non meaningfull error message

But again thanks for you very good support !
  1. more than a month ago
  2. RO CSVI
  3. # 8
Accepted Answer Pending Moderation
Hello,
Very much thanks for the quick response !
I would be happy to test drive the new version, I am not using it in a live environment

Please load the attached patch file using CSVI maintenance menu and you should be the getting the new feature of selecting a import based on field option in your template settings as explained in the custom table import document.

How can I set a field to required, I did not notice this in the template (also not in the SQL table)

You cannot set a field to be required in template. What i meant with required fields is that there are few fields CSVI uses to check if they are present in your import file so to process a record. The required field for custom table import is the primary key field in the table. For example, if the primary key field of custom table is id, then id is a required field and CSVI looks for this field in the import file. This id field value is compared with database value and record will be inserted if the id value is not found in database, if found then the same id value record in database will be updated. Hope it is clear now.

Also if I try to import the same file with an empty keyfield twice, the I do get an "Duplicate key" error !
I would expect that the program should insert it again, a I did not setup required fields obviously

Primary key fields are unique fields, they cannot be duplicated. Also you cannot have a empty value in key field. It has to be integers.

Yes indeed the document tells you that a primary key is necessary, but RTF is not always what we do in first case
And for a user not as experianced as I am, it does not help if you get a non meaningfull error message

Will look into this. Thank you for your feedback.

patch_custom_table_2016_12_02.zip
Attachments (1)
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 9
Accepted Answer Pending Moderation
Hi Tharuna

Thanks for the patch which I have been testing to see what happens
Included the results of my testing
I still have a problem with adding a new record as shown in the report
It does now search through the new field, but what is the use of that if you still cannot add records ??
Or am I doing something wrong, this time I did read the manual but am confused Test report different field.docx
Attachments (1)
  1. more than a month ago
  2. RO CSVI
  3. # 10
Accepted Answer Pending Moderation
Hello,
Can you also post the debug log of your import and the sample import file? To know how to get the debug log check Where to get the debug information? section on top of this page.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 11
Accepted Answer Pending Moderation
Hi Tharuna

Included a zip file containing my logs and the export of the template and the import file
I also noted that for the backand no Dutch language is present
I can do the translations for you if you want ?
There is an nl-NL folder but that file seems to be very small compared with the English one
Attachments (1)
  1. more than a month ago
  2. RO CSVI
  3. # 12
Accepted Answer Pending Moderation
Hello,
I only want the log of custom table import. You have sent your entire logs folder, now i don't know which one to check. Checking your template shows that you are doing the import based on field Insrt but this field seems to be empty in your import file. You need to have a value to this field so CSVI checks this field with database field values.
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 13
Accepted Answer Pending Moderation
Hi Tharuna

Included two new logs
Thsi is what I tested
First attempt
1.Empty database
2. Import one line where Type =R 1999 (Primary key Insrt = 1999 and search is by Model with value 1999
3. Import says "Updated" but no record present

Second attempt
1. Empty database
2. Import one line where Type ="" Insrt = 1999 and search is by Model with value 1999
3. Import says "Updated"but no record present

I would expect that the record would be inserted as the primary key is empty
Attachments (2)
  1. more than a month ago
  2. RO CSVI
  3. # 14
Accepted Answer Pending Moderation
Hello all,

Just some background information on this. The primary key, the way Joomla treats this is, if it is present in the import file, the record will be updated regardless if it exists or not. If the primary key is not present, Joomla will do an insert.

The option Import based on is there to override the default primary key field but it doesn't change it's behavior. If the field is not in the import file, the default primary key will be used.

Looking at your test document, impressive by the way, I think I see a catch-22. When the primary key value is empty, we use the default primary key. So in your case, if we leave Insrt empty, it will use Type as fallback. Hence you get updates all the time rather than inserts. However, if an insert would be done and the Type field already exists you most likely will get an error as a primary key field needs to be unique.

Hope this clarifies a few things.
  1. more than a month ago
  2. RO CSVI
  3. # 15
Accepted Answer Pending Moderation
Hi Roland,

Thanks for your explenation, it does clarify the behavior a bit
But it does not resolve the issue I have, currently I cannot add a record with the proper details
I proposed it earlier why not do a check if a record exists (using the field indicated), update the record if it is present or insert if it is not present
I tried using my SeqNo as primary key but it behaves the same way (as you described in your explenation)
I added the SeqNo for this purpose, you can leave it empty so SQL does insert it (and joomla should do it also)
  1. more than a month ago
  2. RO CSVI
  3. # 16
Accepted Answer Pending Moderation
Hello,
Included two new logs
Thsi is what I tested
First attempt
1.Empty database
2. Import one line where Type =R 1999 (Primary key Insrt = 1999 and search is by Model with value 1999
3. Import says "Updated" but no record present

This is correct, you have set import based on Model with value 1999, CSVI is searching for a record with 1999, there is no such record to update but setting an import field value 1999 says CSVI to update that record and it runs update query and so message Updated.

Second attempt
1. Empty database
2. Import one line where Type ="" Insrt = 1999 and search is by Model with value 1999
3. Import says "Updated"but no record present

This is same like above, you have given Model value to be 1999 and so CSVI is trying to update a record with value 1999. You need to leave this empty if you need record to be inserted. Also this field Model should be set as import based on field.

I tried using my SeqNo as primary key but it behaves the same way (as you described in your explenation)
I added the SeqNo for this purpose, you can leave it empty so SQL does insert it (and joomla should do it also)

Can you try to load the attached patch file using CSVI maintenance menu and see if it works?

patch_custom_table_2016_12_05.zip
Attachments (1)
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 17
Accepted Answer Pending Moderation
Hi Tharuna

Thanks again for this fix, but unfortunately it did not resolve the problem
See the included tests I have done with it
Following has been done to test it
1. Remove component completely
2. Remove all CSVI tables
3. Reinstalled the component
4. Aply both patch files
5. Empty the database
Attachments (1)
  1. more than a month ago
  2. RO CSVI
  3. # 18
Accepted Answer Pending Moderation
Hello,
From your document in Test case 1(the one result is NOK), you have a value for Type field in your import file(R1 2020) which is also a import based on field and CSVI is trying to update. What if you leave this field empty in the import file? does it inserts a new record then?
Kind regards,

Tharuna

=========================
If you use CSVI, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 19
Accepted Answer Pending Moderation
Hi Tharuna

Look at the following tests, if I leave the Type field empty, than the record is inserted !
I forgot to include the log files, but for some reason they are not present ??
I did setup logging so there should be log files present
Is this maybe caused by the change that was made ??
Within the backend there are log entries shown, so if necessary I can download them
  1. more than a month ago
  2. RO CSVI
  3. # 20
  • Page :
  • 1
  • 2
  • 3


There are no replies made for this post yet.
Be one of the first to reply to this post!