Re^4: Perl Database Entries

Replies are listed 'Best First'.
Re^5: Perl Database Entries by huck (Prior) on Jul 16, 2017 at 05:34 UTC
what did you assign to $api_results? Remember my line dropped the pipe into jg, what you showed us above is not json	[reply]
Re^6: Perl Database Entries by cbtshare (Monk) on Jul 16, 2017 at 06:51 UTC
Yes sorry about that, my api_results variable has {"id":1084,"slug":"twitter-sportchek","name":"Twitter - SportChek","pr +ovider":{"id":44,"slug":"twitter","name":"Twitter - Shared","access_t +ype_cd":null,"login_method":"spider","launch_timeout":45,"capabilitie +s":{"has_import":false,"has_password_reset":true,"has_provision":fals +e,"has_saml":false,"has_sync":false,"unsupported_browsers":[],"servic +es":"authenticatable","auth_or_directory":false,"has_ou":false},"icon +s":{"small":"https://am.com/a/images/apps/twitter_sm.png","large":"ht +tps://assets.bitium.com/a/images/apps/twitter_lg.png","color_code":"7 +c8d95","first_letter":"t"}},"configuration":{"saml":false,"login":rtc +hek","url":"https://twier.com","extra":null,"install_type":"shared"," +authentication_type":"browser","launch_method":"get","namespace":["tw +itter.com"]},"labels":[],"created_at":"2017-06-09T23:22:15.571-07:00" +,"status":"active","key_vault":false,"requires_mfa_code":false},{"id" +:109291,"slug":"rei-spredfast","name":"REI - Spredfast","provider":{" +id":5561,"slug":"spredfast","name":"Spredfast","access_type_cd":null, +"login_method":"spider","launch_timeout":45,"capabilities":{"has_impo +rt":false,"has_password_reset":false,"has_provision":false,"has_saml" +:false,"has_sync":false,"unsupported_browsers":[],"services":"authent +icatable","auth_or_directory":false,"has_ou":false},"icons":{"small": +"https://assom/a/images/apps/spredfast_sm.png","large":"https://asset +s.bitium.com/a/images/apps/spredfast_lg.png","color_code":"65c6bb","f +irst_letter":"s"}},"configuration":{"saml":false,"login":"reiteamocia +l","url":"https://www.spredfast.com","extra":null,"install_type":"sha +red","authentication_type":"browser","launch_method":"get","namespace +":["www.spredfast.com"]},"labels":[],"created_at":"2017-06-19T12:33:3 +3.561-07:00","status":"active","key_vault":false,"requires_mfa_code": +false}] [download] But after running the code I am getting the error Not a HASH reference at line 79, <> line 2. line 79 is push @insert,$row->{$var};	[reply] [d/l]
Re^7: Perl Database Entries by huck (Prior) on Jul 16, 2017 at 08:15 UTC
No that is not your $api_results, is it? Again that is invalid json, when i fixed it i got the same result you did. If you lie to us why do you expect to get valid advice? And you dont follow instructions well do you? you changed THREE major parts of what i had posted, resulting in a whole different result from what you would have gotten if you had just cut & pasted what i wrote. but not knowing what jg did, i misinterpreted part of what was needed to make this work, so i included the fix in this version. See if you can figure out what you did wrong and why was it wrong from what i had first posted. Then try to figure out what i did to simulate how jg worked. But i suspect both tasks are beyond your abilities. $app_insert = $dbh->prepare("INSERT INTO bitium_apps(id, app_slug, app +_Name, team_id, provider_id, provider_slug, provider_saml, config_saml, confi +g_login, config_url, created_date, status, key_vault, mfa_code, configuration_i +nstall) VALUES (?, ?, ?, \"$response\", ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?") o +r die $dbh->errstr; use JSON; my $json=new JSON; my $unjson=$json->allow_nonref->decode($api_results); 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.url .created_a +t .status .key_vault .requires_mfa_code .configurati +on.install_type /){ my $var2=substr($var,1); my @parts=split('\.',$var2); my $base=$row; my $terminal=pop @parts; while (scalar(@parts)>0) { my $subpart=shift @parts; $base=$base->{$subpart}; } push @insert,$base->{$terminal}; } # var $app_insert->execute(@insert[0,1,2,4 .. 15]) or die $app_insert->e +rrstr; } # row [download] Dont screw up again, and try to learn about what you are doing rather than just trying to get someone to do it for you	[reply] [d/l]
Re^8: Perl Database Entries by cbtshare (Monk) on Jul 16, 2017 at 20:39 UTC
Re^9: Perl Database Entries by huck (Prior) on Jul 16, 2017 at 21:39 UTC
Some notes below your chosen depth have not been shown here
Re^7: Perl Database Entries by poj (Abbot) on Jul 16, 2017 at 08:15 UTC
Try this, check the log and if OK un-comment the database commands #!/usr/bin/perl use strict; use JSON; use DBI; use Data::Dump 'pp'; my $api_results = q! [{ "id":1084, "slug":"twitter-sportchek", "name":"Twitter - SportChek", "provider":{ "id":44, "slug":"twitter", "name":"Twitter - Shared", "access_type_cd":null, "login_method":"spider", "launch_timeout":45, "capabilities":{ "has_import":false, "has_password_reset":true, "has_provision":false, "has_saml":false, "has_sync":false, "unsupported_browsers":[], "services":"authenticatable", "auth_or_directory":false, "has_ou":false }, "icons":{ "small":"https://am.com/a/images/apps/twitter_sm.png", "large":"https://assets.bitium.com/a/images/apps/twitter_lg.png" +, "color_code":"7c8d95", "first_letter":"t" } }, "configuration":{ "saml":false, "login":"rtchek", "url":"https://twier.com", "extra":null, "install_type":"shared", "authentication_type":"browser", "launch_method":"get", "namespace":["twitter.com"] }, "labels":[], "created_at":"2017-06-09T23:22:15.571-07:00", "status":"active", "key_vault":false, "requires_mfa_code":false }, { "id":109291, "slug":"rei-spredfast", "name":"REI - Spredfast", "provider":{ "id":5561, "slug":"spredfast", "name":"Spredfast", "access_type_cd":null, "login_method":"spider", "launch_timeout":45, "capabilities":{ "has_import":false, "has_password_reset":false, "has_provision":false, "has_saml":false, "has_sync":false, "unsupported_browsers":[], "services":"authenticatable", "auth_or_directory":false, "has_ou":false }, "icons":{ "small":"https://assom/a/images/apps/spredfast_sm.png", "large":"https://assets.bitium.com/a/images/apps/spredfast_lg.pn +g", "color_code":"65c6bb", "first_letter":"s" } }, "configuration":{ "saml":false, "login":"reiteamocial", "url":"https://www.spredfast.com", "extra":null, "install_type":"shared", "authentication_type":"browser", "launch_method":"get", "namespace":["www.spredfast.com"] }, "labels":[], "created_at":"2017-06-19T12:33:33.561-07:00", "status":"active", "key_vault":false, "requires_mfa_code":false }]!; my $json = new JSON; my $data = $json->allow_nonref->decode($api_results); pp $data; my $dbh;# connect my @cols = qw( id app_slug app_Name provider_id provider_slug provider_saml config_saml config_login config_url created_date status key_vault mfa_code configuration_install ); my $cols = join ',',@cols; my $sql_insert = " INSERT IGNORE INTO apps ( $cols ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)" ; #my $sth = $dbh->prepare($sql_insert) or die $dbh->errstr; my $logfile = 'log.dat'; open LOG,'>',$logfile or die "Could not open $logfile : $!"; print LOG join "\t",@cols,"\n"; my $count; for (@$data){ my @row; push @row,$_->{'id'}; push @row,$_->{'slug'}; push @row,$_->{'name'}; push @row,$_->{'provider'}{'id'}; push @row,$_->{'provider'}{'slug'}; push @row,$_->{'provider'}{'capabilities'}{'has_saml'}; push @row,$_->{'configuration'}{'saml'}; push @row,$_->{'configuration'}{'login'}; push @row,$_->{'configuration'}{'url'}; push @row,$_->{'created_at'}; push @row,$_->{'status'}; push @row,$_->{'key_vault'}; push @row,$_->{'requires_mfa_code'}; push @row,$_->{'configuration'}{'install_type'}; #$sth->execute(@row) or die $sth->errstr; print LOG join "\t",@row,"\n"; ++$count; } close LOG; print "$count records inserted - see $logfile"; [download] poj	[reply] [d/l]
Re^8: Perl Database Entries by cbtshare (Monk) on Jul 16, 2017 at 20:42 UTC