Perl Database Entries

cbtshare has asked for the wisdom of the Perl Monks concerning the following question:

Hello All, I am using perl DBI in a perl script to pull information from an api, parse it and then enter it into a database.My code is as follows: So I put the api results in $api_results

$api_results=`curl -H "Authorization: token 5FEFAQ1W4" https://www.bom
+/api/v2/organizations/company/admin/groups/"$groid"/installations?pag
+e=1 > "$valid"; cat "$valid" | jq -r '(.[]|[.id, .slug, .name, .provi
+der.id, .provider.name,.provider.capabilities.has_saml,.configuration
+.saml,.configuration.login, .configuration.install_type, .created, .k
+eyvault, .requires_mfa_code) | \@csv` ;
   
##insert statement 
$app_insert = $dbh->prepare("INSERT IGNORE INTO apps(id, app_slug, app
+_Name, provider_id, provider_slug, provider_saml, config_saml, config
+_login, config_url, created_date, status, key_vault, mfa_code) VALUES
+ (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?") or die $dbh->errstr;

                           ##Insert execution
                           while (my @api = $api_results->fetchrow_arr
+ay())
                           {
                           $app_insert->execute(@api) or die $app_inse
+rt->errstr;
                           }
                        print "Done inserting \n";
                        exit;
                         }
[download]

But after I execute the script, I get the following error, I think it is because i dont have api_results->execute() , but there is no statement to execute, so how can I make this work? Thank you

Total    % Received % Xferd  Average Speed   Time    Time     Time  Cu
+rrent
                                 Dload  Upload   Total   Spent    Left
+  Speed
100 15573  100 15573    0     0  13102      0  0:00:01  0:00:01 --:--:
+-- 83278
Can't call method "fetchrow_array" without a package or object referen
+ce at bitium.pl line 70, <> line 2.
[download]

Comment on Perl Database Entries Select or Download Code

Replies are listed 'Best First'.
Re: Perl Database Entries by huck (Prior) on Jul 14, 2017 at 05:02 UTC
I am not sure what is in $api_results, but it is defiantly not a DBI/DBD object that can be used to call a DBI/DBD method with. It has the TEXT output of the curl command thru a series of pipes. and i am pretty sure that your final pipe in that command `\| \@csv` is not doing anywhere near what you think it is, for it will resolve to something like `\| ARRAY(0xa3e43c)` "jq" seems to be some sort of json post processor, maybe you just want to end with that and then parse its text output with split?	[reply] [d/l] [select]
Re^2: Perl Database Entries by cbtshare (Monk) on Jul 14, 2017 at 23:46 UTC
yes jq is a json parse, the results I am trying to store in $api_results are 105924,"sprinklr","Sprinklr",5562,"Sprinklr",false,false,"Farme@.socia +l","shared","2017-05-08T09:54:29.787-07:00","active",false,false 108459,"toll-brothers-spredfast","Toll Brothers - Spredfast",5561,"Sp +redfast",false,false,"toll@social","shared","2017-06-07T18:03:52.862- +07:00","active",false,false 1034,"twitter-sportchek","Twitter - SportChek",44,"Twitter - Shared",f +alse,false,"sportchek","shared","2017-06-09T23:22:15.571-07:00","acti +ve",false,false 109291,"rei-spredfast","REI - Spreast",5561,"Spredfast",false,false,"r +eitea@cial","shared","2017-06-19T12:33:33.561-07:00","active",false,f +alse [download] So the api call works, I just need to put that info in a database	[reply] [d/l]
Re^3: Perl Database Entries by huck (Prior) on Jul 15, 2017 at 00:52 UTC
If i were doing it, i'd skip curl and use LWP, then JSON As that may turn out to be too much for you you might want to skip jg. at least. Something like $api_results=`curl -H "Authorization: token 5FEFAQ1W4" https://www.b +om/api/v2/organizations/company/admin/groups/"$groid"/installations?p +age=1`; use JSON; my $json=new JSON; my $unjson=$json->allow_nonref->decode($api_results); $app_insert = $dbh->prepare("INSERT IGNORE INTO apps(id, app_slug, a +pp_Name, provider_id, provider_slug, provider_saml, config_saml, conf +ig_login, config_url, created_date, status, key_vault, mfa_code) VALU +ES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?") or die $dbh->errstr; for my $row (@$unjson) { my @insert; for my $var (qw/.id .slug .name .provider.id .provider.name .provider.capabilities.has_saml .configuration.sam +l .configuration.login .configuration.install_type .created .keyvault .requires_mfa_code /){ push @insert,$row->{$var}; } # var $app_insert->execute(@api) or die $app_insert->errstr; } # row [download]	[reply] [d/l]
Re^4: Perl Database Entries by cbtshare (Monk) on Jul 16, 2017 at 05:23 UTC
Re^5: Perl Database Entries by huck (Prior) on Jul 16, 2017 at 05:34 UTC
Some notes below your chosen depth have not been shown here
Re^4: Perl Database Entries by cbtshare (Monk) on Jul 15, 2017 at 14:45 UTC
Re: Perl Database Entries by roboticus (Chancellor) on Jul 14, 2017 at 05:30 UTC
cbtshare: If your `$api_results` is holding a reference to an array of arrays of data like this: `$api_results = [ [ "id1", "app_slug1", "app_name_1", "provider_id_1", ..etc.. ], [ "id2", "app_slug2", "app_name_2", "provider_id_2", ..etc.. ], . . . . . . . . . . . . . . . [ "idN", "app_slugN", "app_name_N", "provider_id_N", ..etc.. ] ];` [download] Then I think what you're looking for is: `# Prepare a handle to an insert statement: $app_insert = $dbh->prepare("INSERT .....blah, blah"); # Fetch each row of data from results while (my $row = shift @$api_results) { # Tell the insert statement to insert the data $app_insert->execute(@$row); } print "Done inserting\n";` [download] ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply] [d/l] [select]
Re^2: Perl Database Entries by cbtshare (Monk) on Jul 14, 2017 at 23:48 UTC
ok, thank you , will try this and get back to you.	[reply]